Excel parses Hebrew chars to Google as gibberish

Question

I'm using VB to parse values form Excel spreadsheet to Google search.

ActiveWorkbook.FollowHyperlink Address:="http://www.google.com./search?hl=en&q=" + Sheet1.name1, NewWindow:=True

When I try to parse Hebrew chars I get gibberish. Can't find the reason for this...

Answer 1

It's an encoding issue, you can either change encoding, which is kind of complicated, because excel defaults to your system encoding.

I think the easiest thing to do in your case is to URL Encode your query.

Prior to Excel 2013, use this can be done with this snippet, taken from here

Public Function URLEncode( _
   StringVal As String, _
   Optional SpaceAsPlus As Boolean = False _
) As String

  Dim StringLen As Long: StringLen = Len(StringVal)

  If StringLen > 0 Then
    ReDim result(StringLen) As String
    Dim i As Long, CharCode As Integer
    Dim Char As String, Space As String

    If SpaceAsPlus Then Space = "+" Else Space = "%20"

    For i = 1 To StringLen
      Char = Mid$(StringVal, i, 1)
      CharCode = Asc(Char)
      Select Case CharCode
        Case 97 To 122, 65 To 90, 48 To 57, 45, 46, 95, 126
          result(i) = Char
        Case 32
          result(i) = Space
        Case 0 To 15
          result(i) = "%0" & Hex(CharCode)
        Case Else
          result(i) = "%" & Hex(CharCode)
      End Select
    Next i
    URLEncode = Join(result, "")
  End If
End Function

In Excel 2013, you can use the EncodeUrl function as such

ActiveWorkbook.FollowHyperlink Address:="http://www.google.com./search?hl=en&q=" + WorksheetFunction.EncodeUrl(Sheet1.name1), NewWindow:=True