Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Run time error when accessing Internet using VBA
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4
Default Run time error when accessing Internet using VBA
Hi,

Please refer to the following code. This code works fine on my office machine,
but does not work on my laptop and home machine. All three machines have
Excel 2003, SP3, and MS XP.

When I run the code on my laptop, the macro stops at the line : BUSY and the
following error message is displayed.

Runtime error -2147023170, Automation error, The remote procedure call failed.


My guess is that the error has something to do with the way I have defined
the IE object.
I tried changing the Dim IE As New SHDocVw.InternetExplorer declaration to
Dim IE as Object and Dim IE as InternetExplorer, but the error did not go
away.


Code:
  
 Dim IE As New SHDocVw.InternetExplorer   '***
 Dim URL As String
 Dim Ext As String
 Dim ocell As Range
 Dim file_loc As String
 Dim ResultDIV As HTMLDivElement, AllResultsDIV As HTMLDivElement,
OneResultDIV As HTMLDivElement
 Dim message As String
  Dim htmlDoc As MSHTML.HTMLDocument
 Dim htmlInput As MSHTML.HTMLInputElement
 Dim htmlColl As MSHTML.IHTMLElementCollection
 Dim counter As Integer
 Dim DLd As Boolean
 
 file_loc = "C:\newfolder\"
 
    Set IE = CreateObject("InternetExplorer.Application")
    With IE
    .Visible = 1
    .Navigate "http://www.fxstreet.com/forum/showthread.php?t=2910"
      
        
    Do While .Busy: DoEvents: Loop   ' runtime error occurs here or on the
next line
    Do While .readyState < 4: DoEvents: Loop

   ...
Thanks,

MG

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200907/1

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Run time error when accessing Internet using VBA
You need to wiat for both readystate and busy. It is probably runs at
different speed on different machines and will very depending on the version
of Internet exlporer on you r machines.


from

Do While .Busy: DoEvents: Loop ' runtime error occurs here or on the
next line
Do While .readyState < 4: DoEvents: Loop



to

Do While .readyState < 4 Or .busy = True
DoEvents
Loop

Do While .Busy: DoEvents: Loop ' runtime error occurs here or on the
next line
Do While .readyState < 4: DoEvents: Loop



"musicgold" wrote:

Hi,

Please refer to the following code. This code works fine on my office machine,
but does not work on my laptop and home machine. All three machines have
Excel 2003, SP3, and MS XP.

When I run the code on my laptop, the macro stops at the line : BUSY and the
following error message is displayed.

Runtime error -2147023170, Automation error, The remote procedure call failed.


My guess is that the error has something to do with the way I have defined
the IE object.
I tried changing the Dim IE As New SHDocVw.InternetExplorer declaration to
Dim IE as Object and Dim IE as InternetExplorer, but the error did not go
away.


Code:
   
  Dim IE As New SHDocVw.InternetExplorer   '***
  Dim URL As String
  Dim Ext As String
  Dim ocell As Range
  Dim file_loc As String
  Dim ResultDIV As HTMLDivElement, AllResultsDIV As HTMLDivElement,
 OneResultDIV As HTMLDivElement
  Dim message As String
   Dim htmlDoc As MSHTML.HTMLDocument
  Dim htmlInput As MSHTML.HTMLInputElement
  Dim htmlColl As MSHTML.IHTMLElementCollection
  Dim counter As Integer
  Dim DLd As Boolean
  
  file_loc = "C:\newfolder\"
  
     Set IE = CreateObject("InternetExplorer.Application")
     With IE
     .Visible = 1
     .Navigate "http://www.fxstreet.com/forum/showthread.php?t=2910"
       
         
     Do While .Busy: DoEvents: Loop   ' runtime error occurs here or on the
 next line
     Do While .readyState < 4: DoEvents: Loop
 
    ...

Thanks,

MG

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200907/1


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 7
Default Run time error when accessing Internet using VBA
Joel,

Thanks. I used the following code in my macro but still got the same error.
Now the error occurs at the combined condition statement.

Code:
  Do While .readyState < 4 Or .Busy = True: DoEvents: Loop

  Do While .Busy: DoEvents: Loop
  Do While .readyState < 4: DoEvents: Loop
--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200907/1

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Run time error when accessing Internet using VBA
try this. I don't know why you have the colon in the statement. It is not
needed.

Dim IE As New SHDocVw.InternetExplorer '***
Dim URL As String
Dim Ext As String
Dim ocell As Range
Dim file_loc As String
Dim ResultDIV As HTMLDivElement, AllResultsDIV As HTMLDivElement,
OneResultDIV As HTMLDivElement
Dim message As String
Dim htmlDoc As MSHTML.HTMLDocument
Dim htmlInput As MSHTML.HTMLInputElement
Dim htmlColl As MSHTML.IHTMLElementCollection
Dim counter As Integer
Dim DLd As Boolean

file_loc = "C:\newfolder\"

Set IE = CreateObject("InternetExplorer.Application")
With IE
.Visible = 1
.Navigate "http://www.fxstreet.com/forum/showthread.php?t=2910"


Do While .readyState < 4 Or .busy = True
DoEvents
Loop



"musicgold via OfficeKB.com" wrote:

Joel,

Thanks. I used the following code in my macro but still got the same error.
Now the error occurs at the combined condition statement.

Code:
 
   Do While .readyState < 4 Or .Busy = True: DoEvents: Loop
 
   Do While .Busy: DoEvents: Loop
   Do While .readyState < 4: DoEvents: Loop

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200907/1


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 7
Default Run time error when accessing Internet using VBA
I have been able to resolve the problem at least for one of my machines.

In the machine with IE7, when I disabled the protected mode of IE7, the code
started working.
(Tools Internet Options security tab tick button - "Enable protected
mode")

I am still trying to solve the problem for the machine with IE6.

MG.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200908/1

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Anyway of accessing the internet from within excel 2007? Ron Excel Discussion (Misc queries) 2 February 26th 10 01:50 PM
VBA - Internet Time Service Carl Hartness[_2_] Excel Programming 3 May 12th 07 07:09 PM
Run-Time Error 91 for Internet Explorer .elements(name).value [email protected] Excel Programming 3 October 24th 06 05:46 PM
Why Excel is accessing Internet & Slowing Excel Performance Jeff Excel Discussion (Misc queries) 1 May 27th 05 10:29 PM
Office Apps accessing the Internet BigBirtha Excel Discussion (Misc queries) 1 December 1st 04 07:07 PM


All times are GMT +1. The time now is 07:14 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement