Run-time error '91' with IE App

[cadkins]

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.

Please Login or Register  to view this content.

[Leith Ross]

Hello cadkins,

Welcome to the forum!

The problem has to with timing. While stepping through the code, more time passes allowing for the web page to load. You're probably saying, "I have a loop to tell me when the web page has finished loading." The method you used works, but is not reliable. The browser may not be busy, but that doesn't mean the page is completed loaded. This is signaled by the ReadyState property being set to 4.

To make your macro more robust, I have added a time out routine. Currently the time out period is 10 seconds. You can make this longer or shorter if wish. Once the time out occurs, a message box informs you a time out has occurred. The web browser is displayed after dismissing the message, so you can see why this happened.

The API calls are needed to switch between Excel and Internet Explorer. Without these calls, the message boxes will be hidden until the user clicks on cell in Excel. During a time out, the browser window must be displayed on top of Excel.

Please Login or Register  to view this content.

[cadkins]

Thanks, I am putting the code in now an walking through it. This is a great learning experience and I appreciate your time in helping my fix this problem. I'll be back in a few to post the results.