Vista, IE7 and Excel 2000

K

Kevin

The following code used to work - a year or two ago.

Sub X()
Dim IE As Object
Set IE = Nothing
Set IE = CreateObject("InternetExplorer.Application")
With IE
.Navigate "http://www.yahoo.com"
Do Until Not .Busy And .ReadyState <> 4
DoEvents
Loop
ReturnURLcontent$ = .Document.Body.innerText
.Quit
End With
Set IE = Nothing
End Sub

Now I get the following error at the ReturnURLcontent$ line.
Run-time error '-2147467259 (80004005)':
Method 'Document' of object 'IWebBrowser2' failed

I've also tried the following.

Sub Y()
Const READYSTATE_COMPLETE& = 4&
Dim IE As Object
Set IE = Nothing
Set IE = CreateObject("InternetExplorer.Application")
With IE
.Navigate "http://www.yahoo.com"
While Not .ReadyState = READYSTATE_COMPLETE
DoEvents
Wend
.Quit
End With
Set IE = Nothing
End Sub

I get the following error after the While Not... line
Run-time error '-2147417848 (80010108)':
Automation Error
The object invoked has diconeccted from its clients.

Any help with be greatly appreciated.

Thanks much.
 
B

Barb Reinhardt

I think I'd try this. I'm on XP, IE7 and Excel 2003.

Sub X()
'Need Microsoft Internet Controls reference selected
Dim myIE As SHDocVw.InternetExplorer
Set myIE = Nothing
Set myIE = CreateObject("InternetExplorer.Application")
myIE.Visible = True
With myIE
.Navigate "http://www.yahoo.com"
Do Until Not .Busy And .ReadyState = READYSTATE_COMPLETE
Debug.Print .Busy
Debug.Print .ReadyState

DoEvents
Loop
ReturnURLcontent$ = .Document.Body.innerText
.Quit
End With
Set myIE = Nothing
End Sub
 
K

Kevin

Thanks Barb. Specifying the IE object type seemed to have an affect. I'm
still trouble shooting. I've tested the following six configurations. Only
Sub CC completes without error. Looks like [Do Until Not .Busy And
..ReadyState <> 4] is working but only when there's no msgbox after navigate.
And it looks like I've isolated the problem to IE.Document.Body.InnerText. I
do have Microsoft Internet Controls reference selected.

Sub A()
Dim IE As Object
Set IE = Nothing
Set IE = CreateObject("InternetExplorer.Application")
MsgBox "Set IE ok"
With IE
.Navigate "http://www.yahoo.com"
MsgBox "IE.Navigate ok"
Do Until Not .Busy And .ReadyState <> 4
'get the following error message at this point
'Run-time error '462'
'The remote server machine does not exist or is unavailable
DoEvents
Loop
.Quit
MsgBox "IE.Quit ok"
End With
Set IE = Nothing
End Sub

Sub B()
'same as A except delete [MsgBox "IE.Navigate ok"]
Dim IE As Object
Set IE = Nothing
Set IE = CreateObject("InternetExplorer.Application")
MsgBox "Set IE ok"
With IE
.Navigate "http://www.yahoo.com"
Do Until Not .Busy And .ReadyState <> 4
'get the following error message at this point
'Run-time error '-2147467259 (80004005)':
'Method 'Busy' of object 'IWebBrowser2' failed
DoEvents
Loop
.Quit
MsgBox "IE.Quit ok"
End With
Set IE = Nothing
End Sub

Sub CC()
'same as B except [Dim IE As SHDocVw.InternetExplorer]
Dim IE As SHDocVw.InternetExplorer
Set IE = Nothing
Set IE = CreateObject("InternetExplorer.Application")
MsgBox "Set IE ok"
With IE
.Navigate "http://www.yahoo.com"
Do Until Not .Busy And .ReadyState <> 4
DoEvents
Loop
.Quit
MsgBox "IE.Quit ok"
End With
Set IE = Nothing
End Sub

