1

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.

Community
  • 1
  • 1
ink49
  • 61
  • 6
  • Are you able to paste `thisURL` into a browser address bar and get the expected result? Is the service login-restricted or are there other access restrictions? You've not given us much to go on here, but you don't seem to be correctly separating the querystring parameters using `&` - `thisURL = thisURL & "?name=" & ptc & "&assets=" & assets` You may also need to URLencode `ptc` and `assets` depending on their respective values. – Tim Williams Nov 22 '21 at 18:21
  • Thank you Tim! I noticed that just a short bit ago. Thanks. Unsure how to close this one out. I'm still new to stackoverflow. – ink49 Nov 22 '21 at 18:43
  • For others using this code, you will need to add Tools/References/Microsoft XML, v 6.0 to VBA – gordon613 Feb 20 '22 at 17:53

1 Answers1

4

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?

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • Yeah, I just noticed that literally 30 seconds ago! LOL. Sometimes when you're in deep, you can't see the obvious. Thanks everyone! – ink49 Nov 22 '21 at 18:43