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.