Re-using query names?

[tangcla]

I was wondering how I would be able to weed out and remove previous references to query names.
For example if I have an external data query named "qry1 by week", then remove the query, then create another one, how do I use the same query name again? Surely there's a reference to it somewhere, that I can eliminate?

[Cheeky Charlie]

If you delete all the data in a query Excel will prompt you about whether you want also to delete the query.

Manually, (instructions for xl2003 - not xl2010!?) Select a cell in the query range -> Data -> Import External Data -> Data Range Properties uncheck "save query definition" - will propmt you, <OK>

HTH

[tangcla]

No that won't delete the query name. After I un-save the query, it won't allow the name to be re-used.

[Cheeky Charlie]

How are you creating the query?

[tangcla]

How are you creating the query?

Using Microsoft Query: Tools --> External data --> new database query.

[Cheeky Charlie]

So you're actually saving queries - you should be given a file-save prompt when you save them showing the location, but try My documents -> My data sources.

Why not just reuse the existing query though?

[tangcla]

They aren't saved as a file, they're saved in the Excel spreadsheet.

Sometimes the existing query is changed too much, or I don't want to change the layout of the rest of the sheet, so I need to delete the query and start again.

[Cheeky Charlie]

They aren't saved as a file, they're saved in the Excel spreadsheet.

You sure?

Try Documents and Settings -> [your username] -> Application Data -> Microsoft -> Queries

At which stage do you save them?

[tangcla]

When it asks me for a location to return the query, then I go to query properties.

[Cheeky Charlie]

Ah.

Oh.

Dear.

I think it's a bit of a glitch in Excel - the query name is deleted when you unsave the query definition as we discussed at the start. (The "you're saving it as a query" idea was a red herring...)

But, Excel only refreshes its names references for this when the workbook is closed & reopened (I don't know whether closing it or opening it purges the names, but it's kind of irrelevant as you can't open it until after you've closed it).

Sooooo, long story short:
unsave query as discussed at top of page
save wbook
close wbook
open wbook
profit!

Finally eh?

HTH

[tangcla]

Okay, gotcha. Yeah I was gonna say, I think we were on two different topics... :p