Hi all, this is my first post and I am an excel vba hack.
I have an spreadsheet with a list of companies. Each cell contains a company name, i.e., XYZ Factory. I want to assign a hyperlink to the cell that will take the users to that company's web-site. To automate the process, I am taking the cell contents (XYZ Factory) and performing a Google "I'm feeling lucky" search. The resultant URL is then placed in a hyperlink associated with that cell.
The code runs perfectly in "debug" mode when I am stepping through. However, when I release it to run ... I encounter a Run-Time error '91'. The code stops on the following line
IEdoc.all("q").Value = searchtext
As I said, I am a bit of a hack. Do i need to reset variable or clear memory? I am at a loss.
Here is the code.
Sub geturl()
' The purpose of this macro is to iterate through a list of company names
' conduct a google search on the name
' return the "I'm feeling lucky" url
' and creating a hyperlink to that url from the active cell
Dim IEapp As Object
Dim IEdoc As Object
Dim URL As String
Dim searchtext As String
' This macro begins on a selected cell and is designed to iterate
' through all the active, non-blank cells.
Set IEapp = New InternetExplorer
URL = "http://www.google.com/"
' Launch Internet Explorer and go to the site
With IEapp
.Visible = True
.Navigate URL
End With
' Wait until Internet Explore finishes loading
While IEapp.Busy
DoEvents
Wend
Return1:
' Test active cell is non-blank. when reaching the first instance
' of a blank cell. The macro will end.
If IsEmpty(ActiveCell.Value) = True Then GoTo Stop1
' Test the cell to the immediate right to see if the company is a
' duplicate. If duplicate, skip to the next unique company.
If ActiveCell.Offset(0, 1).Value = 0 Then GoTo Next1
' set the search text equal to the company name
searchtext = ActiveCell.Value
' Insert search text
Set IEdoc = IEapp.Document
IEdoc.all("q").Value = searchtext
' Wait till the page is loaded
While IEapp.Busy
DoEvents
Wend
' Clicks the button named "btnI" which is google's I'm feeling lucky search button
IEdoc.all("btni").Click
While IEapp.Busy
DoEvents 'wait until IE is done loading page.
Wend
' return the company url
compurl = IEdoc.URL
' Create hyperlink
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=compurl, _
TextToDisplay:=searchtext
' return IE back to google
With IEapp
.Navigate URL
End With
Next1:
' Go to next active cell
ActiveCell.Offset(1, 0).Select
GoTo Return1
Stop1:
End Sub
Bookmarks