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


.