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"
    .Wait 3000
    .FindElementById("medicine-name").SendKeys ("pump")
    .Wait 10000
    .Wait 3000
    .Wait 5000
    .Wait 5000
    .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
  • \$\begingroup\$ Cells(x, y) is confusing: the signature for Cells is Cells([RowIndex], [ColumnIndex]), which means you have x for rows and y for columns. \$\endgroup\$ – Mathieu Guindon Jul 11 '17 at 17:30
  • \$\begingroup\$ Yes it is. I meant x for rows and y for columns. Thanks for your comment sir Mat's Mug. \$\endgroup\$ – SIM Jul 11 '17 at 17:35
  • \$\begingroup\$ Yet x instinctively refers to the x-axis, and y to the y-axis; with that in mind the call would be Cells(y, x) which looks uglier, but isn't wrong. Better naming row/column would fix that though. \$\endgroup\$ – Mathieu Guindon Jul 11 '17 at 17:38
  • \$\begingroup\$ I'll must from now on. I think I should be more careful about naming variables. Thanks again. \$\endgroup\$ – SIM Jul 11 '17 at 17:42

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
| improve this answer | |
  • \$\begingroup\$ Thanks sir alecxe, for your review and the comment. It's nice to see that you always come up something new to follow along. Perhaps I need to take a good care of css selectors. I just tested your code and found it working nice as always. Thanks again. \$\endgroup\$ – SIM Jul 11 '17 at 17:06
  • \$\begingroup\$ @SMth80 right, try to grasp CSS selectors - it would be quite beneficial for you in web-scraping. Thanks. \$\endgroup\$ – alecxe Jul 11 '17 at 17:07
  • \$\begingroup\$ One request to you sir alecxe. Can you provide me with a link following which I can have a good idea on the basic of css selectors? Please, don't get bothered. \$\endgroup\$ – SIM Jul 11 '17 at 19:35
  • 1
    \$\begingroup\$ @SMth80 well, I don't know of the best resource - there is plenty of them on the web. Mozilla's docs are pretty descriptive..and practice - open up browser developer tools, the console and play around with $("css selector here") function. Thanks. \$\endgroup\$ – alecxe Jul 11 '17 at 19:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.