HTTP GET with VBA and special characters in URL-CodePudding

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?

CodePudding user response：

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