Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Querytable naming cells
hey, i am running a query from excel 2002. when the
query populates the cell the query is naming the cell. I would like it to stop doing that... any ideas? Thanks for any help you can give. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Querytable naming cells
Ray
I don't think it's possible. Why do you want to do it? Maybe there's another solution to your problem. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com Ray wrote: hey, i am running a query from excel 2002. when the query populates the cell the query is naming the cell. I would like it to stop doing that... any ideas? Thanks for any help you can give. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Querytable naming cells
Hey DK,
When I run the queries to populate my spreadsheet there are about 74 cells that are being named. Everday I run these queries the cells are being renamed with another dynamic name the query is applying. I will run these queries every business day so eventually I will have 250+ days creating 74 names a day. I am affraid that eventually the routine could create so many named cells that the sheet will blow up. Will it hurt to have a constantly growing list of defined names? -----Original Message----- Ray I don't think it's possible. Why do you want to do it? Maybe there's another solution to your problem. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com Ray wrote: hey, i am running a query from excel 2002. when the query populates the cell the query is naming the cell. I would like it to stop doing that... any ideas? Thanks for any help you can give. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Querytable naming cells
Ray
When I run the queries to populate my spreadsheet there are about 74 cells that are being named. Everday I run these queries the cells are being renamed with another dynamic name the query is applying. I will run these queries every business day so eventually I will have 250+ days creating 74 names a day. I am affraid that eventually the routine could create so many named cells that the sheet will blow up. Will it hurt to have a constantly growing list of defined names? Most definitely. How are you "running" the queries? In a macro using QueryTables.Add? -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Querytable naming cells
Dick,
I am using querytable.add. Can I do it different? Here is a code example of what I am doing.... Do DoEvents: 'setting up and running query. 'designing connection string and table destination With Worksheets("Data Input").QueryTables.Add (Connection:=Array(Array( _ "ODBC;DSN=Visual FoxPro Tables;UID=;PWD=;SourceDB=" & DataBase_Location & ";SourceType=DBF;Exclusive=No;BackgroundFet" _ ), Array ("ch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;" )), Destination:=ActiveCell.Offset(x, 0)) 'SQL string .CommandText = Array( _ "SELECT `" & strFundID & "_gl`.ytdbal_ly, `" & strFundID & "_gl`.ytdbal_ty" & Chr(13) & "" & Chr(10) & _ "FROM `" & strFundID & "_gl` `" & strFundID & "_gl`" & Chr(13) & "" & Chr(10) & _ "WHERE (`" & strFundID & "_gl`.acno='" & Account (ActiveCell.Offset(x, -4).Value) & "')") 'setting query properties .FieldNames = False .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = False .RefreshOnFileOpen = False .BackgroundQuery = False .RefreshStyle = xlOverwriteCells .SavePassword = False .SaveData = False .AdjustColumnWidth = False .RefreshPeriod = 0 .PreserveColumnInfo = False .Refresh BackgroundQuery:=False End With I am having to use a loop because of the varying pieces of information; fund id, account number, .... there are a number of accounts and not all are common between funds so in stead of creating a huge sql string I am just iterating through a list hence the constant adding of defined names because of every query callback. I appreciate the help. If you can help me figure this out I would forever be in your debt! Ray -----Original Message----- Ray When I run the queries to populate my spreadsheet there are about 74 cells that are being named. Everday I run these queries the cells are being renamed with another dynamic name the query is applying. I will run these queries every business day so eventually I will have 250+ days creating 74 names a day. I am affraid that eventually the routine could create so many named cells that the sheet will blow up. Will it hurt to have a constantly growing list of defined names? Most definitely. How are you "running" the queries? In a macro using QueryTables.Add? -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Querytable naming cells
Ray
The way to do this is NOT to add querytables, but to edit the existing querytables. If, for instance, you had five querytables on your worksheet, then you would write a macro like this Dim qt As QueryTable For Each qt In Worksheets("Data Input").QueryTables qt.Connection = "Build connection string here" qt.CommandText = "Build SQL statement here" qt.Refresh False Next qt By changing the existing querytables, you don't have to add new ones. If you don't add new ones, you won't get more named ranges, just the original five you started with. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com wrote in message ... Dick, I am using querytable.add. Can I do it different? Here is a code example of what I am doing.... Do DoEvents: 'setting up and running query. 'designing connection string and table destination With Worksheets("Data Input").QueryTables.Add (Connection:=Array(Array( _ "ODBC;DSN=Visual FoxPro Tables;UID=;PWD=;SourceDB=" & DataBase_Location & ";SourceType=DBF;Exclusive=No;BackgroundFet" _ ), Array ("ch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;" )), Destination:=ActiveCell.Offset(x, 0)) 'SQL string .CommandText = Array( _ "SELECT `" & strFundID & "_gl`.ytdbal_ly, `" & strFundID & "_gl`.ytdbal_ty" & Chr(13) & "" & Chr(10) & _ "FROM `" & strFundID & "_gl` `" & strFundID & "_gl`" & Chr(13) & "" & Chr(10) & _ "WHERE (`" & strFundID & "_gl`.acno='" & Account (ActiveCell.Offset(x, -4).Value) & "')") 'setting query properties .FieldNames = False .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = False .RefreshOnFileOpen = False .BackgroundQuery = False .RefreshStyle = xlOverwriteCells .SavePassword = False .SaveData = False .AdjustColumnWidth = False .RefreshPeriod = 0 .PreserveColumnInfo = False .Refresh BackgroundQuery:=False End With I am having to use a loop because of the varying pieces of information; fund id, account number, .... there are a number of accounts and not all are common between funds so in stead of creating a huge sql string I am just iterating through a list hence the constant adding of defined names because of every query callback. I appreciate the help. If you can help me figure this out I would forever be in your debt! Ray -----Original Message----- Ray When I run the queries to populate my spreadsheet there are about 74 cells that are being named. Everday I run these queries the cells are being renamed with another dynamic name the query is applying. I will run these queries every business day so eventually I will have 250+ days creating 74 names a day. I am affraid that eventually the routine could create so many named cells that the sheet will blow up. Will it hurt to have a constantly growing list of defined names? Most definitely. How are you "running" the queries? In a macro using QueryTables.Add? -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Querytable naming cells
Thank you very much Dick. It worked fine. I appreciate
the help! -----Original Message----- Ray The way to do this is NOT to add querytables, but to edit the existing querytables. If, for instance, you had five querytables on your worksheet, then you would write a macro like this Dim qt As QueryTable For Each qt In Worksheets("Data Input").QueryTables qt.Connection = "Build connection string here" qt.CommandText = "Build SQL statement here" qt.Refresh False Next qt By changing the existing querytables, you don't have to add new ones. If you don't add new ones, you won't get more named ranges, just the original five you started with. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com wrote in message ... Dick, I am using querytable.add. Can I do it different? Here is a code example of what I am doing.... Do DoEvents: 'setting up and running query. 'designing connection string and table destination With Worksheets("Data Input").QueryTables.Add (Connection:=Array(Array( _ "ODBC;DSN=Visual FoxPro Tables;UID=;PWD=;SourceDB=" & DataBase_Location & ";SourceType=DBF;Exclusive=No;BackgroundFet" _ ), Array ("ch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;" )), Destination:=ActiveCell.Offset(x, 0)) 'SQL string .CommandText = Array( _ "SELECT `" & strFundID & "_gl`.ytdbal_ly, `" & strFundID & "_gl`.ytdbal_ty" & Chr(13) & "" & Chr(10) & _ "FROM `" & strFundID & "_gl` `" & strFundID & "_gl`" & Chr(13) & "" & Chr(10) & _ "WHERE (`" & strFundID & "_gl`.acno='" & Account (ActiveCell.Offset(x, -4).Value) & "')") 'setting query properties .FieldNames = False .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = False .RefreshOnFileOpen = False .BackgroundQuery = False .RefreshStyle = xlOverwriteCells .SavePassword = False .SaveData = False .AdjustColumnWidth = False .RefreshPeriod = 0 .PreserveColumnInfo = False .Refresh BackgroundQuery:=False End With I am having to use a loop because of the varying pieces of information; fund id, account number, .... there are a number of accounts and not all are common between funds so in stead of creating a huge sql string I am just iterating through a list hence the constant adding of defined names because of every query callback. I appreciate the help. If you can help me figure this out I would forever be in your debt! Ray -----Original Message----- Ray When I run the queries to populate my spreadsheet there are about 74 cells that are being named. Everday I run these queries the cells are being renamed with another dynamic name the query is applying. I will run these queries every business day so eventually I will have 250+ days creating 74 names a day. I am affraid that eventually the routine could create so many named cells that the sheet will blow up. Will it hurt to have a constantly growing list of defined names? Most definitely. How are you "running" the queries? In a macro using QueryTables.Add? -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Range naming cells with blank cells through coding | Excel Discussion (Misc queries) | |||
naming cells | Excel Discussion (Misc queries) | |||
Naming cells | Excel Discussion (Misc queries) | |||
Re-Naming cells | Excel Worksheet Functions | |||
Naming Cells | Excel Programming |