CODEWITHSUNDEEP
javascriptexcelvbaazure
ink49
ink49

Reputation: 61

How to construct URL with VBA?

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

Answers (1)

Tim Williams
Tim Williams

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

Related Questions