Ah, I’ve run into this exact issue before—VBA’s default encoding behavior doesn’t play nice with APIs that strictly require UTF-8, like Nominatim. The problem here is that VBA’s built-in URLEncode function uses your system’s default code page (usually Windows-1252) instead of UTF-8, which mangles non-ASCII characters like the umlaut in "Schönefeld". Browsers automatically handle UTF-8 encoding for URLs, which is why your request works there but not in VBA.

Here’s a step-by-step solution to properly send UTF-8 encoded requests to Nominatim:

1. Create a Custom UTF-8 URL Encoding Function #

First, we need a function that converts strings to UTF-8 bytes and URL-encodes them correctly. Replace VBA’s default URLEncode with this:

Function URLEncodeUTF8(ByVal strInput As String) As String
    Dim unicodeBytes() As Byte
    Dim utf8Bytes() As Byte
    Dim i As Integer
    Dim hexChar As String
    
    ' Convert input string to Unicode bytes
    unicodeBytes = StrConv(strInput, vbUnicode)
    
    ' Convert Unicode bytes to UTF-8
    utf8Bytes = ConvertToUTF8(unicodeBytes)
    
    ' URL-encode each UTF-8 byte
    For i = LBound(utf8Bytes) To UBound(utf8Bytes)
        hexChar = Hex(utf8Bytes(i))
        If Len(hexChar) = 1 Then hexChar = "0" & hexChar
        URLEncodeUTF8 = URLEncodeUTF8 & "%" & hexChar
    Next i
End Function

Function ConvertToUTF8(ByRef unicodeBytes() As Byte) As Byte()
    Dim utf8Bytes() As Byte
    Dim i As Integer, j As Integer
    Dim codePoint As Integer
    
    ' Resize array to max possible UTF-8 size (3 bytes per Unicode char)
    ReDim utf8Bytes(0 To UBound(unicodeBytes) * 3)
    
    j = 0
    i = 0
    Do While i <= UBound(unicodeBytes)
        codePoint = unicodeBytes(i) + (unicodeBytes(i + 1) * 256)
        i = i + 2
        
        Select Case codePoint
            Case &H0 To &H7F
                ' 1-byte UTF-8
                utf8Bytes(j) = codePoint
                j = j + 1
            Case &H80 To &H7FF
                ' 2-byte UTF-8
                utf8Bytes(j) = &HC0 Or (codePoint \ &H40)
                utf8Bytes(j + 1) = &H80 Or (codePoint And &H3F)
                j = j + 2
            Case &H800 To &HFFFF
                ' 3-byte UTF-8
                utf8Bytes(j) = &HE0 Or (codePoint \ &H1000)
                utf8Bytes(j + 1) = &H80 Or ((codePoint \ &H40) And &H3F)
                utf8Bytes(j + 2) = &H80 Or (codePoint And &H3F)
                j = j + 3
        End Select
    Loop
    
    ' Trim array to actual size
    ReDim Preserve utf8Bytes(0 To j - 1)
    ConvertToUTF8 = utf8Bytes
End Function

2. Build and Send the UTF-8 Encoded Request #

Now use this function to encode your address parameter, then construct the full URL and send the request with proper UTF-8 headers:

Sub FetchChargingStations()
    Dim baseUrl As String
    Dim targetAddress As String
    Dim encodedAddress As String
    Dim fullRequestUrl As String
    Dim httpRequest As Object
    
    ' Base Nominatim API endpoint
    baseUrl = "https://nominatim.openstreetmap.org/search?amenity=charging_station&format=json&q="
    
    ' Your target address with non-ASCII characters
    targetAddress = "Elly-Beinhorn-Ring 2,12529 Schönefeld"
    
    ' Encode the address using our UTF-8 function
    encodedAddress = URLEncodeUTF8(targetAddress)
    
    ' Assemble the full URL
    fullRequestUrl = baseUrl & encodedAddress
    
    ' Initialize HTTP request object (use MSXML2.XMLHTTP.6.0 for better encoding support)
    Set httpRequest = CreateObject("MSXML2.XMLHTTP.6.0")
    
    ' Open and send the request, specifying UTF-8 content type
    With httpRequest
        .Open "GET", fullRequestUrl, False
        .setRequestHeader "Content-Type", "application/x-www-form-urlencoded; charset=UTF-8"
        .send
        
        ' Check for successful response
        If .Status = 200 Then
            ' Output the JSON response (you'll need a JSON parser like VBA-JSON to process this)
            Debug.Print .responseText
        Else
            Debug.Print "Request failed: " & .Status & " - " & .statusText
        End If
    End With
    
    ' Clean up
    Set httpRequest = Nothing
End Sub

Key Notes: #

• Why this works: The custom URLEncodeUTF8 function converts the address to UTF-8 bytes (e.g., "ö" becomes %C3%B6 instead of Windows-1252's %F6), which is what Nominatim expects.
• Use MSXML2.XMLHTTP.6.0: Older versions of the XMLHTTP object may not handle UTF-8 correctly, so stick with version 6.0.
• Parsing JSON: To work with the response, you’ll need a JSON parser for VBA—search for "VBA-JSON" to find a popular library that can parse the UTF-8 response correctly.

内容的提问来源于stack exchange，提问作者Jan