Web Query in XL2004 ?

[Matthias Carell]

Hi,

I've read the Microsoft KB Article about how to create web query files
(http://support.microsoft.com/default.aspx?scid=kb;en-us;274787). But
even if I just copy one of the example query files to a new one, I
can't open it. Excel disables them in the open dialog if I try to open
them. (As if they have the wrong file type, but even the original
files do not have a file name extension.)

Thanks in advance,
Matthias

 

[Jim Gordon MVP]

Hi,

Well, I see that article needs to be corrected!

They have it part-way right. At its simplest, a web query is a URL saved
as a text file. The article does not tell you two important things.

Thing 1: After you save the text file, change the extension from .txt
to .iqy.

Thing 2: Put the .iqy file into the Microsoft Office 2004:Office:Queries
folder so that your query will show up in the list when you choose form
the menu Data>Get External Data>Run Saved Query.

I put a lot of stuff about web queries on this page:
http://www.agentjim.com/MVP/Excel/ExcelHome.htm

For a simple step-by-step example:
1. Open a blank word document
2. Paste or type a URL in the document (first line all by itself).
3. File > Save As choose file type Plain Text
4. Close Word
5. Change the file extension to .iqy
6. Move the file to the Office 2004:Office:Queries folder

I hope this explains enough to get you started.

-Jim

 

[Matthias Carell]

Yep, this works!

Thank you very much!

Regards,
Matthias

 

[rob byrne]

I can use saved queries in XL 2004 to retrieve data, but am having a lot of
trouble getting XL to place the data in columns. When I use csv and prn
imports it places all my data in one column when I want it in two columns.
The same queries will do this in Xl XP and import data into columns, but on
the Mac I find it puts all the data in one column when I want it in two.
Does anyone know how I can control the formatting of imported data? No doubt
I have missed something
Thanks
RB

 

[Jim Gordon MVP]

Hi Rob,

Excel looks at the HTML code and formats the columns according to the
table tags.

When you run a web query, the External Data toolbar should automatically
display. (If it doesn't then toggle it on using View > Toolbars >
External Data.)

Click the Data Range Properties button on the External Data toolbar and
and see if adjusting the Data Layout properties gives you the effect you
desire.

You can also try my free Excel template if you are not running Excel v.X
with the 10.1.5 update (which breaks the template).
http://www.acsu.buffalo.edu/~gordonj/XL/DownloadPage.htm

-Jim

--
Jim Gordon
Mac MVP
MVP FAQ
<http://mvp.support.microsoft.com/default.aspx?scid=fh;EN-US;mvpfaqs>

 

[rob byrne]

Thanks Gordon
I worked that out and have that aspect of it working now. I have run into
another problem that you or someone may know the answer to. In VBA help
files on office 2004 there is a script for ComboBoxes that make use of the
LoadPicture function. According to the help file if I cut and paste this
code it should work. I have tried this and I consistently get an error
message that says automated function not supported in Visual basic. The
second issue is that if I change the control from a comboBox in the example
to an image control, the properties windows refuses to recognise any of the
image files I attempt to place in the control. It will not even allow me to
open office clip art pics. I am not sure of why this is the case. It seems
odd that Microsoft would inlcude a help file that doesn't work and even
odder that they would not support images. So I suspect that there is a
preference or something I need to turn on or a mistake I have made in my
code. Please note here I am attaching the image to a userform and not a
spreadsheet.
Thanks
Rob

 

[JE McGimpsey]

It seems odd that Microsoft would inlcude a help file that doesn't
work and even odder that they would not support images.

It's not intentional - it's a bug:

http://www.mcgimpsey.com/macoffice/excel/04/xl04bugs.html#userform_loadpi
cture