Excel cells values with special letters not recognized when sent to Google Search via VBA

Question

I have an Excel file with cells containing words written with special characters (é, î, ù, etc.) that I sent to Google search. I use VBA for that and it seems VBA recognize them without any problem when using the MsgBox. But when those words appears in Google Search Bar, the accent letters turn into question marks (ex.: île becomes ?le).

(My regional settings already displayed to be able to read those special letters.)

Here is my VBA Code:

Sub SpecialLetters()
Dim objIe As Object

Set objIe = CreateObject("InternetExplorer.Application")
objIe.Visible = True

objIe.navigate "http://www.google.com/search?hl=en&ie=UTF-8&q=" & Sheets("Sheet1").Range("A1").Value
    
End Sub

DecimalTurn · Accepted Answer · 2020-08-23T21:16:04.653

To avoid this, you can encode the search term in the standard URL manner.

If you have Excel 2013 or higher, you can use WorksheetFunction.EncodeURL to do that. Your code would then be:

Sub SpecialLetters()
Dim objIe As Object

Set objIe = CreateObject("InternetExplorer.Application")
objIe.Visible = True

objIe.Navigate "http://www.google.com/search?hl=en&ie=UTF-8&q=" & WorksheetFunction.EncodeURL(Sheets("Sheet1").Range("A1").Value)
    
End Sub

For Excel 2010 or lower, There was no way to do that without creating your own function, but luckily Tomalak already provided a function to do that in VBA (see here), you can then simply use this :

Public Function URLEncode( _
   ByVal StringVal As String, _
   Optional SpaceAsPlus As Boolean = False _
) As String
  Dim bytes() As Byte, b As Byte, i As Integer, space As String

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

  If Len(StringVal) > 0 Then
    With New ADODB.Stream
      .Mode = adModeReadWrite
      .Type = adTypeText
      .Charset = "UTF-8"
      .Open
      .WriteText StringVal
      .Position = 0
      .Type = adTypeBinary
      .Position = 3 ' skip BOM
      bytes = .Read
    End With

    ReDim result(UBound(bytes)) As String

    For i = UBound(bytes) To 0 Step -1
      b = bytes(i)
      Select Case b
        Case 97 To 122, 65 To 90, 48 To 57, 45, 46, 95, 126
          result(i) = Chr(b)
        Case 32
          result(i) = space
        Case 0 To 15
          result(i) = "%0" & Hex(b)
        Case Else
          result(i) = "%" & Hex(b)
      End Select
    Next i

    URLEncode = Join(result, "")
  End If
End Function

Make sure that you have a reference to the Microsoft ActiveX Data Objects Library for it to work.

And your code would become:

Sub SpecialLetters()
Dim objIe As Object

Set objIe = CreateObject("InternetExplorer.Application")
objIe.Visible = True

objIe.navigate "http://www.google.com/search?hl=en&ie=UTF-8&q=" & URLEncode(Sheets("Sheet1").Range("A1").Value)
    
End Sub

Working perfectly. I appreciate your clear and detailed explanations. — Mister Propre, Aug 23 '20 at 09:57

score 3 · Answer 2 · answered Aug 23 '20 at 08:33

3

Try EncodeURL function, please (it works in Office installations after 2010 version):

Sub SpecialLetters()
Dim objIe As Object

Set objIe = CreateObject("InternetExplorer.Application")
objIe.Visible = True

  objIe.navigate "http://www.google.com/search?hl=en&ie=UTF-8&q=" & _
         WorksheetFunction.EncodeURL(Sheets("Sheet1").Range("A1").Value)
End Sub

answered Aug 23 '20 at 08:33

FaneDuru

38,298
4
19
27

Working perfectly! I didn't know about that function. Thanks a lot. – Mister Propre Aug 23 '20 at 10:00
@Mister Propre: Glad I could help! But we here, when somebody answer our question, tick the code left side check box, in order to make it **accepted answer**. In this way, somebody else searching for a similar issue will know that the code works... :) – FaneDuru Aug 23 '20 at 10:10

Excel cells values with special letters not recognized when sent to Google Search via VBA

2 Answers2