CODEWITHSUNDEEP
vbaexcel
shettyrish
shettyrish

Reputation: 99

Ampersand not passed in url as encoded character

I am passing a url using a string from a cell in an excel workbook. The string is : Ben & Jerry's 2017 Base PIC - 1.

The following is my code to pass the string in a url:

Dim targetUrl As String: targetUrl = 

"https://catalina.my.salesforce.com/_ui/search/ui/UnifiedSearchResults?searchType=2&sen=aAh&str=" & i & "#!/initialViewMode=summary"

where 'i' is the string that is passed.

This is the url obtained in chrome :

  https://catalina.my.salesforce.com/_ui/search/ui/UnifiedSearchResults?str=Ben+&+Jerry%27s+2017+Base+PIC+-+1+=&searchType=2&sen=aAh#!/fen=aAh&initialViewMode=detail&str=Ben

As you can see, the '&' isn't encoded and the search doesn't include any value after 'Ben'.

Could someone guide me on how to solve this issue? Thank you.

Upvotes: 3

Views: 818

Answers (1)

Florent B.
Florent B.

Reputation: 42538

To encode the parameter:

Sub Test()
  Dim url As String

  url = "https://catalina.my.salesforce.com/_ui/search/ui/UnifiedSearchResults" _
    & "?searchType=2" _
    & "&sen=aAh" _
    & "&str=" & EncodeURL("Ben & Jerry's 2017 Base PIC - 1") _
    & "#!/initialViewMode=summary"

  Debug.Print url
End Sub

Public Function EncodeURL(url As String) As String
  Dim buffer As String, i As Long, c As Long, n As Long
  buffer = String$(Len(url) * 12, "%")

  For i = 1 To Len(url)
    c = AscW(Mid$(url, i, 1)) And 65535

    Select Case c
      Case 48 To 57, 65 To 90, 97 To 122, 45, 46, 95  ' Unescaped 0-9A-Za-z-._ '
        n = n + 1
        Mid$(buffer, n) = ChrW(c)
      Case Is <= 127            ' Escaped UTF-8 1 bytes U+0000 to U+007F '
        n = n + 3
        Mid$(buffer, n - 1) = Right$(Hex$(256 + c), 2)
      Case Is <= 2047           ' Escaped UTF-8 2 bytes U+0080 to U+07FF '
        n = n + 6
        Mid$(buffer, n - 4) = Hex$(192 + (c \ 64))
        Mid$(buffer, n - 1) = Hex$(128 + (c Mod 64))
      Case 55296 To 57343       ' Escaped UTF-8 4 bytes U+010000 to U+10FFFF '
        i = i + 1
        c = 65536 + (c Mod 1024) * 1024 + (AscW(Mid$(url, i, 1)) And 1023)
        n = n + 12
        Mid$(buffer, n - 10) = Hex$(240 + (c \ 262144))
        Mid$(buffer, n - 7) = Hex$(128 + ((c \ 4096) Mod 64))
        Mid$(buffer, n - 4) = Hex$(128 + ((c \ 64) Mod 64))
        Mid$(buffer, n - 1) = Hex$(128 + (c Mod 64))
      Case Else                 ' Escaped UTF-8 3 bytes U+0800 to U+FFFF '
        n = n + 9
        Mid$(buffer, n - 7) = Hex$(224 + (c \ 4096))
        Mid$(buffer, n - 4) = Hex$(128 + ((c \ 64) Mod 64))
        Mid$(buffer, n - 1) = Hex$(128 + (c Mod 64))
    End Select
  Next

  EncodeURL = Left$(buffer, n)
End Function

Upvotes: 4

Related Questions