Mac Web Query

R

rantcliff

Registered
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
 
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.
 
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
 
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.
 
Hi,

I'm using Excel for Mac 2011, and I followed the instructions to create a web query. I have it saved it as an rtf and then renamed it to a .txt file. (You couldn't save it as a txt in the first place- I had to choose .rtf at first)
Now when I go to 'Run Saved Query' and choose that file, I get an "An unexpected error occurred" sign and nothing else!! Super frustrating because I have no idea what the problem is.

Can anyone help with that? Would really appreciate it.
 
I don't have that version of the software, but it sounds like you created an RTF file then just changed the extension w/o making the contents of the file actually plain text. You can save files with the .txt extension in TextEdit if you go to the Format menu and select "Make Plain Text". But you should probably save them with the .iqy extension I think.
 
Ok so I did what you said, I'm not getting that error anymore that is good but now at first it says "retrieving data" for a second but then I don't get anything. Just a blank spreadsheet. I'd saved it as a utf-8.

What do you think I should do now?

Thanks so much for your help. I really appreciate it.
 
Quick update: It looks like there is something in the first two cells (for it won't let me insert another query into A1 and A2 but they are blank! I can right click and say "edit query" but nothing pops up! I'm soo confused. Any ideas?

Thanks again..
 
You must log in or register to reply here.
Back
Top