# Solved: Remove Embedded Excel Queries



## RHurlburt (Oct 31, 2003)

As most are aware, once you have created a web query and brought it to an Excel workbook, that query is embedded in the sheet. If you delete the query by highlighting the row and column data, there will be a message that you are deleting the query. Press ok and the data is removed. OR you can click on the "parameters" within the sheet query and uncheck the "save query".

However, if you want to re enter that query you will be messaged "query exists on this sheet" ... or some message similar and you will need to add an " _1" to that query to get new results. 

The problem is that the original query is still embedded in the sheet and, at times, you can see the original link data displayed.

I want to completely remove that first query. I have even deleted the rows on the sheet where the original query was embedded. 

Excel 2007, Windows XP and the latest updates to Windows/Office 2007.


----------



## slurpee55 (Oct 20, 2004)

This works in 2003. Highlight the range of the query, go to Data, Import External Data, Data Range Properties. Uncheck the box marked "Save Query Definition." Click yes (or ok or whatever) for the next box to appear and it is gone. Click OK once more.


----------



## RHurlburt (Oct 31, 2003)

I have done that and it doesn't remove the query regardless. Once you follow that instruction, try to use the same query and reinstall. You will get a message similar to "Query exists on this sheet" or "Unable to find ...." and it will have the "_1", or "_2" added to the query so that it won't duplicate. ie. a query named "Yahoo Historical" would become "YahooHistorical_1". The original query cannot be used on that same sheet in the same area. That query also establishes a "Defined Name" for the area the data will go, and deleting that "Defined Name" doesn't help.

I am using Excel Office 2007, Excel 2007. It has a neat feature to look at the connections to the workbook, including the queries. I have even deleted that connection.

The reason I am trying to replace this query is that at one time we used "http://table.finance.yahoo.com/d/?a=......" . This query has changed to "http://finance.yahoo.com/q/hp?a= ..." and is no longer valid.

I have tried to edit the query using the method you describe, and it doesn't take the edit either. My thought is to delete the sheet and start over ... but I have a lot of data set up for Moving Averages, Bollinger Bands, etc., etc.

Thank you for your suggestion
Richard


----------



## slurpee55 (Oct 20, 2004)

MS says for 2007:
Editing a Web query

1. To edit a saved Web query, navigate to the query file on your computer and right-click on the file. The query file will have the name you gave it, and will end with an .iqy extension.

Note If you cannot find a file that you have just saved, it may be in a hidden folder. See Microsoft® Windows® Help to get help on viewing hidden folders.
2. On the shortcut menu, click Edit with Notepad. The query opens in Microsoft Notepad so you can make changes to the file.
3. In Notepad, you will see the contents of the query file. In the text, find the Web address where the imported data originated. For example:

http://moneycentral.msn.com/investor/external/excel/quotes.asp

At the end of the Web address, type the following:

?SYMBOL=["parameter", "Text that will prompt user to enter parameter"]

So that the whole line looks like:

http://moneycentral.msn.com/investor/external/excel/quotes.asp?SYMBOL=["QUOTE", "Enter stock, fund or other MSN MoneyCentral Investor symbols separated by commas."]
4. After adding this text, click Save on the File menu and close Notepad.

see
http://office.microsoft.com/en-us/excel/HA010346061033.aspx


----------



## RHurlburt (Oct 31, 2003)

Thank you very much.

I have edited the query several times and have created a couple with the correct URL info. Note that this does NOT solve the embedded query problem in the sheet. It retains the original URL information.

None have corrected my problem.

Again, thanks


----------



## slurpee55 (Oct 20, 2004)

This is something I don't (obviously) have any real experience with - nor do I have 2007. But if I see someone I know who does, I will refer them to your post.


----------



## RHurlburt (Oct 31, 2003)

Thank you so much for the attempt.

I do have quite a bit of experience with queries, etc. This one has bugged me for some time and I just can't seem to overcome it. 

One thing is that the "_1" (etc) that append to the query are a part of the query that increments each time you use the query. That is resolved with VBA code that deletes the appendage (as I call them) with the code that follows:
****
Sub RemoveQueryNames()
Dim nQuery As Name

With ThisWorkbook
For Each nQuery In Names
If IsNumeric(Right(nQuery.Name, 1)) Then
nQuery.Delete
End If
Next nQuery
End With
End Sub
*****
So, if one has to use the "_1", etc., in the query to establish it ... the VBA routine deletes it and it will no longer work.

If it isn't one thing, it is another.

You can see the "_1" if you look at the "Name Define" ... and don't use some sort of routine to delete them. Otherwise they will build to infinity.

Thanks again.

Richard


----------



## RHurlburt (Oct 31, 2003)

This is pretty close. There are a couple of issues with the Symbol, but I think I can get it from here. Thanks for the help.

In Excel 2007 in order to:
Edit an existing Web query

1. On the Data tab, in the Connections group, click Connections.
2. In the Workbook Connections dialog box, select the Web query, and then click Properties.
3. In the Connection Properties dialog box, click the Definition tab, and then click Edit Query.

This works unless you want to use ' ["Symbol"] ' in order to have a dynamic update on symbol change. Am working on that issue now.

Richard
I will close this thread.


----------

