.ReadyState & .Busy Is Not Waiting For IE Page To Load

Question

I have some VBA to launch a company intranet site which will bring me directly to the document I am searching for. I need to wait for the page to finish loading, and then hit the "Print" button which will open the document in a Adobe Reader supported IE tab, and from there I save it as a PDF to a drive.

My issue is that the loop I have to wait until the webpage is loaded does not properly wait. Doing some research on SO, I've seen this is a known issue with newer versions of IE. I have since tried playing with some XMLHTTP methods, but I am unfamiliar with those, and my attempts with it have also fallen short (not sure how I would navigate to the next page by hitting the Print link using XMLHTTP).

My current VBA is as follows, and ieApp is New InternetExplorerMedium.

    Set objShell = CreateObject("Shell.Application")
    IE_Count = 0
    IE_Count = objShell.Windows.Count
    For x = 0 To (IE_Count - 1)
        On Error Resume Next
        my_url = ""
        my_title = ""
        my_url = objShell.Windows(x).Document.Location
        my_title = objShell.Windows(x).Document.Title

        If my_url Like "http://ctdayppv002/Home/DocViewer?" & "*" Then
            Set ie = objShell.Windows(x)
            Do While ieApp.ReadyState <> 4 And ie.Busy
                DoEvents
            Loop
            For Each ee In ie.Document.getElementsByTagName("a")
                If ee.ID = "printDocLink" Then
                 ee.Click: DoEvents: Sleep 1500
                    Do While ie.ReadyState <> 4 And ie.Busy
                        DoEvents
                    Loop
                 Exit For
                End If
            Next ee
            Exit For
        Else
        End If
    Next

If I add a bunch of Sleep time, then it will wait, until a document comes up that exceeds the time I told it to Sleep, so obviously that isn't a reliable solution.

Using the following questions for reference, I have tried to use XMLHTTP, but also noticed comments that this method may not work with JavaScript sites.

VBA hanging on ie.busy and readystate check

web scraping with vba using XMLHTTP

One of my attempts with XMLHTTP:

Public ieApp As MSXML2.XMLHTTP60
Set ieApp = New MSXML2.XMLHTTP60

    With ieApp

        .Open "GET", urlString, False
        .send

        While ieApp.ReadyState <> 4
            DoEvents
        Wend

        Dim HTMLDoc As MSHTML.HTMLDocument
        Dim HTMLBody As MSHTML.HTMLBody

        Set HTMLDoc = New MSHTML.HTMLDocument
        Set HTMLBody = HTMLDoc.body
        HTMLBody.innerHTML = ieApp.responseText
        Debug.Print HTMLBody.innerHTML

    End With

Within the resulting HTMLBody.innerHTML I do not see the "printDocLink" element.

FYI - I have been emailing a rep from the company that created the website database, and they do not believe there is an API call that can directly export as a PDF, which I was hoping would be available to skip over the "Print" button entirely.

What exactly do you see when it fails? Instead of having a single Sleep, you could use a shorter one in a loop which checks (with error handling if necessary) for the required result. — Tim Williams, Jun 06 '19 at 17:58
@TimWilliams are you referring to the Internet Explorer approach, or the XMLHTTP? When using IE, I don't necessarily "see" anything, it just moves on to the next part of the code, which obviously doesn't work because it didn't get a chance to hit the Print button. Of course, adding a long Sleep gives it time to load and hit print, which then the rest of the VBA code works fine. Using XMLHTTP it seems to do the same thing, but I told it to print the innerHTML which returned the entire JavaScript for the page. — TBoulz, Jun 06 '19 at 18:21
@TimWilliams forgive me for sounding stupid, but arent I checking for the desired result when I use the `If ee.ID = "printDocLink" Then`? And the attempt at not advancing until that element exists would be above at the `Do While` loop? — TBoulz, Jun 06 '19 at 18:24
@TimWilliams okay I think I figured out what you were saying. I did this: `Do Until .Document.getElementById("printDocLink") <> 0 DoEvents: Sleep 1000 Loop` and it seems to be working. I'll have to try it out with some larger documents that take longer to load. — TBoulz, Jun 06 '19 at 19:19

score 1 · Accepted Answer · answered Jun 07 '19 at 19:22

Following the advice from Tim Williams and QHarr, I found a solution that works for me.

I added a Do Until, and also a timer for 6 seconds:

            t = Now + TimeValue("0:00:6")
            Do Until .Document.getElementById("printDocLink") <> 0
                DoEvents: Sleep 1000
                If Now > t Then
                    Call Not_Found_PPV(N, searchitem)
                    .Quit
                    Set ieApp = Nothing
                    GoTo NxtInv
                End If
            Loop

.ReadyState & .Busy Is Not Waiting For IE Page To Load

1 Answers1