+ Reply to Thread
Results 1 to 11 of 11

Identifying Table For Scraping By Excel VBA

  1. #1
    Registered User
    Join Date
    10-17-2003
    Posts
    32

    Identifying Table For Scraping By Excel VBA

    I am trying to use Excel VBA to scrape data from a web table, but I am running into trouble referencing the table. The table I am trying to access is contained on the following webpage:
    Please Login or Register  to view this content.
    I have checked the source code for the new page and it looks to me like the data table starts on row 466 with this code:
    table class="grid" id="proj" cellspacing="0" cellpadding="2"

    I already have functioning VBA code that scrapes data from a table on a different webpage where the table I want to grab is defined with id=“playertable_0”. An excerpt from that code is at the end of this post.

    I tried to reuse that code by changing HTMLdoc.getElementById("playertable_0") to HTMLdoc.getElementById("proj") or HTMLdoc.getElementByClass("grid"), but the code throws a Runtime Error 91 (object variable or with block variable not set) at the subsequent line in my VBA code, which is "Set tableRows = playerTable.Rows". I assume that the error is because playerTable isn't grabbing any data, and it is impossible to count the number of rows in nothing. So the question is, how do I instruct VBA to grab the data from the table with id=proj?

    Thanks!

    Code Excerpt:
    Please Login or Register  to view this content.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Identifying Table For Scraping By Excel VBA

    Hi gurs,

    Excel has a newer tool called "Power Query" in 2010 and 2013 Excel. It is called "Get & Transform" in 2016 that does this problem in a blink. I'm using 2016 excel and clicked on the Data tab and then on "New Query" then on "From Other Sources" and then "From Web". I get an input box when I copy your URL from above. I then picked "Table 1" and the data imported into the Edit screen that I then Loaded into Excel.

    Easy Peasy!! See the attached where I 've done these steps. I'd bet you could change your URL to pull in more than the second week.

    Power Query Web Import.xlsx
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Identifying Table For Scraping By Excel VBA

    I do not even get in to the table lines. Access denied is the error I am getting on the send method.

  4. #4
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Identifying Table For Scraping By Excel VBA

    Hi Marvin,
    I do not have access to power query. Am I right to assume that this is enhanced functionality to importing data from web site? I have some problems with some kind of scripting deriver missing from PC, so I keep getting scripting error when I try to use the excel web import function. In the past, I used this function to import table from sites. I suspect the power query is an improved version of "From web" function in excel.
    I am sure is too much hassle to get the free download in to excel 2010, thus I shall wait until I get excel 2016.

  5. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Identifying Table For Scraping By Excel VBA

    Hi AB33,

    Power Query was an Add-In for 2010 and 2013 Excel. You can download it from:
    https://www.microsoft.com/en-us/down...3-580db12f0d7e

    It comes standard in the 2016 (or Office 365) version of Excel.

    Read more at
    https://support.office.com/en-us/art...5-89F6269CD605

  6. #6
    Registered User
    Join Date
    10-17-2003
    Posts
    32

    Re: Identifying Table For Scraping By Excel VBA

    Thanks for the idea Marvin. But the question is whether PowerQuery can be controlled via VBA. I have about a dozen pages that get scraped this way during each update, and I don't want to go through and manually update the results for a dozen PowerQuery tables.

  7. #7
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Identifying Table For Scraping By Excel VBA

    Hey gurs,

    Power Query will open and scrub those web pages each time the file is opened. You will create the Power Query to open those URL pages and when you open the file it will update all the data. It is exactly what you want. VBA is downstream from PQ. OR you can perform many of your VBA functions using power query.

    Here is a link to do exactly what you are asking for if you want to refresh the PQ using VBA.
    http://www.excelguru.ca/blog/2014/10...uery-with-vba/

  8. #8
    Registered User
    Join Date
    10-17-2003
    Posts
    32

    Re: Identifying Table For Scraping By Excel VBA

    Marvin, I have been playing around with PQ a bit. Definitely way better than the old Excel Web Query! But I am still running into a problem with my current need. Not all of the data is displayed on the first page of the results. My VBA will seek out the "Next" link and load the subsequent pages of data automatically. With PQ, I can't find a way to get it to load subsequent pages. I can't just create a second PQ table for page 2, etc., because the link for page 2 changes every week (whereas the link for page 1 doesn't change). So I think this leaves me back with my initial question: how do I get VBA to grab the data from the table with id=proj?

  9. #9
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Identifying Table For Scraping By Excel VBA

    Hi gurs,

    Can you do a PQ for page 1 and a second for page 2? You can have multiple PQs in the same workbook. Then simply append the two results together using a third PQ. I think there is a trick here that you must save the file before the append of the two.

    You could also have a PQ for page 1 and a second for page 2 that put then in separate xl files. Then create a third PQ that would pull those other two into the third.
    http://datapigtechnologies.com/blog/...nto-one-table/

  10. #10
    Registered User
    Join Date
    10-17-2003
    Posts
    32

    Re: Identifying Table For Scraping By Excel VBA

    The problem with using separate PQs is that the URL for page 2 changes each week, so I wouldn't be able to just update that PQ without editing the query and changing the URL. However, I actually solved the page 1/page 2 problem by having PQ save my login credentials (which for some reason grabs all results from the URL instead of just page 1).

    But my new issue is that updating all the PQ tables takes a loooong time (almost 90 seconds). My old VBA to capture similar tables used to take less than half of that time. Much as I love the PQ functionality, I wish I could figure out how to re-use my old VBA code and just refer to the correct table on the target page.

  11. #11
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Identifying Table For Scraping By Excel VBA

    Ah,
    I did not notice that you have included the increment as part of the URL
    URL = BaseURL & Increment
    I have copied the URL and put it on the URL line. No wonder why I was getting an error message on the send method. Basically the code was parsing the number of pages at the end of URL twice; that is

    http://fftoolbox.scout.com/football/...1&WeekNumber=2
    and then the 2 is appended again here on this line.
    BaseURL & Increment.

    I always put the URL on the separate line and appended all the parameters at the end of URL, so that I can easily change the parameters.
    There are a number of ways to change the parameters (In this case the page numbers). You can use any loop. If you know the number of pages you want to open, you can use a fixed loop. You can also use a hard coded reference, so that you can change the pages at the top before the URL. Let's say the default value is page 2 you can change it to page 3.
    I have tried to loop from pages 1 to 4. The loop works for the first 3 pages and then at loop 4, it errors. It is probably , there is no page 4.
    I have attached both codes
    Attached Files Attached Files
    Last edited by AB33; 09-22-2016 at 04:24 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA web data scraping from table
    By fotodj in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 08-25-2016, 03:18 PM
  2. Scraping/Importing Table
    By Madskillet in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-10-2016, 07:34 PM
  3. Scraping Data from Web Into Excel Using VBA
    By gurs in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 01-31-2015, 06:26 PM
  4. scraping info from one excel sheet to another
    By hawee in forum Excel General
    Replies: 1
    Last Post: 12-01-2014, 05:54 PM
  5. Scraping website into excel
    By eodsolo in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-12-2014, 06:25 AM
  6. [SOLVED] Excel 2010 Identifying data changed in cells and Identifying the changed rows
    By SandyLake in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-13-2013, 01:12 AM
  7. Are web scraping tools like XPathSelector available for use within Excel VBA?
    By cloudvirake in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-13-2012, 09:52 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1