using "&" in google search from excel

[ajm]

hi all, i am researching a list of companies, the names of which are in a long list in excel. i am using a macro called Launch Site which i picked up a year or two ago from here or one of the other sites around. trouble is, its common to see "&" in a company name, eg "A & C Scales".

Sub LaunchSite()

Dim Newsite As Variant
Dim coyname As String
Dim X, Y, Z

X = ActiveCell.Value
Y = Replace(X, " ", "+")
Z = Replace(Y, "&", "and")
coyname = "http://www.google.com.au/search?q=" & Z & "&ie=utf-8&oe=utf-8&aq=t&rls=org.mozilla:en-GB:official&client=firefox-a"
Newsite = Shell("C:\Program Files\Mozilla Firefox\Firefox.exe " & coyname, vbNormalFocus)


End Sub

at the moment, i am replacing the ampersand with "and" otherwise, i only get a search on the characters before the ("A") in my search term. i would like to be able to just grab the name as it is without replacing the ampersand. any tips??

ajm

 

[John_w]

You need to 'escape' special characters like '&'. Also, I don't think you need all those other URL parameters.

Sub LaunchSite()

Dim Newsite As Variant
Dim coyname As String
Dim X, Y, Z

Z = ActiveCell.Value
Z = "A & C Scales"
coyname = "http://www.google.com.au/search?q=" & Escape(Z)
Newsite = Shell("e:\Program Files\Mozilla Firefox\Firefox.exe " & coyname, vbNormalFocus)

End Sub

'From http://www.tushar-mehta.com/publish_train/xl_vba_cases/vba_web_pages_services/

Function Escape(ByVal URL As String) As String
    'URLs cannot contain most special characters. VBScript and JavaScript have built-in Escape functions.
    'In VB we have to write our own
    
    Dim I As Integer, BadChars As String
    BadChars = "<>%=&!@#$^()+{[}]|\;:'"",/?"
    For I = 1 To Len(BadChars)
        URL = Replace(URL, Mid(BadChars, I, 1), "%" & Hex(Asc(Mid(BadChars, I, 1))))
    Next I
    URL = Replace(URL, " ", "+")
    Escape = URL
End Function