I have just finished writing my first MS Access VBA web scrape that feeds from an Access form into a CMS Web Portal form, First & Last Name, DOB, SSN, and then reads back the MBI data to Access. My code works fine when my data matches to the CMS data. However, there are < 5% where my data will not match. When data matches, a table with results appears on a part of the form that was previously empty. When I don't match, an Error message appears at the top of the form where it was previously empty screen.
I have Error handling in my form, (below) that handles this scenario by noting the No Match, and then bailing out to the next record. My issue is that the errors aren't being caught by the error handling code. I get the Access error, and hit Debug, and if I drag the cursor to the error handling code and hit Run, it works properly. The line that throws the error is:
Set ELE = WD.FindElementByXPath("//*[@id='eligibility']/div[2]/table[3]/tbody/tr[3]/td/table/tbody/tr[2]/td[2]")
Error handler:
MyErr:
If Err.Number = 7 Then 'Element Not Found
MyStatus = "Attention: No Match Found"
GoTo MyUpdate
So what other methodology could I use that would check if the "successful match part of the page" was visible with results or not and handle accordingly. I don't even need to read the No Match Error message on the web page, just not having the successful match table visible or not is all I need.
Update 2023-06-22
Below is the complete VBA code that runs from an Access form. I have also included screenshots from the CMS web portal showing both where data is displayed when I match, and where the error is displayed when my data does not match/return a result. I just would like my code to identify when I don't match, and then bail to the next record. One thing I did notice this morning running the code is that the error handling will successfully catch and handle the first no match record the way I intended. After that however, I just get the actual Access Debug End error.
Successful Match No Match Errors
Sub WEB_SCRAPE_MBI()
On Error GoTo MyErr
Dim RS As DAO.Recordset, I As Integer, WD As Object, MyWait As Long
Dim URL As String, FirstName As String, LastName As String, dateOfBirth As String, ssnOrHicn As String
Dim mbiNumber As String, MyStatus As String, Tcnt As Long, Gcnt As Long, Bcnt As Long
Dim ELE As Selenium.WebElement, Keys As Selenium.Keys
' **********************************************************************************
' Create a new Chrome WebDriver
Set WD = CreateObject("Selenium.ChromeDriver")
URL = "https://portal.cms.gov/portal"
' Start the Chrome browser
WD.Start "chrome", "C:\Users\ab81154\AppData\Local\SeleniumBasic\chromedriver.exe"
Set Keys = New Selenium.Keys
' Navigate to a website
WD.Get URL
I = MsgBox("Click Okay once you have finished logging into the Portal and have the MBI Search Form up.", vbOKCancel, MyCap)
If I = 1 Then 'Okay
Else
MsgBox "Process Cancelled....", vbInformation, MyCap
GoTo MyExit
End If
' **********************************************************************************
' Connect to Form
Set RS = Me.F_Sub.Form.RecordsetClone
Tcnt = RS.RecordCount
MyWait = Nz(Me.txtWait, 1000)
Me.txtStatus = ""
Me.txtSuccess = ""
Me.txtFallout = ""
RS.MoveFirst
Do Until RS.EOF
On Error GoTo MyErr
MyStatus = "MACRO SUCCESSFUL"
mbiNumber = ""
FirstName = RS!FName
LastName = RS!LName
ssnOrHicn = RS!SSN_ROLLUP
dateOfBirth = RS!BRTH_DT
RS.Edit
If I < 2 Then
WD.SwitchToFrame (0)
End If
Set ELE = WD.FindElementByXPath("/html/body")
Set ELE = WD.FindElementByClass("eligTable1")
Set ELE = WD.FindElementByClass("eligTable4")
'Set Keys = New Selenium.Keys
' Fill in the form fields
Set ELE = WD.FindElementByName("fName")
ELE.SendKeys (Keys.LeftControl + "a" + Keys.Delete) 'Clear Previous Entry
ELE.SendKeys FirstName
Set ELE = WD.FindElementByName("lName")
ELE.SendKeys (Keys.LeftControl + "a" + Keys.Delete) 'Clear Previous Entry
ELE.SendKeys LastName
Set ELE = WD.FindElementByName("dob")
ELE.SendKeys (Keys.LeftControl + "a" + Keys.Delete) 'Clear Previous Entry
ELE.SendKeys dateOfBirth
Set ELE = WD.FindElementByName("ssn")
ELE.SendKeys (Keys.LeftControl + "a" + Keys.Delete) 'Clear Previous Entry
ELE.SendKeys ssnOrHicn
' Click the "Find" button
WD.FindElementByName("submitBtn").Click
Sleep MyWait
On Error GoTo MyErr
'Set ELE = WD.FindElementByXPath("/html/body/form/div[2]/div/font/h2") 'Line where Not Found is displayed
Set ELE = WD.FindElementByXPath("//*[@id='eligibility']/div[2]/table[3]/tbody/tr[3]/td/table/tbody/tr[2]/td[2]")
mbiNumber = Trim(ELE.Text)
MyUpdate:
RS!MBI = mbiNumber
RS!M_Status = MyStatus
RS!M_DATE = Now()
If RS!MBI = "" Then
Bcnt = Bcnt + 1
Else
Gcnt = Gcnt + 1
End If
RS.Update
I = I + 1
Me.txtStatus = I & " of " & Format(Tcnt, "#,0") & " Completed....."
Me.txtFallout = Bcnt
Me.txtSuccess = Gcnt
If I Mod 3 = 0 Then
Me.txtStatus.Requery
Me.txtSuccess.Requery
Me.txtFallout.Requery
Me.F_Sub.Form.Repaint
End If
RS.MoveNext
Loop
MyExit:
RS.Close
Set RS = Nothing
WD.Quit
DoCmd.Hourglass False
DoCmd.SetWarnings True
MsgBox "MBI Scrape Completed!", vbInformation, MyCap
Exit Sub
MyErr:
If Err.Number = 7 Then 'Element Not Found
MyStatus = "Attention: No Match Found"
GoTo MyUpdate
ElseIf Err.Number = 2501 Then 'User clicked cancel
MsgBox "Process Cancelled !", vbInformation
Resume MyExit
Else
MsgBox "Error# " & Err.Number & vbCrLf & Err.Description, vbInformation, MyCaption & " - LoadUserInfo"
Resume MyExit
End If
End Sub