0

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
3

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.