Using VBA to get data from API with bearer token to Excel

[lala_Hae_dong]

Can this be done? How do I create the Authorization Header?
I have created the API Request , see below, but when I run I get the following message ,"{"status":"Internal Server Error","msg":[{"type":"ERROR","details":"The authorization header is either empty or isn't Basic."}]}" and I am unsure how to resolve.

Here is my current API Request

Sub SendAPIrequest()

Dim HTTPreq As Object, URL As String, response As String

Set HTTPreq = CreateObject("MSXML2.XMLHTTP")


URL = "myurl"

With HTTPreq
.Open "Get", URL, False
.send
End With

response = HTTPreq.responseText
Debug.Print response
Call ReadJSONresponse(response)

End Sub

 

[YasserKhalil]

Maybe something like that (you have to change according to the provider authorization)

Sub SendAPIrequest()
    Dim HTTPreq As Object, URL As String, response As String
    Dim authHeader As String, authCreds As String

    Set HTTPreq = CreateObject("MSXML2.XMLHTTP")

    URL = "myurl"
    authCreds = "username:password"
    authHeader = "Basic " & Base64Encode(authCreds)

    With HTTPreq
        .Open "Get", URL, False
        .setRequestHeader "Authorization", authHeader
        .send
    End With

    response = HTTPreq.responseText
    Debug.Print response
    Call ReadJSONresponse(response)
End Sub

Private Function Base64Encode(ByVal data As String) As String
    Dim enc As Object, oText As Object
    Set enc = CreateObject("System.Text.Encoding")
    Set oText = enc.EncodeBase64(enc.Getbytes_4(data))
    Base64Encode = oText
End Function

 

[lala_Hae_dong]

I must be missing something?
I got the following error

Run-time error '429':
ActiveX component can't create object

 

[YasserKhalil]

You need the .NET Framework library, which includes the System.Text.Encoding object. You can download the .NET Framework from the Microsoft website.

 

[lala_Hae_dong]

I must be doing something wrong. I downloaded .net Framework but i cant find it in my reference tools

 

[John_w]

Here's an alternative function which encodes a text string as Base64, using MSXML2 instead of System.Text.Encoding. No references are required.

Private Function EncodeBase64(plainText As String) As String

    Dim bytes() As Byte
    Dim objXML As Object 'MSXML2.DOMDocument60
    Dim objNode As Object 'MSXML2.IXMLDOMNode
    
    bytes = StrConv(plainText, vbFromUnicode)
   
    Set objXML = CreateObject("MSXML2.DOMDocument.6.0")
    Set objNode = objXML.createElement("b64")
    objNode.DataType = "bin.base64"
    objNode.nodeTypedValue = bytes
    EncodeBase64 = objNode.Text
    
    Set objNode = Nothing
    Set objXML = Nothing
    
End Function

 

[lala_Hae_dong]

Here's an alternative function which encodes a text string as Base64, using MSXML2 instead of System.Text.Encoding. No references are required.

Private Function EncodeBase64(plainText As String) As String

    Dim bytes() As Byte
    Dim objXML As Object 'MSXML2.DOMDocument60
    Dim objNode As Object 'MSXML2.IXMLDOMNode
   
    bytes = StrConv(plainText, vbFromUnicode)
  
    Set objXML = CreateObject("MSXML2.DOMDocument.6.0")
    Set objNode = objXML.createElement("b64")
    objNode.DataType = "bin.base64"
    objNode.nodeTypedValue = bytes
    EncodeBase64 = objNode.Text
   
    Set objNode = Nothing
    Set objXML = Nothing
   
End Function

Thank you that worked perfectly