64位Excel 365中VBA调用谷歌距离API遇特殊字符问题

核心问题原因 #

谷歌API要求请求参数使用UTF-8编码的URL编码，而VBA默认字符串编码为ANSI（依赖系统区域设置），直接传入含特殊字符（如ä）的地址会导致参数格式错误，触发Invalid 'origins' parameter提示。

可靠解决方案：实现UTF-8 URL编码 #

不能通过简单替换特殊字符解决问题，需在VBA中手动实现符合要求的UTF-8编码转义，以下是完整代码示例：

1. 新增UTF-8 URL编码函数 #

Function URLEncodeUTF8(strInput As String) As String
    Dim bytes() As Byte
    Dim i As Integer
    Dim charCode As Integer
    Dim result As String
    
    ' 将字符串转换为UTF-8字节数组
    bytes = StrConv(strInput, vbUnicode)
    ReDim Preserve bytes(UBound(bytes) - 1) ' 移除末尾空字节
    
    For i = LBound(bytes) To UBound(bytes) Step 2
        charCode = bytes(i) + (bytes(i + 1) * 256)
        Select Case charCode
            ' 保留URL合法字符，无需转义
            Case 48 To 57, 65 To 90, 97 To 122, 45, 46, 95, 126
                result = result & Chr(charCode)
            Case Else
                ' 对特殊字符进行UTF-8编码转义
                If charCode < 128 Then
                    result = result & "%" & Hex(charCode)
                ElseIf charCode < 2048 Then
                    result = result & "%" & Hex(&HC0 Or (charCode \ 64)) & _
                             "%" & Hex(&H80 Or (charCode Mod 64))
                Else
                    result = result & "%" & Hex(&HE0 Or (charCode \ 4096)) & _
                             "%" & Hex(&H80 Or ((charCode \ 64) Mod 64)) & _
                             "%" & Hex(&H80 Or (charCode Mod 64))
                End If
        End Select
    Next i
    
    URLEncodeUTF8 = result
End Function

2. 修改API调用代码，使用编码后的地址 #

Sub CallGoogleDistanceAPI()
    Dim xmlHttp As Object
    Dim apiKey As String
    Dim originAddr As String
    Dim destAddr As String
    Dim apiUrl As String
    Dim responseText As String
    
    ' 初始化参数
    apiKey = "你的谷歌API密钥"
    originAddr = "München, Germany" ' 含特殊字符ä的测试地址
    destAddr = "Berlin, Germany"
    
    ' 对地址进行UTF-8 URL编码
    originAddr = URLEncodeUTF8(originAddr)
    destAddr = URLEncodeUTF8(destAddr)
    
    ' 拼接合法的API请求URL
    apiUrl = "https://maps.googleapis.com/maps/api/distancematrix/json?" & _
             "origins=" & originAddr & _
             "&destinations=" & destAddr & _
             "&key=" & apiKey
    
    ' 发送请求（64位Excel兼容MSXML2.XMLHTTP.6.0）
    Set xmlHttp = CreateObject("MSXML2.XMLHTTP.6.0")
    xmlHttp.Open "GET", apiUrl, False
    xmlHttp.Send
    
    ' 获取并处理响应
    responseText = xmlHttp.ResponseText
    Debug.Print responseText ' 在VBA立即窗口查看响应结果
    
    Set xmlHttp = Nothing
End Sub

关键说明 #

• 编码函数严格遵循UTF-8规则，可处理所有Unicode特殊字符（ä、ö、ü、é等），避免手动替换的不可靠性。
• 使用MSXML2.XMLHTTP.6.0确保64位Excel兼容性，且请求发送时编码格式符合谷歌API要求。
• 此方法可精确控制请求次数，避免Power Query重复调用导致的额外付费成本。

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