Verifying status of a long list of patients in a spreadsheet. Can't figure out how to mark a cell after verifying text exists on each page.

JordanStach

New Member
Joined
Sep 19, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
The code cycles through a huge list of patients and plugs their IDnumber and DoB into a portion of a URL, then opens a webpage to check that they're marked as eligible for some service. That all works fine, but I'm having trouble automating the marking of cells to indicate whether a patient is eligible.

I'm very new (day 4) to this, and I don't have any background in coding, so I'm sort of stuck at sea at this point. Not sure which direction to start moving...

This is a snapshot of the HTML I'm trying to target:
And this is the code (removed the actual URL for privacy):

VBA Code:
Sub SomeModule()


    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To FinalRow

        Dim IE As New InternetExplorer
        IE.Visible = True
        IE.Silent = True
        
        IE.navigate "https://website.com/" _
            & Range("A" & i).Value _
            & "&dob=" _
            & Range("C" & i).Value
            
        Do
        DoEvents
        Loop Until IE.readyState = READYSTATE_COMPLETE
        
        Dim Doc As HTMLDocument
        Set Doc = IE.document
        
        If InStr(IE.document.getElementsByClassName("alert alert-success big").innerHTML, "patient is eligible") > 0 Then
        
            Range("F" & i) = "Elligible"
        
        End If
        IE.Quit: Set IE = Nothing

    Next i



End Sub

I get an error on the 'If InStr' line. "Runtime Error 438: Object doesn't support this property or method."

Any advice is appreciated.
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

JordanStach

New Member
Joined
Sep 19, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
VBA Code:
    Sub SomeModule()
    
    
        FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
        For i = 2 To FinalRow
    
            Dim IE As New InternetExplorer
            IE.Visible = True
            IE.Silent = True
            Dim codeLine As String
            Dim startPos As Long
            
            IE.navigate "https://website.com/" _
                & Range("A" & i).Value _
                & "&dob=" _
                & Range("C" & i).Value
                
            Do
            DoEvents
            Loop Until IE.readyState = READYSTATE_COMPLETE
            
            Dim Doc As HTMLDocument
            Set Doc = IE.document
            
            Dim HTML As HTMLDocument
            
            
            mes = IE.document.body.innerHTML
    
            startPos = InStr(mes, "patient is eligible")
            If startPos = 0 Then
                ActiveSheet.Range("F" & i).Value = "Not eligible."
            Else
                codeLine = Mid(mes, startPos, InStr(startPos, mes, ";") - startPos)
                ActiveSheet.Range("F" & i).Value = "Patient is eligible."
            End If
    
    
            IE.Quit: Set IE = Nothing
        Next i
    
    
    
    End Sub

Solved. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,670
Messages
5,597,482
Members
414,144
Latest member
UDFNewbie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top