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
.
.
|