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.

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

[email protected] 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.