Consulting

Results 1 to 2 of 2

Thread: Best Way to Stop Macro until Webpage has Loaded?

  1. #1
    VBAX Newbie
    Joined
    Mar 2016
    Posts
    2
    Location

    Question 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.

    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.

    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.

     '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?

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    "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

    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.
     
    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. ...
    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."
    Last edited by SamT; 03-04-2016 at 03:55 PM.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

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