Best Way to Stop Macro until Webpage has Loaded?

Hello,

I'm creating a macro in Access that will launch a new instance of Internet Explorer, login into a website with the company's credentials, navigate the website to download a file and then import that file into Access.

A macro I created in Access (the first time I used VBA!) already does this and seems to do it without too much of an issue; however, I feel like the code was written poorly and is unstable, and in creating this new macro for a different file I want to make the code less sloppy to read and understand :)

Here is the complete macro I wrote in Access. Pardon me if it looks terrible…it was, after all, the first time I used VBA.

Code:

---

Sub InternetLogin()


    'Creating variables
    Dim IE As Object
    Dim userName As Object
    Dim password As Object
    Dim submitLoginButton As Object
    Dim domFromDate As Object
    Dim domToDate As Object
    Dim domStoreNumber As Object
    Dim submitInventoryButton As Object
    Dim exportToExcel As Object
    Dim webPage As Object
    Dim tagNames As Integer
    Dim userToDate As Variant
    Dim userFromDate As Variant
    Dim userStoreNumber As Variant
   
    userFromDate = InputBox("Please enter the job From Date (dd/mm/yyyy)")
    userToDate = InputBox("Please enter the job To Date (dd/mm/yyyy)")
    userStoreNumber = InputBox("Please enter the store number")
               
    'Creating instance of IE
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = True
   
    'URL to open
    IE.Navigate "website"
     
    'Searching for DOM elements on login page
    Do While IE.ReadyState <> 4: DoEvents: Loop
        Set webPage = CreateObject("htmlfile")
        Set webPage = IE.Document
       
        Set userName = webPage.getElementByID("txtUserName")
        userName.Value = ""
       
        Set password = webPage.getElementByID("UserPw")
        password.Value = ""
       
        Set submitLoginButton = webPage.getElementByID("submit1")
        submitLoginButton.Click
       
    'Wait until web page finishes loading
    Pause (5)
   
    'Go to Inventory Status page
    IE.Navigate "website"
   
    'Searching for DOM elements on inventory status page
    Do While IE.ReadyState <> 4: DoEvents: Loop
        Set webPage = CreateObject("htmlfile")
        Set webPage = IE.Document
       
        Set domFromDate = webPage.getElementByID("txtFromDate")
        domFromDate.Value = userFromDate
       
        Set domToDate = webPage.getElementByID("txtToDate")
        domToDate.Value = userToDate
       
        Set domStoreNumber = webPage.getElementByID("txtStoreNo")
        domStoreNumber.Value = userStoreNumber
       
        Set submitInventoryButton = webPage.getElementByID("submit1")
        submitInventoryButton.Click
       
    'Wait until web page finishes loading
    Pause (10)
 
   
    'Searching for DOM elements on results page
    Do While IE.ReadyState <> 4: DoEvents: Loop
        Set webPage = CreateObject("htmlfile")
        Set webPage = IE.Document
       
        'Force a pause to ensure all DOM elements have loaded
        If Not IE.Busy Then
            Set exportToExcel = webPage.getElementByID("btnExportToExcel")
            exportToExcel.Click
        End If
       
    'Wait until web page download finishes
    Pause (8)
   
    Call GetFileAndSaveToTable
   
End Sub


Sub GetFileAndSaveToTable()


    'Creating variables
    Dim fileSysObj As FileSystemObject
    Dim File As Object
    Dim folder
    Dim filePath As String
    Dim fileDate As Date
    Dim uniqueID As String
       
    'File directory (dir)
    Const myDir As String = "C:\Users\Downloads"
   
    Set fileSysObj = New FileSystemObject
    Set folder = fileSysObj.GetFolder(myDir)
    uniqueID = Format(Now, "mmddyyhhnnss")
   
    'Looping through all files to find the newest Excel workbook
    fileDate = DateSerial(1900, 1, 1)
    For Each File In folder.Files
        If InStr(1, File.Name, ".xls") > 0 Then
            If File.DateLastModified > fileDate Then
                fileDate = File.DateLastModified
                'File has to be changed from .xls to .html because it is not formatted as an .xls file
                File.Name = Replace(File.Name, ".xls", uniqueID & ".html")
                filePath = File.Path
            End If
        End If
    Next File
       
    'Ending the file scripting
    Set fileSysObj = Nothing
    Set folder = Nothing
   
    'Import the html file into Access table
    DoCmd.TransferText acImportHTML, , "InventoryStatus", filePath, True
   
    Call CleanTable
               
