Hi Experts,
I am wandering if someone can help with the following:
I am using excel VBA to execute the search API and not getting any where.
I am receiving request status 401 in other word not authorised to use the API.
Code:
Private Sub CommandButton1_Click()
Dim xmlhttp As Object
Set xmlhttp = CreateObject(“MSXML2.ServerXMLHTTP.6.0”)
'Dim xmlhttp As New MSXML2.xmlhttp
'Dim xmlhttp As New MSXML2.XMLHTTP60 for Microsoft XML, v 6.0
Dim myurl As String
myurl = “https://api.companieshouse.gov.uk/search/companies/”
xmlhttp.Open “GET”, myurl, False
xmlhttp.setRequestHeader “Content-Type”, “application/xml”
xmlhttp.setRequestHeader “Authorization”, "Basic XXXXXXXXXX= " + EncodeBase64(“apiKEY:XXXXXXXXXXXXXXXXXXXXXXX”)
xmlhttp.send
MsgBox (xmlhttp.Status)
Debug.Print xmlhttp.responseText
End Sub
Function EncodeBase64(text As String) As String
Dim arrData() As Byte
arrData = StrConv(text, vbFromUnicode)
Dim objXML As MSXML2.DOMDocument
Dim objNode As MSXML2.IXMLDOMElement
Set objXML = New 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
Could someone point what is stopping this from working?
Many thanks,