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.

3
  • 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. Commented Nov 22, 2021 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. Commented Nov 22, 2021 at 18:43
  • For others using this code, you will need to add Tools/References/Microsoft XML, v 6.0 to VBA Commented Feb 20, 2022 at 17:53

1 Answer 1

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?

Sign up to request clarification or add additional context in comments.

1 Comment

Yeah, I just noticed that literally 30 seconds ago! LOL. Sometimes when you're in deep, you can't see the obvious. Thanks everyone!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.