Quote:
Originally Posted by Vikesrock
I'm not all that great in Excel either, but I believe I have found a way to work around this issue.
For each table you want to grab you need to create a .icy file. This is a text file that describes the query. To find out how to format the file you can create a query to a valid page and then save the query, the only part you need to change is the event code in the URL.
Here's an example .icy file to get the Qualifications table from the DC regional
Code:
WEB
1
http://www2.usfirst.org/2009comp/events/DC/matchresults.html
Selection=3
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False
The way to use these files is a bit of a hack, but is the only thing I've gotten to work. According to the help files you can just click open and select the .icy and an import wizard will pop up letting you insert the data into the current sheet, but it just opens in a new workbook for me.
The workaround I found is to create the query with a valid page (any page), then locate it under Data->Connections (it helps to label the connections). Once you find the Connection click Properties -> Definition -> Browse and select the appropriate .icy file.
I tested this by setting one for the DC regional with my computer unplugged from the internet and then plugged back in and refreshed the data and the correct data appeared, so I think it should work. |
I tested it and it worked on pre-Excel2007. Thanks for the tip. I will get the week 3 regionals data ready and post it tomorrow. I will test it on Excel 2007 when I have time but I think it will work too.
To use it, all you have to do is go to the tab of the regional you are attending or interested in and press CTRL-SHIFT-P while you have internet access and let Excel do the rest for you. If you don't have internet access at the regional, you will have to wait until you can get internet access Friday night. Then on Saturday, you just have to update the results of other matches and the rankings. Then press CTRL-SHIFT-O to calculate OPR and CCWM.
I will try this out Friday morning to make sure they all work for Week 3 regionals. If there is a problem, I will fix it immediately. So if you encounter a problem, let me know and check back here for updates if needed. Good luck!
Ed Law