Reputation: 61
I was able to type in a URL azure function and get it to display the results through the body object/method.
I have been trying to retrieve a return from the Azure function through VBA.
I get an "Internal Error" message.
When I copy the URL I create from VBA into the address bar, same thing.
The Javascript in the code has "return ("Hello World!");" for testing of a responseText but I can't get VBA to even grab that because of the HTTP 500 Internal Server error.
The Azure function is operating as an API and I am doing a GET method to call it and bring back a calculation.
Dim xmlhttpb As New MSXML2.XMLHTTP60
Dim resString As String
Dim thisURL As String
'example URL
thisURL = "https://someazurefunction.net/api/somefunc"
thisURL = thisURL & "?name=" & ptc & "assets=" & assets & ""
'I have also constructed it in one line
xmlhttpb.Open "GET", thisURL, False
xmlhttpb.Send
Debug.Print xmlhttpb.StatusText 'This returns an Internal Server Error
resString = xmlhttpb.responseText
' resString, when printed, is absolutely nothing, not even a "Null" or "Empty" due to the Internal Server error
As a refresher, I can type in the URL and adjust the parameters and it responds in the body successfully.
I am able to run it locally and from Azure in Visual Studio Code and by typing in the URL and hitting "enter".
Constructing a URL in VBA does not seem to be successful when executing the GET method and hence, no responseText.
Upvotes: 1
Views: 750
Reputation: 166980
Missing &
to separate your querystring parameters:
thisURL = "https://someazurefunction.net/api/somefunc"
thisURL = thisURL & "?name=" & ptc & "&assets=" & assets
Should probably also be URL-encoding ptc
and assets
How can I URL encode a string in Excel VBA?
Upvotes: 4