How do I deal with non-static websites when doing web queries???

I recently built a large spreadsheet which would import data from a website using a web query (I was using about 30 different web pages) to collect data from the internet. I only wanted bits of the website, but I settled for importing the entire page as I could not find out how to grab a "piece" of the page and not all of it. Needless to say, it worked well and then referenced various cells to use the data I wanted from each sheet I had created. It took me some time to get it to work but I loved it. Then, sadly, after I thought I had it perfect, I went to use it one day and it was messed up. After reviewing the updated information and my data sheets, I realized a fear I had was true. The webpages were not static and therefore the data which was showing up in my referenced cells was changing and therefore rendered my entire spreadsheet worthless.

Besides simply finding a static website (doubtful), how can I correct this issue? in short, I want to import data from the web, have it located in a spreadsheet and reference a cell containing a particular type of data, and every time I open the spreadsheet, I want to to reference the correct data no matter where it moves to on the spreadsheet.

A quick example was I was referencing the number of goals made by a player (14 goals). This information is always on the webpage I was using, but moves around. The first time I set it up, I had to reference cell H128 and it would contain this information. However, the next day, more or less information was added to the website and while the website still contained the information, it would now be moved to, say, H135.

If you provide the website you use, I can have a look. Provide one or two examples.

Thank you for responding TKCZBW. Here is one of the pages:http://espn.go.com/nba/team/stats/_/...geles-clippers

As you will notice, down in the middle of the page is a bunch of player stats and then two lines for totals. I want to collect various entries on the total line. If I import the entire webpage (the only thing I know how to do) it will place this info into cells as I desired. But I then found out that after more games are played, the number of players (and often advertisements at the top of the page) then shift the "total" row up or down by a lot. This moves my desired data out of the cell in my spreadsheet which I was referencing to go to another sheet. You can imagine my discontent as I had done this for 30 different pages and did not realize the flaw until I was done and thought everything was working the way I wanted it to.

This should do it, although I have just recorded it rather than written from scratch so it can probably be trimmed somewhat.

Using the import data tool, you can specify which tables to import (little black arrow in yellow box next to data). It's not possible on all websites, but does appear to be on this one.

Please Login or Register  to view this content.

That is how I did it last time and it works, but then when he page changes, or in this case an additional player plays, it adds an additional row before the totals row in my spreadsheet, forcing the data row I desired to be pushed down as well. For instance, I have this entire page imported. For sake of argument, the data I want (total 3PTS) shows up in sheet 1, cell E184. I then have an additional page referencing sheet 1, E184 which gathers the data from sheet 1 (which came from the website). But the next day, after a game had played, an additional player played and they added that in a row before the totals row. Now, when my page updates upon opening (which I want it to do), the data for total 3PTS is now imported into sheet 1, cell E195, and the improper data is now imported into sheet 1, cell E184 where another sheet in the workbook is referencing, messing up my analysis.

Hmm.

All I can really think of would be to use:

Please Login or Register  to view this content.

To determine the last line that has been imported, then use a function of this (e.g. lastrow-2) to 'point' to the cell you want.

The simple answer is that you can't. Scraping websites only ever works whilst the content remains static, you simply have to update your code when things change. That's why it's always better to look for an API when working with data - ESPN has one here http://developer.espn.com, however it looks somewhat limited for general public use.

You could make your workbook more resilient by checking for specific data in the page, the code below looks for a table row with a totals class name and dumps the contents in a worksheet. You'll need to add a reference to Microsoft HTML Object library and this will only work on IE9+

Please Login or Register  to view this content.