HTTP GET with VBA and special characters in URL

Question

I'm trying to communicate with Google Distance API via VBA in 64bit Excel 365, but having trouble with special character input in street address data. Other threads having similar issues with other HTTP requests, though with output and I struggle to relate.

Sub Check_routes()

Dim objRequest, Json As Object
Dim apiKey, apiURL, myOrigin, myDest As String

Set objRequest = CreateObject("MSXML2.XMLHTTP")

myOrigin = "Tähetorni+1+Tallinn"
myDest = "Sääse+2+Tallinn"
apiURL = "https://maps.googleapis.com/maps/api/distancematrix/json?units=metric&origins=" & myOrigin & "&destinations=" & myDest & "&mode=driving&key=" & apiKey
                                                                                              
objRequest.Open "GET", apiURL, False
objRequest.send

...

Set objRequest = Nothing

End Sub

I'm getting an error response in JSON back stating Invalid 'origins' parameter. The problem is special character ä in origin & destination. Replacing this with a fixes it, but that's not a reliable solution. Is my problem string not being UTF-8 as Google stipulates? https://developers.google.com/maps/documentation/distance-matrix/web-service-best-practices#BuildingURLs

Power Query in Excel environment is also perfectly capable in communicating with Google Distance API and has no problem handling special characters - and is a more convenient tool on top of that, however annoyingly generates duplicate requests which is less than ideal with a paid API such as Google Distance. That's the lone reason for going with VBA here.

Is this a MSXML2.XMLHTTP object problem which cannot handle my input string as is?

Apparently you need to properly URL-Encode those characters. I fed your two addresses into a URL Encoder and fed those encoded URL's into the api call. Resulted in a distance of 3.2 mi or 5.2 km which seems correct. — Ron Rosenfeld
– Ron Rosenfeld, Commented Jul 17, 2022 at 20:43

Ron Rosenfeld · Accepted Answer · 2022-07-17 23:28:03Z

2

I suggest you change these two lines in your code:

myOrigin = WorksheetFunction.EncodeURL("Tähetorni 1 Tallinn")
myDest = WorksheetFunction.EncodeURL("Sääse 2 Tallinn")

Your code returns a valid string with those changes (also note I replaced the + with <space> as the function will take care of encoding that also).

By the way, you need to specify data type for each element in a declaration string. They will NOT take the data type of the last element. Rather they will be of type Variant when not specified.

So change these lines also:

Dim objRequest as Object, Json As Object
Dim apiKey as String, apiURL as String, myOrigin as String, myDest As String

answered Jul 17, 2022 at 23:28

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges

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

1 Comment

dotsent12 Over a year ago

Thank you, works brilliantly. Didn't realize there's native formula for that in Excel. As for declaring variables - it wasn't a typo, I did think it works like that too. Thanks again for pointing that out too.

Collectives™ on Stack Overflow

HTTP GET with VBA and special characters in URL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related