CODEWITHSUNDEEP
excelvbainternet-explorerweb-scraping
user14286902
user14286902

Reputation:

Getting a relevant details from web via code

Hi i am new to VBA and trying to upgrade my skills in VBA.

I am trying to getting the "Owner Name" and "Mailing Address" from this below web link

https://www.pbcgov.org/papa/Asps/PropertyDetail/PropertyDetail.aspx?parcel=30424032060001820

by using this ID in Sheet1"A1"

30-42-40-32-06-000-1820 ( that ID is the relevant to a person which name and mailing address will be paste in Col"B" and Col"C".

I have tried but could not make it.

Any help by anybody will be appreciated.

Sub Data()

    Set ie = CreateObject("InternetExplorer.Application")
    ie.Visible = True

    Url = "https://www.pbcgov.org/papa/?fbclid=IwAR28Ao4d0Ic5hTcd4w6BYv5FwaVYKFc3sCtmcqPI8Ctw2Q0jUy2zIdc7I-c"

    'Wait for site to fully load
    ie.Navigate2 Url
    
    Do While ie.Busy = True
        DoEvents
    Loop

    RowCount = 1

    With Sheets("Sheet1")
        .Cells.ClearContents
        RowCount = 1
        For Each itm In ie.document.all
            .Range("A" & RowCount) = itm.tagname
            .Range("B" & RowCount) = itm.ID
            .Range("c" & RowCount) = Left(itm.innertext, 1024)

            RowCount = RowCount + 1
        Next itm
    End With
    
End Sub

Upvotes: 0

Views: 46

Answers (1)

QHarr
QHarr

Reputation: 84465

This may be a little advanced but gives another way of looking at the problem.

The info you want is spread over two tables, and two rows within those tables. One table for the owner info (split across several lines); and one table, likewise, for address.

You can use css pattern #ownerInformationDiv table:nth-child(1) to isolate both of those tables, returned in a nodeList by applying querySelectorAll method of ie.document.

Loop each table, and whilst in a given table, loop the rows (ignoring the header row) and concatenate the text found in each row. Once the text is combined, for a given table, write it out to the sheet.

Another things to note include:

The full page load wait

While .Busy Or .ReadyState <> READYSTATE_COMPLETE: DoEvents: Wend

Qualifying object with class

Dim ie As SHDocVw.InternetExplorer

Use of a descriptive title

Public Sub WriteOutOwnersInfo()

VBA:

Option Explicit

Public Sub WriteOutOwnersInfo()

    Dim ie As SHDocVw.InternetExplorer
    
    Set ie = New SHDocVw.InternetExplorer
    
    With ie
        .Visible = True
        .Navigate2 "https://www.pbcgov.org/papa/Asps/PropertyDetail/PropertyDetail.aspx?parcel=30424032060001820"
        While .Busy Or .ReadyState <> READYSTATE_COMPLETE: DoEvents: Wend

        Dim tables As Object
        
        Set tables = .Document.querySelectorAll("#ownerInformationDiv table:nth-child(1)")
 
        Dim currTable As Object, currRow As Object, c As Long
        Dim i As Long, j As Long, lineOutput As String
        
        For i = 0 To tables.Length - 1
            Set currTable = tables.Item(i)
            lineOutput = vbNullString
            
            For j = 1 To tables.Item(i).Rows.Length - 1
                Set currRow = currTable.Rows(j)
                lineOutput = lineOutput & Chr$(32) & Trim$(currRow.innertext)
            Next
            c = c + 1
            ActiveSheet.Cells(1, c) = Trim$(lineOutput)
        Next
        .Quit
    End With
    
End Sub

Upvotes: 1

Related Questions