Forum Discussion
Odata feed URL basic authentication
Hi, I created OData.Feed and manualy insert my username and password into basic authentication. It works fine, but there is my credentials all the time in the same excel workbook. My idea is ......
It is my final solution:
... Source4 = Excel.CurrentWorkbook(){[Name="encode"]}[Content], options = Source4{0}[encode], Zdroj = OData.Feed("URL, null, [Headers = [#"Authorization"=options]]), ...variable "options" is store in cell in excel sheet.
Options is "Basic dXNlcm5hbWU6cGFzc3dvcmQ=" is the base64 encoded string of sample credential username: password . Between username and password is no space.
It works perfectly.
This is function for excel for encoding string from username: password to base64 encoded string. May be useful.
Function EncodeBase64(text As String) As String Dim arrData() As Byte arrData = StrConv(text, vbFromUnicode) Dim objXML Dim objNode Set objXML = CreateObject("MSXML2.DOMDocument") Set objNode = objXML.createElement("b64") objNode.DataType = "bin.base64" objNode.nodeTypedValue = arrData EncodeBase64 = objNode.text Set objNode = Nothing Set objXML = Nothing End Function
It is my final solution:
...
Source4 = Excel.CurrentWorkbook(){[Name="encode"]}[Content],
options = Source4{0}[encode],
Zdroj = OData.Feed("URL, null, [Headers = [#"Authorization"=options]]),
...variable "options" is store in cell in excel sheet.
Options is "Basic dXNlcm5hbWU6cGFzc3dvcmQ=" is the base64 encoded string of sample credential username: password . Between username and password is no space.
It works perfectly.
This is function for excel for encoding string from username: password to base64 encoded string. May be useful.
Function EncodeBase64(text As String) As String
Dim arrData() As Byte
arrData = StrConv(text, vbFromUnicode)
Dim objXML
Dim objNode
Set objXML = CreateObject("MSXML2.DOMDocument")
Set objNode = objXML.createElement("b64")
objNode.DataType = "bin.base64"
objNode.nodeTypedValue = arrData
EncodeBase64 = objNode.text
Set objNode = Nothing
Set objXML = Nothing
End Function