up vote 1 down vote favorite
share [g+] share [fb]

I have a Web Query looking at a site for data, and it gets the table selection wrong. Viewing source of the linked to web page I am able to identify the correct table.

In an Excel Web Query you are able to save to an external .iqy file, and I can then edit that file replacing the correctly identified table, which works. However I would like the query to be stored inside of the Excel file, and not an external .iqy file.

How is it possible to access and edit the internal query of an Excel Web Query?

I forgot to mention I am using Excel 2003

link|improve this question
73% accept rate
feedback

1 Answer

up vote 1 down vote accepted
+50

You have not specified which version of Excel you are using.

However, if you are using an .xslx file, you could search for your query inside it, as this is just an ordinary Zip file.

To examine it, copy the .xslx elsewhere, rename the extension to .zip, then open it and extract all the files while conserving directory structure. Open all the .xml files in notepad, or just double-click to view in the default viewer (normally Internet Explorer), then search for the name of the query website.

After finding it and editing the .xml file, just do the same operation in reverse : zipping and changing the extension back to .xslx. Only verify that the directory structure looks exactly the same as the original.

link|improve this answer
Sorry I forgot to mention the version. If it is not directly doable in 2003 maybe I can try a conversion to 2007 or 2010 and then save as the older format. I may try that, if no one else provides anything further. And if this is the best solution I will award the bounty to you – datatoo Sep 17 '11 at 17:20
I did what you suggested, using Excel 2010 then converted the file back to a 2003 version for the target users and this worked. I would still love to know if it is possible to do directly in 2003, you have provided a usable solution so far. Thanks @harrymc – datatoo Sep 17 '11 at 18:17
xslx is easier, since one can update the xml files. With 2003 and earlier the files are binary with unpublished format, and one needs to sign a non-disclosure agreement with Microsoft to get it. It is really not worth the effort. – harrymc Sep 17 '11 at 18:24
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.