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!
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

BiocideJ

Well-known Member
Joined
Jan 23, 2012
Messages
1,733
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
 

flaviu123

New Member
Joined
Aug 20, 2014
Messages
44
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
 

BiocideJ

Well-known Member
Joined
Jan 23, 2012
Messages
1,733
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)
 

flaviu123

New Member
Joined
Aug 20, 2014
Messages
44

ADVERTISEMENT

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
 

BiocideJ

Well-known Member
Joined
Jan 23, 2012
Messages
1,733
I am not sure why that wouldn't work.

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

ieApp.Navigate vAddr
 

flaviu123

New Member
Joined
Aug 20, 2014
Messages
44
Yes, it's a good idea. I will try with next 5000 links to the next project. Thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,638
Messages
5,549,110
Members
410,897
Latest member
Ekrupa25
Top