Excel query creates named range
 

Gary

You probably don't need to create a new QueryTable every time. Create the
QueryTable once then use

Sheet1.QueryTables(1).Refresh

to update the data. If you need to change the QT every time, you can do
that also without deleting and recreating it.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"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

Excel query creates named range
 

Thanks, Dick. The data in the spreadsheet isn't updated in place.
Each time the query is run, it's being used to pull down new and
different client info. The macro inserts the new info at the next
blank line based on a certain column that will always contain data.
The spreadsheet is being used to track contact with clients (the
"clients" are internal to the company).

I created the query and then recorded a macro that used it. Here's
the VBA

With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\pathtoqueries\ClientTracking4.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

Someone helped out in this group with placing the cursor in the next
empty cell in column B. The query prompts for a number to be entered.

Are you saying that I can place the cursor and then use the Refresh
method to get new data without creating a new named range?

Thanks.

gary

"Dick Kusleika" wrote in
:

Gary

You probably don't need to create a new QueryTable every time. Create
the QueryTable once then use

Sheet1.QueryTables(1).Refresh

to update the data. If you need to change the QT every time, you can
do that also without deleting and recreating it.

Excel query creates named range
 

Gary



Are you saying that I can place the cursor and then use the Refresh
method to get new data without creating a new named range?

No, it sounds like you need to create query tables and whenever you do it
will create a name. I don't think there's anyway to prevent that, so you'll
have to use Steve's suggestion and delete the extraneous ones.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.


"Dick Kusleika" wrote in
:

Gary

You probably don't need to create a new QueryTable every time. Create
the QueryTable once then use

Sheet1.QueryTables(1).Refresh

to update the data. If you need to change the QT every time, you can
do that also without deleting and recreating it.