URL中&符号未编码问题：Excel取值拼接Salesforce搜索URL异常

The root cause of your broken URL in Chrome is clear: you're not encoding special characters in your search parameter. The & in your string (Ben & Jerry's 2017 Base PIC - 1) is a reserved character in URL syntax—it's used to separate query parameters. When you paste it directly into the URL, Chrome interprets Jerry's 2017 Base PIC - 1 as a new parameter instead of part of the str value, which breaks the Salesforce search URL.

Here are two reliable solutions for VBA:

1. Use Excel's Built-in EncodeURL (Excel 2013+) #

If you're running Excel 2013 or later, the simplest approach is to use WorksheetFunction.EncodeURL—it handles all standard URL encoding for special characters like &, spaces, and single quotes automatically.

Modified code:

Dim searchStr As String
' Note: If your Excel cell contains "Ben & Jerry's...", that's HTML-encoded &.
' You may need to first convert it back to a literal & using Replace, like:
' searchStr = Replace(Range("A1").Value, "&", "&")
searchStr = "Ben & Jerry's 2017 Base PIC - 1"

Dim targetUrl As String
targetUrl = "https://catalina.my.salesforce.com/_ui/search/ui/UnifiedSearchResults?searchType=2&sen=aAh&str=" & _
            WorksheetFunction.EncodeURL(searchStr) & _
            "#!/initialViewMode=summary"

This will encode your search string to Ben%20%26%20Jerry%27s%202017%20Base%20PIC%20-%201, which Chrome will parse correctly as a single parameter value.

2. Custom URL Encoding Function (Older Excel Versions) #

If you're on an older Excel version without EncodeURL, create a custom function to handle encoding:

Function URLEncode(ByVal inputStr As String) As String
    Dim byteArray() As Byte, singleByte As Byte, i As Integer
    Dim encodedStr As String
    encodedStr = ""
    
    ' Convert string to Unicode bytes
    byteArray = StrConv(inputStr, vbUnicode)
    
    For i = 0 To UBound(byteArray) Step 2
        singleByte = byteArray(i)
        Select Case singleByte
            ' Keep safe characters unencoded: letters, numbers, -._~
            Case 48 To 57, 65 To 90, 97 To 122, 45, 46, 95, 126
                encodedStr = encodedStr & Chr(singleByte)
            ' Convert spaces to %20 (more standard than + for query params)
            Case 32
                encodedStr = encodedStr & "%20"
            ' Encode all other characters as %XX
            Case Else
                encodedStr = encodedStr & "%" & Hex(singleByte)
        End Select
    Next i
    
    URLEncode = encodedStr
End Function

Then use it in your URL construction:

targetUrl = "https://catalina.my.salesforce.com/_ui/search/ui/UnifiedSearchResults?searchType=2&sen=aAh&str=" & _
            URLEncode(searchStr) & _
            "#!/initialViewMode=summary"

Key Note #

If your Excel cell contains & (HTML-encoded ampersand) instead of a literal &, make sure to first replace it with the actual & using Replace(searchStr, "&", "&") before encoding—otherwise you'll end up encoding & instead of the intended &.

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