Mac Web Query

[rantcliff]

Does anyone out there have any experience with doing web queries on Mac Excel? I have been trying several variations and can't get it to work correctly. It always pulls doen the full page, even though I have told it to only bring down the tables.

Any help?

Rich

[rantcliff]

Can i assume there are no excel experts on this site?

[billbaloney]

You'll need to provide more information for anyone to help. For instance, the query.

Excel sometimes perceives parts of a page, or an entire page, to be a table. But AFAIK OS X Excel 2004 behaves much like recent versions of Windows Excel w.r.t web queries.

[rantcliff]

Actually the PC version of web queries and the Mac version are very different. The PC version is several updates ahead. It allows you to simply drag the info from the web page and produce a live link. On the Mac you have to create a text document to grab the external data.

My problem is that I can create these text files and get them to grab the data from the web page, but it always captures the whole page, I cannot get it to just capture the table info. This makes it very hard to predict where the data will end up in the spreadsheet as it is determined by the size of ads, etc.

So, anyone worked with these buggers?

Rich

[erichazann]

Edit the .iqy file directly and where it says "Selection=EntirePage" change that to the table number(s), comma separated. To find the table number, view the source of the HTML page and search for <table (opening table tag) and count how many until you get to the table you want.

ex.
WEB
1
http://kitco.com/market/

Selection=20,23
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False

This takes two tables from the url. The NY Spot Price table, and the World Market Spot Price table. Change formatting to All if you want the HTML formatting preserved.