I'm writing a very simple macro that needs to make an HTTP GET request to a server and the response is not important (it initiates a process on the server). The HTTP GET does NOT require authentication.
I'm using the following code to do this "successfully" (the server logs indicated the request made it to the server, but the server is running HTTP 406):
Function callAPI(Url As String)
With ActiveSheet.QueryTables.Add(Connection:="URL;" & Url, Destination:=Range("D15"))
.PostText = ""
.RefreshStyle = xlOverwriteCells
.SaveData = True
.Refresh
End With
End Function
But I get back the following response from the server:
Unable to open http://someurl.com Cannot locate the Internet server or proxy server.
I can see the server is return an HTTP 406 which, after some research is occurring because the GET request is not sending the correct Content-Type
header.
So my question is - how do tell ActiveSheet.QueryTables.Add
to set the header, or how do I modify my NGINX config to support this specific GET CALL
Here is a piece of code that supports both OSX and Windows. I would credit the authors of this because I certainly didnt write this from scratch but I have lost track of where it all came from:
This wasn't working for me on Mac Excel 15.4 either. I found a helpful version on VBA-Tools Github Here:
https://github.com/VBA-tools/VBA-Web/issues/248
Here's the version that worked for me: