using "&" in google search from excel

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,856
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".

Code:
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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,049
You need to 'escape' special characters like '&'. Also, I don't think you need all those other URL parameters.
Code:
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
 

Forum statistics

Threads
1,144,376
Messages
5,723,997
Members
422,530
Latest member
Badpoisondwarf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top