Sub D()
'same as CC except add [MsgBox "IE.Navigate ok"]
Dim IE As SHDocVw.InternetExplorer
Set IE = Nothing
Set IE = CreateObject("InternetExplorer.Application")
MsgBox "Set IE ok"
With IE
.Navigate "http://www.yahoo.com"
MsgBox "IE.Navigate ok"
Do Until Not .Busy And .ReadyState <> 4
'get the following error message at this point
'Run-time error '462'
'The remote server machine does not exist or is unavailable
DoEvents
Loop
.Quit
MsgBox "IE.Quit ok"
End With
Set IE = Nothing
End Sub

Sub E()
'same as CC except add [ReturnURLcontent$ = .Document.Body.InnerText]
Dim IE As SHDocVw.InternetExplorer
Set IE = Nothing
Set IE = CreateObject("InternetExplorer.Application")
MsgBox "Set IE ok"
With IE
.Navigate "http://www.yahoo.com"
Do Until Not .Busy And .ReadyState <> 4
DoEvents
Loop
ReturnURLcontent$ = .Document.Body.InnerText
'get the following error message at this point
'Run-time error '-2147467259 (80004005)':
'Automation error
'Unspecified error
.Quit
MsgBox "IE.Quit ok"
End With
Set IE = Nothing
End Sub

Sub F()
'same as E except [IE.ReadyState = READYSTATE_COMPLETE]
Dim IE As SHDocVw.InternetExplorer
Set IE = Nothing
Set IE = CreateObject("InternetExplorer.Application")
MsgBox "Set IE ok"
With IE
.Navigate "http://www.yahoo.com"
Do Until Not .Busy And .ReadyState = READYSTATE_COMPLETE
'get the following error message at this point
'Run-time error '-2147467259 (80004005)':
'Automation error
'Unspecified error
DoEvents
Loop
ReturnURLcontent$ = .Document.Body.InnerText
.Quit
MsgBox "IE.Quit ok"
End With
Set IE = Nothing
End Sub
 
K

Kevin

I used XP, IE6 and Excel 2003 and got the following to work. So I'm
concluding that there is something about the combination of Vista, IE7 and
Excel 2000 that is giving me this problem. I'm guessing a Vista, IE7 and
Excel 2003 or Excel 2007 configuration will solve my problem. Any thoughts?
Thanks.

Sub G()
'same as [Sub F] except add [MsgBox ReturnURLcontent$]
Dim IE As SHDocVw.InternetExplorer
Set IE = Nothing
Set IE = CreateObject("InternetExplorer.Application")
MsgBox "Set IE ok"
With IE
.Navigate "http://www.yahoo.com"
Do Until Not .Busy And .ReadyState = READYSTATE_COMPLETE
DoEvents
Loop
ReturnURLcontent$ = .Document.Body.InnerText
MsgBox ReturnURLcontent$
.Quit
MsgBox "IE.Quit ok"
End With
Set IE = Nothing
End Sub

Kevin said:
Thanks Barb. Specifying the IE object type seemed to have an affect. I'm
still trouble shooting. I've tested the following six configurations. Only
Sub CC completes without error. Looks like [Do Until Not .Busy And
.ReadyState <> 4] is working but only when there's no msgbox after navigate.
And it looks like I've isolated the problem to IE.Document.Body.InnerText. I
do have Microsoft Internet Controls reference selected.

Sub A()
Dim IE As Object
Set IE = Nothing
Set IE = CreateObject("InternetExplorer.Application")
MsgBox "Set IE ok"
With IE
.Navigate "http://www.yahoo.com"
MsgBox "IE.Navigate ok"
Do Until Not .Busy And .ReadyState <> 4
'get the following error message at this point
'Run-time error '462'
'The remote server machine does not exist or is unavailable
DoEvents
Loop
.Quit
MsgBox "IE.Quit ok"
End With
Set IE = Nothing
End Sub

