Querytable naming cells

[Ray]

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.

 

[Dick Kusleika]

Ray

I don't think it's possible. Why do you want to do it? Maybe there's
another solution to your problem.

 

[Guest]

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?

 

[Dick Kusleika]

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?

 

[Guest]

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

 

[Dick Kusleika]

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.

 

[Ray]

Thank you very much Dick. It worked fine. I appreciate
the help!