Copy Table from webpage using Selenium VBA

Question

I am trying to copy the the chart from this webpage ( https://www.rosterresource.com/mlb-schedule-grid/) into a sheet in Excel. I've been using VBA with the Selenium reference to get to the webpage and try to find element by Xpath or ID, but I've having problems finding the chart. I think the chart I want to copy is in a different frame, but I'm unfamiliar with how to get to it properly.

Dim driver As Selenium.ChromeDriver
Set driver = New Selenium.ChromeDriver
Dim url As String

url = "https://www.rosterresource.com/mlb-schedule-grid/"

driver.Start "chrome", "https://www.google.com/"
driver.Wait 1000
driver.Get url
driver.Wait 5000
Element = driver.FindElementByXPath(???).Attribute("innerText") 'this is where I struggle
driver.Close

Range("a1").Value = Element

I opened the page and found that the table you want is in a IFRAME so you should be able to switch into it and get the data, if that's what you want. The IFRAME is just hosting a google spreadsheet so if all you want is the table, you can get it from docs.google.com/spreadsheets/d/…. — JeffC
– JeffC, Commented Jun 15, 2018 at 18:16
It doesn't have to be selenium, but that is what I have been using and starting to get familiar with. Your code seems to work to select the "table", but how can I "copy and paste" it into an excel sheet? — Matt O.
– Matt O., Commented Jun 15, 2018 at 18:20
@JeffC I have some alternate code that uses that "docs.google.com....etc" link to extract the data. I am unsure if that link will stay the same, so I'd prefer to use the rosterresource.com site directly in case that link changes. — Matt O.
– Matt O., Commented Jun 15, 2018 at 18:23
I have added a line at the bottom where you can extract the direct spreadsheet URL. — QHarr
– QHarr, Commented Jun 15, 2018 at 18:50

SIM · Accepted Answer · 2018-06-15 22:13:50Z

1

There are two nested iframes to reach the tabular data from that page. Try the below way to get all the data from every table of that page under waffle class name.

Sub FetchTable()
    Const link As String = "https://www.rosterresource.com/mlb-schedule-grid/"
    Dim posts As Object, post As Object, elem As Object, R&, C&

    With New ChromeDriver
        .get link
        .SwitchToFrame .FindElementByCss("iframe", timeout:=5000)
        .SwitchToFrame .FindElementByCss("iframe#pageswitcher-content", timeout:=5000)
        For Each posts In .FindElementsByCss("table.waffle tr")
            For Each elem In posts.FindElementsByCss("td")
                C = C + 1: Cells(R + 1, C) = elem.Text
            Next elem
            R = R + 1: C = 0
        Next posts
        .Quit
    End With
End Sub

answered Jun 15, 2018 at 22:13

SIM

22.5k6 gold badges45 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

QHarr Over a year ago

Gah..... I was playing around with the other iframe but didn't use switch to. Was gonna come back to it today. Well done!

QHarr · Accepted Answer · 2018-06-16 05:16:05Z

1

You can select iFrames and switch to them with the following syntax:

Option Explicit
Public Sub GetTable()
    Dim d As WebDriver, a As Object
    Set d = New ChromeDriver
    Const URL = "https://www.rosterresource.com/mlb-schedule-grid/"

    With d
        .Start "Chrome"
        .Get URL

        Set a = .FindElementsByTag("iframe")(1)  
        .SwitchToFrame 0
    End With
End Sub

The direct link to the spreadsheet containing the data, however, can be obtained with:

 .FindElementByCss("iframe").Attribute("src")

edited Jun 16, 2018 at 5:16

answered Jun 15, 2018 at 17:10

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Collectives™ on Stack Overflow

Copy Table from webpage using Selenium VBA

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related