Sub B()
'same as A except delete [MsgBox "IE.Navigate ok"]
Dim IE As Object
Set IE = Nothing
Set IE = CreateObject("InternetExplorer.Application")
MsgBox "Set IE ok"
With IE
.Navigate "http://www.yahoo.com"
Do Until Not .Busy And .ReadyState <> 4
'get the following error message at this point
'Run-time error '-2147467259 (80004005)':
'Method 'Busy' of object 'IWebBrowser2' failed
DoEvents
Loop
.Quit
MsgBox "IE.Quit ok"
End With
Set IE = Nothing
End Sub

Sub CC()
'same as B except [Dim IE As SHDocVw.InternetExplorer]
Dim IE As SHDocVw.InternetExplorer
Set IE = Nothing
Set IE = CreateObject("InternetExplorer.Application")
MsgBox "Set IE ok"
With IE
.Navigate "http://www.yahoo.com"
Do Until Not .Busy And .ReadyState <> 4
DoEvents
Loop
.Quit
MsgBox "IE.Quit ok"
End With
Set IE = Nothing
End Sub

Sub D()
'same as CC except add [MsgBox "IE.Navigate ok"]
Dim IE As SHDocVw.InternetExplorer
Set IE = Nothing
Set IE = CreateObject("InternetExplorer.Application")
MsgBox "Set IE ok"
With IE
.Navigate "http://www.yahoo.com"
MsgBox "IE.Navigate ok"
Do Until Not .Busy And .ReadyState <> 4
'get the following error message at this point
'Run-time error '462'
'The remote server machine does not exist or is unavailable
DoEvents
Loop
.Quit
MsgBox "IE.Quit ok"
End With
Set IE = Nothing
End Sub

Sub E()
'same as CC except add [ReturnURLcontent$ = .Document.Body.InnerText]
Dim IE As SHDocVw.InternetExplorer
Set IE = Nothing
Set IE = CreateObject("InternetExplorer.Application")
MsgBox "Set IE ok"
With IE
.Navigate "http://www.yahoo.com"
Do Until Not .Busy And .ReadyState <> 4
DoEvents
Loop
ReturnURLcontent$ = .Document.Body.InnerText
'get the following error message at this point
'Run-time error '-2147467259 (80004005)':
'Automation error
'Unspecified error
.Quit
MsgBox "IE.Quit ok"
End With
Set IE = Nothing
End Sub

Sub F()
'same as E except [IE.ReadyState = READYSTATE_COMPLETE]
Dim IE As SHDocVw.InternetExplorer
Set IE = Nothing
Set IE = CreateObject("InternetExplorer.Application")
MsgBox "Set IE ok"
With IE
.Navigate "http://www.yahoo.com"
Do Until Not .Busy And .ReadyState = READYSTATE_COMPLETE
'get the following error message at this point
'Run-time error '-2147467259 (80004005)':
'Automation error
'Unspecified error
DoEvents
Loop
ReturnURLcontent$ = .Document.Body.InnerText
.Quit
MsgBox "IE.Quit ok"
End With
Set IE = Nothing
End Sub

Barb Reinhardt said:
I think I'd try this. I'm on XP, IE7 and Excel 2003.

Sub X()
'Need Microsoft Internet Controls reference selected
Dim myIE As SHDocVw.InternetExplorer
Set myIE = Nothing
Set myIE = CreateObject("InternetExplorer.Application")
myIE.Visible = True
With myIE
.Navigate "http://www.yahoo.com"
Do Until Not .Busy And .ReadyState = READYSTATE_COMPLETE
Debug.Print .Busy
Debug.Print .ReadyState

DoEvents
Loop
ReturnURLcontent$ = .Document.Body.innerText
.Quit
End With
Set myIE = Nothing
End Sub

--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

make selection in listbox html with vba 0
username and password controls 1
UDF Macro Looping Issue Erroring out 1
2003 Macro not working in 2007 1
VBA Email Macro Skipping code 1
Loading Web Page Issue 2
Copy .xls betwen local disk and https:// location 1
Paste method of worksheet class failed 2

Top