Details for Stennettc

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

Hi Andrew,

I am loving your content. The videos are insightful, simple and easy to understand. Using Selenium, I am writing a webscraping script to loop over an excel array of values to login a website, copy the particulars from that website and write the contents to an excel sheet.  The data is returned across as a table. I know the script works as the data is generated in the immediate window.  I need your guidance how to write the details to an excel file in a pivot table format with the appropriate headers.

Here is some sample values to test the script

16402008114

17006012198

00604005040

06901022020

031D5Y27115

19002019045

031D5Y27129

031D5Y27134

105D5Z02004

031D5Y27109

16301002229

Looking forward to your kind support

Option Explicit

Private chromebrowser As Selenium.ChromeDriver ' declaration at the module level. note we changed DIM to Private

Sub LoopScrapePropTaxWebsite()

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("topInfo")

    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
            Debug.Print PropertyOwnerQueryResult.Text ' print the webeleemnt texts to the immediate window
        
        Next PropertyOwnerQueryResult

    Set PropertyTaxResults = chromebrowser.FindElementsByClass("middleTable")
    
    If PropertyTaxResults.Count = 0 Then          ' Condition to account for an empty result set
        chromebrowser.Quit
        MsgBox "No Results Found", vbExclamation
        Exit Sub
    End If

        For Each PropertyTaxResult In PropertyTaxResults  ' This For Loop goes through the elements of the results table and prints the text in the immediate window
    
            Debug.Print PropertyTaxResult.Text
            

        Next PropertyTaxResult
        
    
    chromebrowser.FindElementById("back").Click 'Select the back button
     
    Worksheets("PropTaxData").Activate
        
    ActiveCell.Offset(1, 0).Select ' Go to the next cell
    
    Loop
    
   

End Sub

Hello, The code failed at the last line ie the code failed to populate the text box...Help Please

I am using the 64 bit version of the 2010 Office Professional Plus 

Option Explicit

Sub BrowseToSite()

Dim IE As SHDocVw.InternetExplorer
Dim HTMLDoc As MSHTML.HTMLDocument
Dim HTMLInput As MSHTML.IHTMLElement

IE.Visible = True
IE.Navigate "https://www.wiseowl.co.uk/"

    Do While IE.ReadyState <> READYSTATE_COMPLETE 'Pausing Excel while the website is loading
    Loop

Set HTMLDoc = IE.Document

Set HTMLInput = HTMLDoc.getElementById("what")

HTMLInput.Value = "VBA"

End Sub