Excel QueryTable name auto increments

I am using a CSV file as a datasource for some Excel formulas. I am therefore loading the CSV file into a second worksheet and using a QueryTable to load the file quickly. This works well but there is one problem ... each time I delete the QueryTable and then re-create it the named range that the QueryTable addition creates is changed. The new name is the original name plus "_1"

How can I stop Excel from changing the name I specify in the name property of the QueryTable ?
How can Excel keep a memory of the named range created by the previous QueryTable even if the QueryTable and the named range have been explicitly deleted ?

I have looked at many articles online and they all talk about the problem being that deletion of the QueryTable does not delete the named range (and they therefore suggest to delete it first) but this does not appear to be the case in the copy of Excel 2010 that I am using.

The code is simple to recreate - just record a macro of you importing a text file and setting the delimiter and then amend the recorded macro to manually delete the previous QueryTable and named range before creating the new one.
LVL 3
AL_XResearchAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rory ArchibaldCommented:
Why delete the query table instead of simply pointing it at a different CSV file?
AL_XResearchAuthor Commented:
I am dynamically creating the worksheets and sometimes the target sheet can contain other data so I cannot reuse. Otherwise I would agree that is the best option.
Rory ArchibaldCommented:
I'm not sure I understand the relevance of the "other data" comment, but perhaps you could use an intermediate defined name that you can simply repoint at the new table.
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

AL_XResearchAuthor Commented:
Unfortunately I can't 'repoint' as the code used a fixed range name that matches the name that the query table was 'supposed' to be created with (before Excel changed it).

I could redesign everything to work a different way but that is not the point, I need to know how to stop excel renaming the query table. If I assign the new QueryTable the name 'MyData' I don't expect it to be called 'MyData_1' or 'MyData_2'
Rory ArchibaldCommented:
You can't stop Excel doing that as far as I know - any more than you can stop or reset the counters used when inserting any other object on a sheet.
AL_XResearchAuthor Commented:
Rory: went does excel do this if the QueryTable and it's associated named range been deleted ? There must be a way to reset excel or fool it
Rory ArchibaldCommented:
Usually the only way is to restart.
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Why not renaming the range back after querying?
AL_XResearchAuthor Commented:
I have found a 'solution' : Delete the containing worksheet.

If you delete the worksheet that contains the QueryTable and amend your import code to create the sheet that contains the QueryTable then it does not matter how many times you re-import, the name will never be changed by Excel.

Does anyone have any more ideas how to stop the supplied name changing ?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AL_XResearchAuthor Commented:
Qlemo: I can't just renamed the range as I don't know what name it will have as Excel appears to have it's own logic to the number it appends too the range name. Usually 1 but I have seen as high as 4.

Does anyone else have an idea on this as deleting the worksheet, although guaranteed to solve the issue, is not ideal in my code ?

Surely there is one Excel export so knows why and how these index number are appended to QueryTable objects ?
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
"as I don't know what name it will have" - that doesn't matter, as you know how it starts, and that there is no other range with a similar name. So going thru the Names collection until you find a match, then renaming it, should work.

But wait, didn't you state the QueryTable target range is renamed too to the new name?
AL_XResearchAuthor Commented:
Yes of course you can cycle through the names and do a match and indeed that is one of the methods I have used, but my point is that you shouldn't have to if the existing QueryTable and it's named range is deleted before a new one is created.

To answer your last question; no, the QueryTable target name is not renamed by my code. I just supply the QueryTable's 'name' property as normal.

My question is why and how is excel doing this ? Given the suffix can vary from 1 to 4, seemingly randomly, whilst my code remains unchanged there must be some Excel 'logic' to this or some application property that has an impact
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
I'm using WebQueries myself (for keeping EE points stats). I do not care about the created range names, but the automatically generated range names are always the same.

Why? Because I save the workbook after processing - and that does the trick. It cleans out the name cache obviously. I assume Excel is keeping the old name for Undo/Redo reasons. However, after saving a workbook (or deleting a worksheet), you cannot undo changes, and hence old stuff can be removed from internal memory.
AL_XResearchAuthor Commented:
It seems to me the real problem is that Excel has a 'memory' of all the range names defined on a worksheet that is not cleared unless the worksheet is deleted.

I have tried manually clearing the 'undo list' by simply copying a named range to itself but this did not work.

Does anyone have an idea how to access or clear Excel's hidden cache ?
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
As said, saving the workbook works to clear out the cache. I've not seen a direct method (yet).
AL_XResearchAuthor Commented:
This does not answer the question of how to stop Excel renaming the QueryTable named range but it does provide a reliable workaround.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.