I'm facing challenges with an API that provides natural gas data. The documentation for this API can be found at . It allows me to access Json-formatted data by inputting a URL into my internet browser. However, in order to download the Json data, I need to use a "x-key" provided to me and execute a cURL command as shown in the example from the documentation:
"https://agsi.gie.eu/api/data/eu?from=2017-06-01&till=2017-06-14" --header "x-key: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"Our goal is to automate certain processes by creating a macro-enabled Excel document that can easily retrieve and utilize this information within Excel. Despite trying various VBA codes found on StackOverflow, I keep encountering an "access denied" error when interacting with the API. My assumption is that this issue arises due to incorrect input of the password (or "x-key"). The VBA code snippet I currently have includes references to JsonConverter and Base64Encode modules:
Sub test()
Dim httpObject As Object
Set httpObject = CreateObject("MSXML2.XMLHTTP")
sUrl = "https://alsi.gie.eu/api/data/eu?from=2018-11-15&till=2018-12-04"
sAuth = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
sRequest = sUrl
httpObject.Open "GET", sRequest, True
httpObject.setRequestHeader "x-key", Base64Encode(sAuth)
httpObject.send
sGetResult = httpObject.responseText
MsgBox sGetResult
Dim oJSON As Object
Set oJSON = JsonConverter.ParseJson(sGetResult)
For Each sItem In oJSON
dItemDate = oJSON(sItem)("date")
sItemString = oJSON(sItem)("string")
vItemValue = oJSON(sItem)("value")
MsgBox "Item: " & sItem & " Date: " & dItemDate & " String: " & sItemString & " Value: " & vItemValue
Next
End Sub
Despite attempting to apply Base64Encode to the password variable (sAuth) and trying different methods including without a password/key, I consistently receive the "access denied" message. This leads me to believe that the issue lies with the password entry. I am uncertain of how to proceed further, as this setup utilizing VBA seems to be the closest I have come to successfully retrieving data from this API within Excel. Your assistance is greatly appreciated.
Thank you.