Names not deleted until book closed
 

After deleting a query, and deleting its name from the name list, I
can't recreate a query with the same name without closing the workbook.
Here is a sample subroutine that illustrates the problem:

Option Explicit

Sub testA()
Dim qt As QueryTable
Dim nameEntry As Name

If Sheets(1).QueryTables.Count = 0 Then
Set qt = Sheets(1).QueryTables.Add _
("URL; http://www.cnn.com", Range("A1"))
qt.Name = "FirstQueryName"
qt.Refresh
Else
Sheets(1).QueryTables(1).Delete
For Each nameEntry In Sheets(1).Names
nameEntry.Delete
Next nameEntry
End If
End Sub


After executing this subroutine the first time, a query with the name
"FirstQueryName" is created. Upon the second execution, the query is
deleted. Then, after the third execution, the query is recreated, but
now the name is "FirstQueryName_1", even though the name has been
deleted from the name list! What am I missing?

This problem does NOT occur if I close and re-open the workbook between
the second and third invocations of the subroutine.

Names not deleted until book closed
 

Very strange.
I did renamed qt.Name = "FirstQueryName_1"
and it maintains that name no matter how many times I ran the macro.

For some reason it keeps it as a count. If we just go right in and name
it so, then there aren't any problems.
Very strange indeed. I will keep looking for solutions.

-E

wrote:
After deleting a query, and deleting its name from the name list, I
can't recreate a query with the same name without closing the workbook.
Here is a sample subroutine that illustrates the problem:

Option Explicit

Sub testA()
Dim qt As QueryTable
Dim nameEntry As Name

If Sheets(1).QueryTables.Count = 0 Then
Set qt = Sheets(1).QueryTables.Add _
("URL; http://www.cnn.com", Range("A1"))
qt.Name = "FirstQueryName"
qt.Refresh
Else
Sheets(1).QueryTables(1).Delete
For Each nameEntry In Sheets(1).Names
nameEntry.Delete
Next nameEntry
End If
End Sub


After executing this subroutine the first time, a query with the name
"FirstQueryName" is created. Upon the second execution, the query is
deleted. Then, after the third execution, the query is recreated, but
now the name is "FirstQueryName_1", even though the name has been
deleted from the name list! What am I missing?

This problem does NOT occur if I close and re-open the workbook between
the second and third invocations of the subroutine.