VBA-IE refresh if not ready

flaviu123

New Member
Joined
Aug 20, 2014
Messages
44
Hi everyone!
Use the following a code to automatically open approx 5,000 links and frequently IE remains busy, thereby blocking the process.
Code:
Sub GetTable()
   
    Dim ieApp As InternetExplorer
    Dim ieDoc As Object
    Dim ieTable As Object
    Dim clip As DataObject
   
    'create a new instance of ie
    Set ieApp = New InternetExplorer
   
    'you don’t need this, but it’s good for debugging
    ieApp.Visible = True
   
    'assume we’re not logged in and just go directly to the login page
    vAddr = ActiveCell.Hyperlinks(1).Address
    ieApp.Navigate vAddr
    Do While ieApp.Busy: DoEvents: Loop
    Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop
'copiaza continutul paginii
    Application.CutCopyMode = False
    ieApp.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DODEFAULT
    ieApp.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT
       
        Sheets("Sheet2").Select
        With ActiveSheet
        LastCol = .Cells(3, .Columns.Count).End(xlToLeft).Column
        End With
        
        h = LastCol + 1
        Cells(1, h).Select
        ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
        DisplayAsIcon:=False
        Sheets("Sheet1").Select
   
    'close 'er up
    ieApp.Quit
    Set ieApp = Nothing
   
End Sub

I'm interested in a code that give refresh after 10 seconds if IE is not ready.


All the best!
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
I believe this is what you are looking for.
I combined both of the Busy/READY_STATE loops into a single loop and inside of that placed a condition to check if the loop has been running for >10 seconds and if so to refresh the page and reset the timer.

Rich (BB code):
Sub GetTable()
   
    Dim ieApp As InternetExplorer
    Dim ieDoc As Object
    Dim ieTable As Object
    Dim clip As DataObject
   
    'create a new instance of ie
    Set ieApp = New InternetExplorer
   
    'you don’t need this, but it’s good for debugging
    ieApp.Visible = True
   
    'assume we’re not logged in and just go directly to the login page
    vAddr = ActiveCell.Hyperlinks(1).Address
    ieApp.Navigate vAddr
    StartTIMER = Timer
    Do While ieApp.Busy And ieApp.ReadyState <> READYSTATE_COMPLETE
        DoEvents
        If StartTIMER + 10 > Timer Then
            ieApp.Refresh
            StartTIMER = Timer  'reset timer for refresh (so it doesn't refresh continuously)
        End If
    Loop
'copiaza continutul paginii
    Application.CutCopyMode = False
    ieApp.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DODEFAULT
    ieApp.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT
       
        Sheets("Sheet2").Select
        With ActiveSheet
        LastCol = .Cells(3, .Columns.Count).End(xlToLeft).Column
        End With
        
        h = LastCol + 1
        Cells(1, h).Select
        ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
        DisplayAsIcon:=False
        Sheets("Sheet1").Select
   
    'close 'er up
    ieApp.Quit
    Set ieApp = Nothing
   
End Sub
 
Upvote 0
Thanks for your help!
I tryed your code and it blocks at ieApp.Refresh and shows me the error: Run-time error '-2147467259 (80004005)'
Automation error
Unspecified error
 
Upvote 0
Is the opened instance of IE still open and responsive?

If so, that is odd, because when I test this on a generic website such as google, it seems to work. (note I am forcing it to not wait the 10 secs)
 
Upvote 0
yes, is an instance of IE open, but must remain open in order to stay logged into the site where the exported data
sorry for english
 
Upvote 0
I am not sure why that wouldn't work.

instead of ieApp.Refresh maybe just navigate to the page again...

ieApp.Navigate vAddr
 
Upvote 0

Forum statistics

Threads
1,218,808
Messages
6,144,605
Members
450,559
Latest member
kwenda farai

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top