Can I use VBA to pull data from the web that requires bearer token authentication and also can I automatically convert the .json data to .xml?

[lala_Hae_dong]

So far I have been using Postman to GET data with our bearer token. I need to get this data into Excel. So far what we've done is download the .json using postman, convert the .json to .xml and open the file with Excel. Can I use VBA to pull data from the web that requires bearer token authentication and also can I automatically convert the .json data to .xml?

I have done some research and found a code

Sub GetData()

Dim hReq As Object, Json As Dictionary
Dim sht As Worksheet
Dim authKey As String

authKey = {my token key}

Set sht = Sheet1

Dim strUrl As String
strUrl = "https://myurl"
Set hReq = CreateObject("MSXML2.XMLHTTP")
With hReq
.Open "GET", strUrl, False
.SetRequestHeader "Authorization", "Bearer " & authKey
.Send
End With

Dim response As String
response = hReq.ResponseText

MsgBox response
End Sub

But whenever I insert the authKey bearer token I have it gives an error.

 

[ChrisFoster]

Regarding the error you're receiving, it's hard to say what the issue might be without more information. However, one possibility is that the authKey variable is not being assigned the correct value. Double-check that the token you're using is valid and correctly formatted.

Here's an example VBA code that you can use as a starting point to pull data from the web using a bearer token and convert the JSON response to XML:

Sub GetData()
    Dim xhr As Object
    Set xhr = CreateObject("MSXML2.XMLHTTP")
    
    Dim url As String
    url = "https://myurl"
    
    Dim token As String
    token = "my_token"
    
    xhr.Open "GET", url, False
    xhr.setRequestHeader "Authorization", "Bearer " & token
    xhr.send
    
    Dim json As Object
    Set json = JsonConverter.ParseJson(xhr.responseText)
    
    Dim xml As String
    xml = XmlConverter.ConvertJsonToXml(json)
    
    'Do something with the XML data, for example:
    Dim xmlDoc As Object
    Set xmlDoc = CreateObject("MSXML2.DOMDocument")
    xmlDoc.LoadXML xml
    
    'Write the XML data to a worksheet
    Dim sheet As Worksheet
    Set sheet = ThisWorkbook.Sheets("Sheet1")
    sheet.Range("A1").Value = xmlDoc.XML
End Sub

 

[lala_Hae_dong]

I think my token length is too long
When I paste the token it turns red and says identifier too long
If I try to cut and paste in sections it keeps adding single quote (") at the end of the first line.

 

[ChrisFoster]

If you're encountering an "identifier too long" error message when pasting a token into your code, it's likely that the token is too long for VBA to handle. The maximum length of an identifier in VBA is 255 characters, so if your token exceeds this limit, you'll need to break it up into smaller pieces.
You could store the token in a cell in your worksheet and retrieve it using VBA code. This approach can be useful if the token is too long to store in a string variable. Here's an example of how to retrieve a long token from a worksheet:

Dim myToken As String
myToken = Range("A1").Value

This code retrieves the value of cell A1 in your worksheet and stores it in a string variable called myToken. You can then use this variable in your code instead of the original long token, just make sure you change the cell that your token is kept.

 

[lala_Hae_dong]

so my code reads as follows but now i am getting Run-time error '10001': Error parsing JSON <!doctype h Expecting '{' or '['
Created a Sheet2 because Sheet1 A1 has the bearer key
Should i have the bearer key in "" or {""}

Sub GetData()
Dim xhr As Object
Set xhr = CreateObject("MSXML2.XMLHTTP")

Dim url As String
url = "https://xxxxxxxxx.xxxxxxxxxxxx.com/xxext-fsmapi/workorder"

Dim myToken As String
myToken = Range("A1").Value

xhr.Open "GET", url, False
xhr.setRequestHeader "Authorization", "Bearer " & token
xhr.send

Dim json As Object
Set json = JsonConverter.ParseJson(xhr.responseText)

Dim xml As String
xml = XmlConverter.ConvertJsonToXml(json)

'Do something with the XML data, for example:
Dim xmlDoc As Object
Set xmlDoc = CreateObject("MSXML2.DOMDocument")
xmlDoc.LoadXML xml

'Write the XML data to a worksheet
Dim sheet As Worksheet
Set sheet = ThisWorkbook.Sheets("Sheet2")
sheet.Range("A1").Value = xmlDoc.xml
End Sub