First time here, but I've been writing VBA apps in Excel for many years.

I've got several Excel projects that create an IE object, navigate to a url, grab the web page body, and do some processing. In particular, one app goes to a site I use and gets copies of all their KB articles.

We just implemented Vista, Office 2007, and IE 7. Now when I use this code, the ie.navigate method causes IE to open a new window, and the subsequent code fails because there is no handle to the new window.

Here is my code:

Code Block
Sub GetKBArticles()
Dim IE As Object
Dim sURL As String
Dim i As Integer

IE = CreateObject("InternetExplorer.Application")
IE.Visible = True

For i = 1 To 180
sURL = "http://<some website url>/KB" & Format(i, "000000") & ".htm"
' this loops thru urls with filenames of KB000001.htm thru KB000180.htm

IE.Navigate sURL

' at this point a new IE window is opened, causing the rest of the code to fail.

Loop While IE.Busy

Loop While IE.ReadyState <> 4

' ... processing


Set IE = Nothing

End Sub

Is it as simple as some setting in IE Or do I need to devise new code to handle this

Re: Excel 2007 VBA accessing Internet Explorer 7 object

Cringing Dragon

Have you tried using the TargetFrameName parameter to force Navigate to use your choice of window See:

Re: Excel 2007 VBA accessing Internet Explorer 7 object


I'm also having problems accessing IE (version 6) from an Excel VBA procedure. I have only a limited understanding / knowledge of the technologies involved. The following code fails with "Method 'Document' of object 'IWebBrowser2' failed" when setting the ieUIdoc. Any advice on how to debug the problem, or what the problem is would be appreciated.

Sub fred()

Set ieUIobj = CreateObject("InternetExplorer.Application")

If ieUIobj Is Nothing Then
MsgBox ("Could not create IE object")
Exit Sub
End If

ieUIobj.Visible = True
ieUIobj.navigate ("")

' The following fails with "Method 'Document' of object 'IWebBrowser2' failed"
Set ieUIdoc = ieUIobj.document

If ieUIdoc Is Nothing Then
MsgBox ("Could not create IE document")
Exit Sub
End If

End Sub

Re: Excel 2007 VBA accessing Internet Explorer 7 object

Cringing Dragon

You need to wait until the web page has finished loading before you can use that method.

Try adding a Wait line into your code after the navigate line (for example, the following line will wait 10 seconds before continuing):

Application.Wait(Now() + TimeValue("0:00:10"))

You may need to experiment with the wait time to give it enough time to load, or add a loop so that it tries a few times, a few seconds apart, before giving up.