End Sub


Sub CleanTable()


    'Creating variables
    Dim dbAccess As Object
    Dim rsInventoryStatus As Object
    Dim tbEnumerator As Object
    Dim tbColumns As Object
    Dim stringFound As Integer
    Dim stringToReplace As String
   
    'Establishing connection to the database
    Set dbAccess = CurrentDb
    Set rsInventoryStatus = dbAccess.OpenRecordset("InventoryStatus")
    Set tbColumns = rsInventoryStatus.Fields
   
    'Looping throught the records
    While Not rsInventoryStatus.EOF
        For Each tbEnumerator In rsInventoryStatus.Fields
            If tbEnumerator.Name = "Job#" Then
                stringFound = InStr(1, tbEnumerator.Value, "#")
                If stringFound > 0 Then
                    rsInventoryStatus.Edit
                    stringToReplace = Left(tbEnumerator.Value, InStr(1, tbEnumerator.Value, "#") - 1)
                    rsInventoryStatus("Job#").Value = stringToReplace
                    rsInventoryStatus.Update
                End If
            End If
        Next
        rsInventoryStatus.MoveNext
    Wend
           
End Sub

---

What I want is a stable method of pausing the macro until IE has finished loading all the elements on a webpage. Online I found it is common to use a Do While with the condition set to IE.ReadyState <> 4.

Code:

---

Do While IE.ReadyState <> 4: DoEvents: Loop
// Code to run once browser has finished loading here

---

I find it odd that the condition would be set to IE.ReadyState <> 4 – shouldn’t IE.ReadyState be equal to 4 once the webpage has loaded?

In the macro I wrote I used a combination of the above Do While and a Pause() function. Nevertheless I occasionally encountered and error in one section of the code because it seemed the browser was loading the elements slower than the macro was running the code. To stop this from happening I wrote an if statement that checked if IE was not busy.

Code:

---

'Searching for DOM elements on results page    Do While IE.ReadyState <> 4: DoEvents: Loop
        Set webPage = CreateObject("htmlfile")
        Set webPage = IE.Document
       
        'Force a pause to ensure all DOM elements have loaded
        If Not IE.Busy Then
            Set exportToExcel = webPage.getElementByID("btnExportToExcel")
            exportToExcel.Click
        End If

---

What is the best way to stop the macro until IE has loaded the webpage? Using a Do While IE.ReadyState <> 4, an If Not IE.Busy, or some other way?

"Do while <> 4," is just VBA's way of saying "do until = 4."

Colloquially, "<>" reads as "Not", IOW, Do while not 4.

I am not familiar with the IE Object Model, but reading your code you should be able to use

Code:

---

Do While IE.Busy: Do Events: Loop

---

This is dangerous because the code will step over the IF when IE is busy and continue running after the End If.

Code:

---


If Not IE.Busy Then
    Set exportToExcel = webPage.getElementByID("btnExportToExcel")
    exportToExcel.Click
End If

---

While there may be subtleties where one is preferable over the other, Using a Do Events loop with either ReadyState <> 4 or IE.Busy is good method.

The one unsubstantiated doubt I have about using a Do Events loop over a basic loop is that Do Events allows the User to interact with the OS and any open applications. IF this is definitely not desired. ...

Code:

---

Do While IE.Busy: 1 = 1: Loop

---

Note that there are 99 ways to do anything in VBA, but only 42 of them are the "Best."