2

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

2
+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.

3
  • 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
0

In EXCEL 2013, had the same problem: when creating a new connection, excel apparently used names and references to an older query which was deleted.

Solved by renaming the excel file (.xlsm in my case) to .zip, extracting the zip file to a folder, identifying which old query (.xml file) was giving me trouble in /xl/queryTables and removing that xml file. Then reassembled the excel file by zipping the files and directories into a new zip file that looked exactly as my original .xlsx file. Renamed the .zip file to .xlsx.

Upon opening Excel detected and fixed errors and I had I lost one connection in the process. But now I could fix the connection that I lost and I could make new connections that worked.

1
  • Welcome to Super User! This duplicates another answer and adds no new content. Please don't post an answer unless you actually have something new to contribute.
    – DavidPostill
    Nov 15 '16 at 11:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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