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?