John

Just to reiterate from my prior post:

I create a valid query table in Excel (using MS Query) and always append
"_1" to the data range name.
I SAVE the workbook.
Then, I run my code. If something goes wrong and Excel starts incrementing
the data range name....I close the workbook, open the saved copy, amend the
code, and save the workbook again. I repeat the process until I get the
desired results. After that, I don't have to worry about incrementing names.

There may be a different/better way to achieve the same results, but I was
thankful to find even one method that worked. Really....one Excel starts
autonumbering the data range name, it tenaciously continues to.

I hope that helps.
***********
Regards,
Ron

XL2002, WinXP


"John Keith" wrote:

I am having the same issue... _1 _2 or no _# appending to the named range
of my QueryTable.

I am deleting and re-creating the Names and QTs each time but still have the
issue. When a RngName_1 was autocreated by the query .add, the it is the only
range name attached to the sheet. This makes no sense why the query.add
would choose to increment the _#

Here is what I am using to delete...
Dim WS As Worksheet
Dim QT As QueryTable
For Each WS In Worksheets
For Each QT In WS.QueryTables
QT.Delete
Next
Next
Dim N As name
For Each N In ActiveWorkbook.Names
N.Delete
Next

My VBA code is set to run each time the book is opened and the option to
re-FTP data was selected. Does the .delete need time to complete? adding a
donothing counter loop to fix?

Open1... Works with no appended _# so my dynamically created formula using
the named range work fine.

Open2... ERROR _1 has been appended.

Open3... Works with nothing appended.

Open4... ERROR _1 again

Each time the only named ranges stored in the WB are the 2 matching the
query tables I have created except the _# being appended or not.

any suggestions?

does closing the workbook do something to complete the named deletes?

Grabbing for straws here!

TIA
--
Regards,
John