1

I'm sorry if my request won't be very clear, but I'm not very familiar with the topic.

I'm trying to extract a Json database using VBA and, of course, Microsoft Excel.

My code is (simplified):

Function GetDB(ByRef MYUrl As String) As Integer

'variables declaration here ...

Set xmlHttp = CreateObject("MSXML2.ServerXMLHTTP")
xmlHttp.Open "GET", MyUrl, False
xmlHttp.setRequestHeader "Content-Type", "application/json"
xmlHttp.send ""

JsonResponse = xmlHttp.responseText
Set sc = CreateObject("ScriptControl"): sc.Language = Script"
Set json = sc.Eval("(" + jsonResponse + ")")

MyRecords = json.totalRecords

End Function

The problem is that in "MyUrl", I set different ”filters" where one has spaces in it. To make it more clear, MyUrl looks like:

MyUrl = "http://domain.xxxx.xxxx.com/pathpart1/pathpart2?DateFrom=20250620&DateTo=20250630&Folder=STRING - STRING&File=MyFile"

So the problem is about the spaces in the "STRING - STRING" part of MyUrl. Removing this part, the code works, but it does not apply the filter I would need. How can I solve?

I would like to understand how to "replace" the spaces " " in MyUrl with a character that can be read by the Object "MSXML2.ServerXMLHTTP". The rest of the Url is fine and doesn't need to be reviewed (but in its anonymized version could be imprecise, I understand). In fact, if I remove the part "Folder=STRING - STRING" the code runs smoothly (but I would need also this additional filter to get data from the Folder I want).

I tried to replace the space " " with "%20", but it does not work.

Thank you for your help

Improve this question
10
  • 1
    You may have a look onto stackoverflow.com/questions/218181/… which is probably a similar question. Commented Jun 27 at 14:24
  • To check the url add a debug.print MyUrl and then copy/paste the result into a browser. Commented Jun 27 at 14:29
  • What exactly is the content of STRING - STRING? Is it possible the "spaces" are not spaces but some other character? What exactly happens when you include the original string? You just don't get the expected results, or something else? Can you successfully target a folder which has no spaces in its name? Commented Jun 27 at 15:33
  • 2
    FYI using ScriptControl like that to decode JSON is very risky if you don't have absolute confidence that the source system won't return something unexpected in the response. The Eval() is not running in a sandboxed environment as it would if you were doing the equivalent thing in a web browser environment. Commented Jun 27 at 16:02
  • Thank you all for the answers. MyUrl is built by another routine I wrote. The spaces are just spaces added with the keyboard. I would like to highlight that I use MyUrl in another routine to create a QueryTable in Excel where data is correctly downloaded also applying the "STRING - STRING" filter with spaces. So I believe that Excel can interpret MyUrl when building the QueryTable, while the Json interrogation does not. For the ScriptControl, I did my best to understand another routine provided on the web. At the moment it works, but I'm not an expert and may need to improve my coding skills.. Commented Jul 3 at 8:20
Related questions
0
VBA Calling WSDL simple method with 1 parameter XMLHTTP
11
Excel VBA Msxml2.XMLHTTP.6.0 vs Msxml2.ServerXMLHTTP.6.0
1
VBA - MSXML2.ServerXMLHTTP GET Request - Server status 416
Load 4 more related questions Show fewer related questions

0

Reset to default

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.