Results 1 to 3 of 3

Run-time error '91' with IE App

Threaded View

  1. #1
    Registered User
    Join Date
    12-01-2010
    Location
    Savannah, Georgia
    MS-Off Ver
    Excel 2003
    Posts
    2

    Run-time error '91' with IE App

    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
    Last edited by cadkins; 12-02-2010 at 02:40 PM. Reason: Added Code Tags

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1