Scraping table contents from a webpage using vba with selenium

Question

My script is able to harvest full contents of a table from a webpage with javascript encrypted using vba in combination with selenium. The table has got a drop-down option from where the full contents can be selected by hitting "all". The table has got 300 rows of data which spread 7 column across. There are around seven steps to traverse to reach the destination page. It takes a while to parse them all. Just run it, sit back and relax until the browser is closed. It works perfectly. I could not manage to create the script with explicit wait cause I doubt there is any option in vba. Here is the working code.

Sub Table_Data() Dim driver As New WebDriver Dim posts As Object, post As Object, t_data As Object With driver .Start "chrome", "http://apps.tga.gov.au/Prod/devices" .get "/daen-entry.aspx" .FindElementById("disclaimer-accept").Click .Wait 3000 .FindElementById("medicine-name").SendKeys ("pump") .Wait 10000 .FindElementByClass("medicines-check-all").Click .Wait 3000 .FindElementById("submit-button").Click .Wait 5000 .FindElementById("ctl00_body_MedicineSummaryControl_cmbPageSelection").Click .Wait 5000 .FindElementByXPath("//option[@value='all']").Click .Wait 5000 End With For Each posts In driver.FindElementsByXPath("//table[contains(@class,'daen-report')]") For Each post In posts.FindElementsByXPath(".//tr") For Each t_data In post.FindElementsByXPath(".//td[@class='row-odd']|.//td") y = y + 1 Cells(x, y) = t_data.Text Next t_data x = x + 1 y = 0 Next post Next posts End Sub 

Answer 1

Good job once again!

I would eliminate one loop depth level - there is only a single table that you are interested it - iterate over its rows directly. And, I would switch to more concise CSS selectors to locate table rows and cells.

Also, I would rename post to a row and t_data to cell to correspond to what these variables represent:

For Each row In driver.FindElementsByCss("table#ctl00_body_MedicineSummaryControl_grdSummary tr") For Each cell In row.FindElementsByCss("td") y = y + 1 Cells(x, y) = cell.Text Next cell x = x + 1 y = 0 Next row