401 Code - API Problem in Excel VBA

API Issues
#1

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,

How to get data into a spreadsheet?
Excel Data Links
#3

bril Matt.

It did work.

Thanks.

#4

Hi, The code above works great, thank you.
Can anyone direct me on how to use the data contained in the responseText please? Is there a way to get the xml data instead?
Thanks

#5

If you wanted info on how to use what’s returned by the MSXML2.xmlhttp object or whatever is the current equivalent (it seems that this library may no longer be supported) then try:

…or see Microsoft documentation:
https://support.microsoft.com/en-gb/help/290591/how-to-submit-form-data-by-using-xmlhttp-or-serverxmlhttp-object

You say “get XML” - the Companies House API doesn’t return XML. It returns JSON. (I don’t use VBA presently but it looks like the XMLHTTP object is the standard for making http requests in general from VBA within e.g. Excel - whether or not you’re dealing with XML). You get back a string (in xmlhttp.responseText if xmlhttp is your object as in the original code.

If you really needed XML you could then convert this JSON string to that. If instead you just wanted to use the data there are libraries for this e.g. https://github.com/VBA-tools/VBA-JSON or methods e.g. https://www.mrexcel.com/forum/excel-questions/898899-json-api-excel.html#post4332075 - note I haven’t used any of these myself.

For documentation on what is returned from Companies House, see the docs e.g. in this case the Company Search object.

If you specifically want XML there is a separate service which returns this, the XML gateway, predating the API. It covers much of the same information - but not necessarily the same data! So it is not directly equivalent. It costs a small amount to subscribe and there are additional charges for e.g. downloading documents.