+ Reply to Thread
Results 1 to 10 of 10

Why does this "wait for page to load" fail?

Hybrid View

  1. #1
    Registered User
    Join Date
    05-13-2011
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    19

    Why does this "wait for page to load" fail?

    If I run this in the editor sometimes it works and sometimes it does not.
    If I run this in editor with F8, stepping through the code, it does work. I can only guess that enough time is passing during the F8 presses that the page can load.
    But is that not the purpose of adding the wait code?

    Private Const READYSTATE_COMPLETE = 4
    Private Const TimeOutSeconds As Double = 15
    Sub IeP()
    
    Dim objIE As Object
    Dim TimeOutTime As Date
    
    'create the IE object
    Set objIE = CreateObject("InternetExplorer.Application")
    objIE.Top = 0
    objIE.Left = 0
    objIE.Width = 1000
    objIE.Height = 800
    objIE.Visible = True
    'objIE.AddressBar = 0
    'objIE.StatusBar = 0
    'objIE.Toolbar = 0
    
    'navigate to the site
    objIE.Navigate ("http://thewebsite.com")
    
       'add 15 seconds to the current time and assign
       'this value to the variable "TimeOutTime"
        TimeOutTime = DateAdd("s", TimeOutSeconds, Now)
        
    Do Until objIE.ReadyState = READYSTATE_COMPLETE
    DoEvents
     If Now > TimeOutTime Then
     'timed out
     MsgBox "Timed out..."
     End If
    Loop
    
    'I WAS USING BELOW BUT SWITCHED TO ABOVE
    'Do
    'DoEvents
    '    If Err.Number <> 0 Then
    '        objIE.Quit
    '        Set objIE = Nothing
    '        GoTo the_start
    '    End If
    'Loop Until objIE.ReadyState = 4
    
            Set the_input_elements = objIE.Document.getElementsByTagName("input")
            For Each input_element In the_input_elements
                If input_element.getAttribute("name") = "username" Then
                    input_element.Value = "PRIVATE"
                    'Exit For
                End If
                If input_element.getAttribute("name") = "password" Then
                    input_element.Value = "NNNNNNN"
                    Exit For
                End If
            Next input_element
            
            Set the_input_elements = objIE.Document.getElementsByTagName("input")
            For Each input_element In the_input_elements
            If input_element.getAttribute("value") = "Login" Then
                input_element.Click
                Exit For
            End If
            Next input_element
       
       'add 15 seconds
       TimeOutTime = DateAdd("s", TimeOutSeconds, Now)
       
    Do Until objIE.ReadyState = READYSTATE_COMPLETE
    DoEvents
     If Now > TimeOutTime Then
     'timed out
     MsgBox "Timed out..."
     End If
    Loop
    
    
            Set the_input_elements = objIE.Document.getElementsByTagName("input")
            For Each input_element In the_input_elements
            If input_element.getAttribute("value") = "Launch Page" Then
                input_element.Click
                Exit For
            End If
            Next input_element
        
        'add 15 seconds
        TimeOutTime = DateAdd("s", TimeOutSeconds, Now)
    
    Do Until objIE.ReadyState = READYSTATE_COMPLETE
    DoEvents
     If Now > TimeOutTime Then
     'timed out
     MsgBox "Timed out..."
     End If
    Loop
    
        'add 15 seconds
        TimeOutTime = DateAdd("s", TimeOutSeconds, Now)
    
    Do Until objIE.ReadyState = READYSTATE_COMPLETE
    DoEvents
     If Now > TimeOutTime Then
     'timed out
     MsgBox "Timed out..."
     End If
    Loop
    
            'open the next page
            Set the_input_elements = objIE.Document.getElementsByTagName("input")
            For Each input_element In the_input_elements
            If input_element.getAttribute("value") = "thenextpage" Then
                input_element.Click
                Exit For
            End If
            Next input_element
        
        'add 15 seconds
        TimeOutTime = DateAdd("s", TimeOutSeconds, Now)
    
    Do Until objIE.ReadyState = READYSTATE_COMPLETE
    DoEvents
     If Now > TimeOutTime Then
     'timed out
     MsgBox "Timed out..."
     End If
    Loop
    
    'objIE.Quit
    'Set objIE = Nothing
     End Sub

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,330

    Re: Why does this "wait for page to load" fail?

    Hi hgtenn,

    When I have these timing problems I add some "DoEvents" commands to my code. They tell the OS to go do other things (like wait for the whole file to open) before trying to execute more code. Give a few of them a try when you think the code should be waiting instead of Wait commands.

    http://support.microsoft.com/kb/118468
    http://msdn.microsoft.com/en-us/libr.../gg264522.aspx
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    05-13-2011
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Why does this "wait for page to load" fail?

    MarvinP, thanks for the reply.

    A bit more guidance please. I have "DoEvents" in the code already.? Are you suggesting to add more, or move around the existing DoEvents?
    Can you show where in one example?

    Also there is java in the pages. I just saw something elsewhere that mentions there may be additional items happening after the page loads...if that makes any sense.

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,330

    Re: Why does this "wait for page to load" fail?

    I'd take the DoEvents out of the loops.

    I'd add one (or two) after you go to your website.
    Id add one after you switch sheets or do something that is "outside" of Excel.

    I was hoping to not have to read your code but simply have you try...

  5. #5
    Registered User
    Join Date
    05-13-2011
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Why does this "wait for page to load" fail?

    Sorry :-) I have tried two different methods for waits.

    I tried...seems to make no difference, gets to the end of the code very quickly.
    Took them out of the loops and placed them between each block.

    Private Const READYSTATE_COMPLETE = 4
    Private Const TimeOutSeconds As Double = 30
    Sub IeK()
    
    Dim objIE As Object
    Dim TimeOutTime As Date
    
    Set objIE = CreateObject("InternetExplorer.Application")
    objIE.Top = 0
    objIE.Left = 0
    objIE.Width = 1000
    objIE.Height = 800
    objIE.Visible = True
    'objIE.AddressBar = 0
    'objIE.StatusBar = 0
    'objIE.Toolbar = 0
    
    objIE.Navigate ("http://thewebsite.com")
    
    DoEvents
    DoEvents
    
       'add 15 seconds to the current time and assign
       'this value to the variable "TimeOutTime"
        TimeOutTime = DateAdd("s", TimeOutSeconds, Now)
        
        DoEvents
        
    Do Until objIE.ReadyState = READYSTATE_COMPLETE
     If Now > TimeOutTime Then
     'timed out
     MsgBox "Timed out..."
     End If
    Loop
        
    
        DoEvents
            Set the_input_elements = objIE.Document.getElementsByTagName("input")
            For Each input_element In the_input_elements
                If input_element.getAttribute("name") = "username" Then
                    input_element.Value = "PRIVATE"
                    'Exit For
                End If
                If input_element.getAttribute("name") = "password" Then
                    input_element.Value = "NNNNNN"
                    Exit For
                End If
            Next input_element
    
        DoEvents
            Set the_input_elements = objIE.Document.getElementsByTagName("input")
            For Each input_element In the_input_elements
            If input_element.getAttribute("value") = "Login" Then
                input_element.Click
                Exit For
            End If
            Next input_element
            
        DoEvents
            
       TimeOutTime = DateAdd("s", TimeOutSeconds, Now)
       
        DoEvents
       
    Do Until objIE.ReadyState = READYSTATE_COMPLETE
     If Now > TimeOutTime Then
     'timed out
     MsgBox "Timed out..."
     End If
    Loop
            
    'GETS UP TO HERE...LOADS THE PAGE BUT DOES NOT CLICK IT
    
        DoEvents
            Set the_input_elements = objIE.Document.getElementsByTagName("input")
            For Each input_element In the_input_elements
            If input_element.getAttribute("value") = "Launch Page" Then
                input_element.Click
                Exit For
            End If
            Next input_element
            
            DoEvents
    
        TimeOutTime = DateAdd("s", TimeOutSeconds, Now)
        
        DoEvents
    
    Do Until objIE.ReadyState = READYSTATE_COMPLETE
     If Now > TimeOutTime Then
     'timed out
     MsgBox "Timed out..."
     End If
    Loop
    
    DoEvents
    
    
    'objIE.Quit
    'Set objIE = Nothing
    
    End Sub

  6. #6
    Registered User
    Join Date
    05-13-2011
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Why does this "wait for page to load" fail?

    Hello, can anyone help with this please?

    (I put this together following a tutorial on youtube(web scraper vba), I am not that skilled in vba, but trying!)

    I added the DoEvents (post #5). It seems to make no difference. If I step through the code it's getting enough time to complete and work as expected.
    If I simply run the code in the editor, even after littering it with DoEvents, the code still flies through to the end.

    Is there a better method to adding a wait? Is there is way to make sure the waits are triggered?

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Why does this "wait for page to load" fail?

    Have you tried adding a loop to wait for the document to be fully loaded?
    If posting code please use code tags, see here.

  8. #8
    Registered User
    Join Date
    05-13-2011
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Why does this "wait for page to load" fail?

    If you look at the code, then you see the below.
    Isn't that the loop to wait for the page to load?

    Private Const READYSTATE_COMPLETE = 4
    
    Do Until objIE.ReadyState = READYSTATE_COMPLETE
     If Now > TimeOutTime Then
     'timed out
     MsgBox "Timed out..."
     End If
    Loop

  9. #9
    Registered User
    Join Date
    05-13-2011
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Why does this "wait for page to load" fail?

    Hello,

    Still looking for resolve on this issue. There are ReadyState loops in the code. So I do not know what else to add or where to add it.

  10. #10
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Why does this "wait for page to load" fail?

    That code isn't waiting for the document to load, it's waiting for IE to reach a complete ready state.

    The code to wait for the document to load would look something like this.
    Do Until objIE.document.readyState = "complete"
        DoEvents
    Loop

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to make a cell display "FAIL" when range of cells is FAIL
    By crazychile in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-18-2017, 04:13 PM
  2. [SOLVED] Trying to get a loop to wait for a web page to load.
    By Habanero Time in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-09-2013, 01:46 AM
  3. [SOLVED] Formula needed to display "Pass" or "Fail" if a column contains any values other than "yes
    By andreindy in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-26-2013, 05:49 PM
  4. Wait for Page to Load When Scraping Data
    By bairdgbaird in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-05-2012, 01:35 PM
  5. Macro won't wait "Refresh all Pivot Tables" command to conclude before calculating
    By malalol in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-23-2010, 04:31 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1