Quote:
Originally Posted by Joe Ross
I'm having trouble updating the data for Silicon Valley. I change to the tab called SiliconValley and do ctrl-shift-p and I get Run-time error '1004'. If I hit debug, it's failing on the first querytable refresh. I notice that if I'm on one of the regionals with existing data, I see the external data toolbar pop up, but I don't see that on the later regionals. Is there something more I need to do?
|
Last year, I entered the link manually after it becomes available which is usually a few days before each week's competition. There is no data but the page would be setup so my link will be valid. At the end of the season, I wrote a macro to automatically set it up for all the weeks and it worked. Unfortunately FIRST changed something this year and it is no longer working. Also I was forced to migrate to Excel 2007 and it does not look like they support the .iqy files any more. So now I am back to manually setting up the link after the competitions are over.
If anybody is interested in using this spreadsheet during the competition which has internet access, you need to set up the link. The easiest way is this. Use a browser to navigate to the page where they show ranking. Copy the URL. Go to the tab of the regional you want to use.
In Excel 2003, select cell A1, go to Data, Import External Data and then New Web Query. Paste the URL data into the box that says Address. Then select the table of the page that shows the ranking only. You will see a check mark. Then click import. Then go to cell A101 and A251 and do the same thing for qualifying and elimination match results. That's it.
In Excel 2007, select Data tab and click From Web. The rest are the same.
If you need more help, please PM me. I will walk you through over the phone.
Sorry I don't know enough to setup the link ahead of time for you when the page does not exist. If somebody knows how to overcome this, please let me know.