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.

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.

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.


.

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

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


.

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


.

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


.



.