VBA to click link in IE table

[sthack99]

Hi all. I am building a macro that will go to a particular county court website (depending on criteria in the worksheet), gather information about the subject case, and return the results to the worksheet. The worksheet will have the following columns (among others): State, County, Case Number, Status, Docket. The information is gathered from State, County, and Case Number, run through a function to determine which website to visit, then I ultimately want the code to search that case number and return the case status (Active, Pending, Dismissed, etc.) in the Status column, and a link to the docket in the Docket column.

The website links are on a different sheet (called "Websites").

The only way I can think to do this is to program the automation for each site individually, which I have no problem doing. However, of course, the 1st website I'm working with, I'm having issues. I'm able to get it to head to the website, enter in the case number, and hit submit, but then for some reason it won't click the case number link that appears in the table results.

Until I get past this part, I have turned off the loop in my code. Here's the code I have so far. The website I'm currently working with is Case Search. The information for the first case (found in row 2) is State = FL, County = Broward, and Case Number = CACE13021641.

Please take a look and tell me what you think I'm doing wrong.

Public Sub GetCaseStatus()


Dim IE As Object
Dim URL As String
Dim RowNum As Integer
Dim RowCount As Integer
Dim State As String
Dim County As String
Dim CountyList As Range
Dim CaseNum As String
Dim link As Object
Dim doc As Object


    Set CountyList = Worksheets("Websites").Range("C2:E150")
    Set IE = CreateObject("InternetExplorer.Application")
       

    RowNum = 2
    RowCount = ActiveSheet.UsedRange.Rows.Count

        

'    Do While RowNum <= RowCount
        State = ActiveSheet.Range("D" & RowNum).Value
        County = ActiveSheet.Range("E" & RowNum).Value
        CaseNum = ActiveSheet.Range("G" & RowNum).Value

        URL = CourtWebsite(State, County, CountyList)
        
        IE.navigate URL
        IE.Visible = True
        Do
        DoEvents
        Loop Until IE.readyState = 4
        
                               
        IE.document.GetElementById("ctl00_ContentPlaceHolder1_txtCaseNumber").Value = CaseNum
        IE.document.GetElementById("ctl00_ContentPlaceHolder1_sbmPublicCase").Click
     
               
        Do
        DoEvents
        Loop Until IE.readyState = 4
        
        Set doc = IE.document
        
                
        
        For Each link In doc.getElementsByTagName("table")
        
            If link.document.Title = CaseNum Then
                link.document.Links(0).Click
            End If
            
        Next link
                   
                        
       RowNum = RowNum + 1
        
              
'    Loop
    
End Sub

Here's the function to determine the correct website, if you're interested:

Public Function CourtWebsite(case_state As String, case_county As String, list As Range)

CourtWebsite = Application.WorksheetFunction.VLookup(case_state & "-" & case_county, list, 3, False)

End Function

 

[John_w]

Instead of the For Next loop, try:

doc.getElementsByTagName("TABLE")(1).Rows(2).Cells(0).Children(0).Click

 

[sthack99]

Instead of the For Next loop, try:

doc.getElementsByTagName("TABLE")(1).Rows(2).Cells(0).Children(0).Click

So I tried it, and nothing happened.... I'm using IE 11. Not sure if that matters or not.

 

[clampron]

If you know the case #, it may be easier to navigate to the website by building the URL first, rather than writing code to type in data and push buttons. For example: CaseNumber=CACE13021641 is part of the URL string. Sample code:

Sub pullCaseData()
    Dim IE As Object
    Dim caseNum As String
    Dim stateReportNum As String
    Dim caseStatus As String
    
    Set IE = CreateObject("InternetExplorer.application")
    caseNum = "CACE13021641"
    IE.Visible = False
    IE.Navigate "[URL]http://www.clerk-17th-flcourts.org/Clerkwebsite/BCCOC2/OdysseyPA/CaseSummary.aspx?CaseID=7105840&hidSearchType=case_number_public&CaseNumber[/URL]="" & caseNum"
    
    Do While IE.Busy Or IE.ReadyState <> 4
    Loop
    Set objDoc = IE.Document
    Set objTable = objDoc.getElementsByTagName("Table")(1)
    stateReportNum = Trim(objTable.Rows(0).Cells(1).innerText) 'State Reporting Number
    
    caseStatus = Mid(Trim(objTable.Rows(2).Cells(1).innerText), 14)  'Case Status
    Set objTable = Nothing
    Set objDoc = Nothing
    
    IE.Quit
    
    Debug.Print stateReportNum
    Debug.Print caseStatus
End Sub

 

[sthack99]

If you know the case #, it may be easier to navigate to the website by building the URL first, rather than writing code to type in data and push buttons. For example: CaseNumber=CACE13021641 is part of the URL string. Sample code:

Sub pullCaseData()
    Dim IE As Object
    Dim caseNum As String
    Dim stateReportNum As String
    Dim caseStatus As String
    
    Set IE = CreateObject("InternetExplorer.application")
    caseNum = "CACE13021641"
    IE.Visible = False
    IE.Navigate "[url=http://www.clerk-17th-flcourts.org/Clerkwebsite/BCCOC2/OdysseyPA/CaseSummary.aspx?CaseID=7105840&hidSearchType=case_number_public&CaseNumber]Case Summary[/url]="" & caseNum"
    
    Do While IE.Busy Or IE.ReadyState <> 4
    Loop
    Set objDoc = IE.Document
    Set objTable = objDoc.getElementsByTagName("Table")(1)
    stateReportNum = Trim(objTable.Rows(0).Cells(1).innerText) 'State Reporting Number
    
    caseStatus = Mid(Trim(objTable.Rows(2).Cells(1).innerText), 14)  'Case Status
    Set objTable = Nothing
    Set objDoc = Nothing
    
    IE.Quit
    
    Debug.Print stateReportNum
    Debug.Print caseStatus
End Sub

I thought of that but the Case ID is unique for each case number, and I have no way of knowing what that is. Even if there was a way to just capture the URL, I could then just use IE.navigate.

 

[sthack99]

And, in case no one knows what I'm talking about, the Case ID is in the link, along with the Case Number. In this case, the Case ID is 7105840.

 

[sthack99]

bump