Hi Andrew,
Subsequent to my earier post, i made some adjustments. I am still stuck at how to get the output data into one results sheet in the pivot table format. Your assistance would be greatly appreciated. The sample input data is valid for this code.
Thanks much!
Option Explicit
Private chromebrowser As Selenium.ChromeDriver ' declaration at the module level. note we changed DIM to Private
Sub LoopScrapePropTaxWebsiteNew()
Dim FindBy As New Selenium.By ' declaration of an auto instancing BY object to query any changes to HTML class name
Dim PropertyOwnerQueryResults As Selenium.WebElements ' Declaration of a variable that can take multiple web elements
Dim PropertyTaxResults As Selenium.WebElements ' Declaration of a variable that can take only one web element
Dim PropertyOwnerQueryResult As Selenium.WebElement
Dim PropertyTaxResult As Selenium.WebElement
Dim PropInfoTable As Selenium.WebElement
Dim PropTaxtable As Selenium.WebElement
Dim ValNo As String
Dim StratNo As String
Set chromebrowser = New Selenium.ChromeDriver
chromebrowser.Start 'The base URL in google chrome
chromebrowser.Get "https://ptsqueryonline.fsl.org.jm/PTSOnlineWeb/ptsquery.jsp" ' access the home page. If we wanted to access any page, we would just indicate the page name after the forward slash
Application.ScreenUpdating = False 'This line will turn off the screen flickering
Worksheets("PropTaxData").Activate ' Go back to the property Tax Sheet
Range("A2").Select
'-----------Loop to download the Property Tax Information
Do Until ActiveCell.Value = ""
ValNo = ActiveCell.Value
StratNo = ActiveCell.Offset(0, 1).Value
If chromebrowser.IsElementPresent(FindBy.Name("valuationno"), 5000) = False Then ' this is to account for any changes in class name as time passes. the number is the timeout period in millisecond, ie 3000 milliseconds is 3 sec
chromebrowser.Quit ' code to exit the chrome browser
MsgBox "Could not find search input box", vbExclamation ' Code to diplay message box with error
Exit Sub ' Exit sub routine
End If ' end if statement
chromebrowser.FindElementByName("valuationno").SendKeys ValNo
If chromebrowser.IsElementPresent(FindBy.Name("stratalotno"), 5000) = False Then ' this is to account for any changes in class name as time passes. the number is the timeout period in millisecond, ie 3000 milliseconds is 3 sec
chromebrowser.Quit ' code to exit the chrome browser
MsgBox "Could not find search input box", vbExclamation ' Code to diplay message box with error
Exit Sub ' Exit sub routine
End If ' end if statement
chromebrowser.FindElementByName("stratalotno").SendKeys StratNo
If chromebrowser.IsElementPresent(FindBy.Class("btn"), 5000) = False Then ' this is to account for any changes button name as time passes. the number is the timeout period in millisecond, ie 3000 milliseconds is 3 sec
chromebrowser.Quit ' code to exit the chrome browser
MsgBox "Could not find submit button", vbExclamation ' Code to diplay message box with error
Exit Sub ' Exit sub routine
End If ' end if statement
chromebrowser.FindElementByClass("btn", 3000).Click
'---------------Retriving the results of the property tax query
Set PropertyOwnerQueryResults = chromebrowser.FindElementsByClass("form-horizontal")
If PropertyOwnerQueryResults.Count = 0 Then ' Condition to account for an empty result set
'chromebrowser.Quit
MsgBox "No Results Found" & " " & "for Valuation No" & " " & ValNo, vbExclamation
Exit Sub
End If
For Each PropertyOwnerQueryResult In PropertyOwnerQueryResults ' loops through the web elements
Set PropInfoTable = PropertyOwnerQueryResult.FindElementByTag("tbody")
'Debug.Print PropInfoTable.Text ' print the webeleemnt texts to the immediate window
Next PropertyOwnerQueryResult
ProcessTable PropInfoTable
chromebrowser.FindElementById("back").Click 'Select the back button
Worksheets("PropTaxData").Activate
ActiveCell.Offset(1, 0).Select ' Go to the next cell
Loop
'Application.ScreenUpdating = True
End Sub
Sub ProcessTable(TableToProcess As Selenium.WebElement)
Dim AllRows As Selenium.WebElements
Dim SingleRow As Selenium.WebElement
Dim AllRowCells As Selenium.WebElements
Dim SingleCell As Selenium.WebElement
Dim OutputSheet As Worksheet
Dim RowNum As Long, ColNum As Long
Dim TargetCell As Range
Set OutputSheet = ThisWorkbook.Worksheets("Results")
Set AllRows = TableToProcess.FindElementsByTag("tr")
For Each SingleRow In AllRows
RowNum = RowNum + 1
Set AllRowCells = SingleRow.FindElementsByTag("td")
If AllRowCells.Count = 0 Then
Set AllRowCells = SingleRow.FindElementsByTag("th")
End If
For Each SingleCell In AllRowCells
ColNum = ColNum + 1
Set TargetCell = OutputSheet.Cells(RowNum, ColNum)
TargetCell.Value = SingleCell.Text
'Debug.Print SingleCell.Text
Next SingleCell
ColNum = 0
Next SingleRow
End Sub
