Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel query and link named range | Links and Linking in Excel | |||
MS Query Refresh Based on Dynamic Named Range | Excel Discussion (Misc queries) | |||
How Do I Query a Named Range | Excel Worksheet Functions | |||
Query Named Range Return Single Column Value | Excel Worksheet Functions | |||
Importing Excel named ranges using MS Query | Excel Discussion (Misc queries) |