Gary,
I agree with Steve's approach. I encounter the same problem when using
MS Query or with a SQL query.

Here are two procedures that I call when I need to clear out any such range
names.

For use after MS Queries:

Public Sub DeleteQueryDefinedNames()
Dim n As Name
For Each n In ActiveWorkbook.Names
If n.Visible = False And InStr(1, n.Name, "QUERY", _
vbTextCompare) 0 And InStr(1, n.Name, _
"Query_from", vbTextCompare) = 0 Then
n.Delete
End If
Next
End Sub

You can replace the "Query" and "Query_from" strings with your own specific
"ClientTracking_" strings to get it to work.


For use after SQL queries:

Public Sub Delete_External_Names()
Dim nName As Name
On Error Resume Next
' First worksheet with names to delete
For Each nName In Application.Names
If nName.Name Like "SpecCalc!ExternalData*" Then
Application.Names(nName.Name).Delete
End If
Next nName
' Second worksheet with names to delete
For Each nName In Application.Names
If nName.Name Like "Metered_Ready!ExternalData*" Then
Application.Names(nName.Name).Delete
End If
Next nName
On Error GoTo 0
End Sub

This looks on specific worksheets for named ranges and deletes names that
start with "ExternalData". I discovered that I was accumulating both
workbook level and worksheet level named ranges after my queries. The sheet
names in this example were "SpecCalc" and "Metered_Ready".
You can replace them with your specific sheet names and the "ExternalData"
string can be replaced with your "ClientTracking_" string to get it to work
for you. If you have more than two worksheets, then additional For..Next
loops can be added.

I call on one or both of these procedures after running a query I have used.
It seems to keep the range names from accumulating in my workbooks.

HTH
Ed Gravley


"Gary West" wrote in message
8.16...
I created a query and then recorded the macro needed to save the code to
run it. I just discovered that every time it runs it inserts a named
range. A number getsincremented and added to the end of the range name
and somewhere Excel is keeping track of the last name even though I
deleted them from the sheet.

The first time I ran the query it created a range named ClientTracking_
1. The 2nd time I got ClientTracking_2. I stumbled across this by the
time it was up to _32. I deleted all the ranges, ran the query again and
the new one got called _33!

What I would like is to reset the named ranges to zero AND either have a
way to prevent this query from creating new ranges or, if that's not
possible, learn how to delete them programmatically.


Thanks. - gary

With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Documents and Settings\querypath\query.dqy" _
, Destination:=Cells(Rows.Count, "B").End(xlUp).Offset(1, 0))
.Name = "ClientTracking"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

Ed,

Thanks!

You might also want to just cycle through all the worksheets

Dim x as Long
Application.ScreenUpdating = False
For x = 1 to Activeworkbook.Worksheets.Count
Sheets(x).Select
*call code to delete names*
Next
Application.ScreenUpdating = True

The deletion is usally pretty fast, so speed shouldn't be a problem.

steve

"Ed Gravley" wrote in message
...
Gary,
I agree with Steve's approach. I encounter the same problem when
using
MS Query or with a SQL query.

Here are two procedures that I call when I need to clear out any such
range
names.

For use after MS Queries:

Public Sub DeleteQueryDefinedNames()
Dim n As Name
For Each n In ActiveWorkbook.Names
If n.Visible = False And InStr(1, n.Name, "QUERY", _
vbTextCompare) 0 And InStr(1, n.Name, _
"Query_from", vbTextCompare) = 0 Then
n.Delete
End If
Next
End Sub

You can replace the "Query" and "Query_from" strings with your own
specific
"ClientTracking_" strings to get it to work.


For use after SQL queries:

Public Sub Delete_External_Names()
Dim nName As Name
On Error Resume Next
' First worksheet with names to delete
For Each nName In Application.Names
If nName.Name Like "SpecCalc!ExternalData*" Then
Application.Names(nName.Name).Delete
End If
Next nName
' Second worksheet with names to delete
For Each nName In Application.Names
If nName.Name Like "Metered_Ready!ExternalData*" Then
Application.Names(nName.Name).Delete
End If
Next nName
On Error GoTo 0
End Sub

This looks on specific worksheets for named ranges and deletes names that
start with "ExternalData". I discovered that I was accumulating both
workbook level and worksheet level named ranges after my queries. The
sheet
names in this example were "SpecCalc" and "Metered_Ready".
You can replace them with your specific sheet names and the "ExternalData"
string can be replaced with your "ClientTracking_" string to get it to
work
for you. If you have more than two worksheets, then additional For..Next
loops can be added.

I call on one or both of these procedures after running a query I have
used.
It seems to keep the range names from accumulating in my workbooks.

HTH
Ed Gravley


"Gary West" wrote in message
8.16...
I created a query and then recorded the macro needed to save the code to
run it. I just discovered that every time it runs it inserts a named
range. A number getsincremented and added to the end of the range name
and somewhere Excel is keeping track of the last name even though I
deleted them from the sheet.

The first time I ran the query it created a range named ClientTracking_
1. The 2nd time I got ClientTracking_2. I stumbled across this by the
time it was up to _32. I deleted all the ranges, ran the query again and
the new one got called _33!

What I would like is to reset the named ranges to zero AND either have a
way to prevent this query from creating new ranges or, if that's not
possible, learn how to delete them programmatically.


Thanks. - gary

With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Documents and Settings\querypath\query.dqy" _
, Destination:=Cells(Rows.Count, "B").End(xlUp).Offset(1, 0))
.Name = "ClientTracking"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With