I discover that when i recounting more then 200 QT on one sheet with this
simply code



Set qt_data = result_sheet.QueryTables.Add("ODBC;DSN=Excel Files;DBQ=" +
ActiveWorkbook.Name + ";DefaultDir=" + ActiveWorkbook.Path +
";DriverId=790;MaxBufferSize=2048;PageTimeout= 5;",
result_sheet.range("AA1"), sql_str)

With qt_data
.PreserveFormatting = True
.FieldNames = False
.BackgroundQuery = False
.AdjustColumnWidth = False
.RefreshStyle = xlOverwriteCells
.Refresh
End With
qt_data.Delete



my EXCEL.exe process (2003 ver.) becomes very slow, "hard" (~300-400 Mb) and
"unstoppable" (closing frames, but not unload process)

Can you give me any advice?

Don't do it?

Tim

"Gregory" wrote in message
...
I discover that when i recounting more then 200 QT on one sheet with
this simply code



Set qt_data = result_sheet.QueryTables.Add("ODBC;DSN=Excel
Files;DBQ=" + ActiveWorkbook.Name + ";DefaultDir=" +
ActiveWorkbook.Path +
";DriverId=790;MaxBufferSize=2048;PageTimeout= 5;",
result_sheet.range("AA1"), sql_str)

With qt_data
.PreserveFormatting = True
.FieldNames = False
.BackgroundQuery = False
.AdjustColumnWidth = False
.RefreshStyle = xlOverwriteCells
.Refresh
End With
qt_data.Delete



my EXCEL.exe process (2003 ver.) becomes very slow, "hard" (~300-400
Mb) and "unstoppable" (closing frames, but not unload process)

Can you give me any advice?

Is this advice? )))

I so prefer to store my algorithm (very flexible finance planning sheets
with my special formulas) which is very transportable

Of cause, i can transfer data to server (with RPC or other technology), make
recount, transfer back, but why i must do that?


"Tim Williams" <saxifrax@pacbell*dot*net ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ
ÓÌÅÄÕÀÝÅÅ: ...
Don't do it?

Tim

"Gregory" wrote in message
...
I discover that when i recounting more then 200 QT on one sheet with this
simply code



Set qt_data = result_sheet.QueryTables.Add("ODBC;DSN=Excel Files;DBQ=" +
ActiveWorkbook.Name + ";DefaultDir=" + ActiveWorkbook.Path +
";DriverId=790;MaxBufferSize=2048;PageTimeout= 5;",
result_sheet.range("AA1"), sql_str)

With qt_data
.PreserveFormatting = True
.FieldNames = False
.BackgroundQuery = False
.AdjustColumnWidth = False
.RefreshStyle = xlOverwriteCells
.Refresh
End With
qt_data.Delete



my EXCEL.exe process (2003 ver.) becomes very slow, "hard" (~300-400 Mb)
and "unstoppable" (closing frames, but not unload process)

Can you give me any advice?

Hi

Am I right that you are running 200 separate queries with source table(s)
in same workbook on same sheet? What do you want to achieve with this, I
wonder! Am I right in my suspect, that you calculate some summary values
from tables on other worksheets this way?

Anyway, I'm sure there are less resource-consuming solutions available -
maybe you describe, what kind of original data you have, and what do you
want to get out of them.


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )



"Gregory" wrote in message
...
Is this advice? )))

I so prefer to store my algorithm (very flexible finance planning sheets
with my special formulas) which is very transportable

Of cause, i can transfer data to server (with RPC or other technology),
make recount, transfer back, but why i must do that?


"Tim Williams" <saxifrax@pacbell*dot*net ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ
ÓÌÅÄÕÀÝÅÅ: ...
Don't do it?

Tim

"Gregory" wrote in message
...
I discover that when i recounting more then 200 QT on one sheet with this
simply code



Set qt_data = result_sheet.QueryTables.Add("ODBC;DSN=Excel Files;DBQ=" +
ActiveWorkbook.Name + ";DefaultDir=" + ActiveWorkbook.Path +
";DriverId=790;MaxBufferSize=2048;PageTimeout= 5;",
result_sheet.range("AA1"), sql_str)

With qt_data
.PreserveFormatting = True
.FieldNames = False
.BackgroundQuery = False
.AdjustColumnWidth = False
.RefreshStyle = xlOverwriteCells
.Refresh
End With
qt_data.Delete



my EXCEL.exe process (2003 ver.) becomes very slow, "hard" (~300-400 Mb)
and "unstoppable" (closing frames, but not unload process)

Can you give me any advice?

Yes, you are right. I have some arrays of input data (foundries and
registries), which were received from other program sources over 16 orgs in
our holding, then finance director preparing finance plan or other reports,
dynamically changing input data and report forms (for example, hi describes
[2,3,7,8][7-9][2^10-90|2^1000-1143,2^1148-3120,2^3135-5999] as profit of
[2,3,7,8] orgs in 3-d quarter, and else) . Of cause, some times, he is
making his work w/o internet, that means local realization of algorithm. If
i don't resolve time-memory problem by excel ODBC, i should try another
ODBC-complain local data-source, may be dbf or mysqld. Almost of all i
prefer current realization, cause it's work normal (one time :))




"Arvi Laanemets" ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ:
...
Hi

Am I right that you are running 200 separate queries with source table(s)
in same workbook on same sheet? What do you want to achieve with this, I
wonder! Am I right in my suspect, that you calculate some summary values
from tables on other worksheets this way?

Anyway, I'm sure there are less resource-consuming solutions available -
maybe you describe, what kind of original data you have, and what do you
want to get out of them.


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )



"Gregory" wrote in message
...
Is this advice? )))

I so prefer to store my algorithm (very flexible finance planning sheets
with my special formulas) which is very transportable

Of cause, i can transfer data to server (with RPC or other technology),
make recount, transfer back, but why i must do that?


"Tim Williams" <saxifrax@pacbell*dot*net ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ
ÓÌÅÄÕÀÝÅÅ: ...
Don't do it?

Tim

"Gregory" wrote in message
...
I discover that when i recounting more then 200 QT on one sheet with
this simply code



Set qt_data = result_sheet.QueryTables.Add("ODBC;DSN=Excel Files;DBQ="
+ ActiveWorkbook.Name + ";DefaultDir=" + ActiveWorkbook.Path +
";DriverId=790;MaxBufferSize=2048;PageTimeout= 5;",
result_sheet.range("AA1"), sql_str)

With qt_data
.PreserveFormatting = True
.FieldNames = False
.BackgroundQuery = False
.AdjustColumnWidth = False
.RefreshStyle = xlOverwriteCells
.Refresh
End With
qt_data.Delete



my EXCEL.exe process (2003 ver.) becomes very slow, "hard" (~300-400
Mb) and "unstoppable" (closing frames, but not unload process)

Can you give me any advice?

Hi

I think an ODBC query is a wrong tool here. I myself use queries in Excel
applications frequently, but only to retrieve data from external datasource,
when user opens the workbook, to consolidate or to split data, returned by
external query/queries, or to consolidate some data from table in same
workbook (p.e. mirror table with links to several workbooks, with gaps
between data blocks). As rule I never use several queries on same sheet. I'm
trying to keep the number of queries low, and allow the refreshing only on
open and manually.

In your case, better consider 2 options:
a) use formulas (SUM(), COUNT(), SUMIF(), COUNTIF(), SUMPRODUCT() etc.) to
calculate summary values (all calculations are made automatically, but when
the number of formulas increases, the workbook is getting slow).
b) create a procedure, which calculates the summary sheet, and is started
p.e. from command button on sheet, and maybe on Open event too (the workbook
is much faster, but the user has to remember to refresh the summary sheet).


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )



"Gregory" wrote in message
...
Yes, you are right. I have some arrays of input data (foundries and
registries), which were received from other program sources over 16 orgs
in our holding, then finance director preparing finance plan or other
reports, dynamically changing input data and report forms (for example, hi
describes [2,3,7,8][7-9][2^10-90|2^1000-1143,2^1148-3120,2^3135-5999] as
profit of [2,3,7,8] orgs in 3-d quarter, and else) . Of cause, some times,
he is making his work w/o internet, that means local realization of
algorithm. If i don't resolve time-memory problem by excel ODBC, i should
try another ODBC-complain local data-source, may be dbf or mysqld. Almost
of all i prefer current realization, cause it's work normal (one time :))




"Arvi Laanemets" ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ:
...
Hi

Am I right that you are running 200 separate queries with source
table(s) in same workbook on same sheet? What do you want to achieve with
this, I wonder! Am I right in my suspect, that you calculate some summary
values from tables on other worksheets this way?

Anyway, I'm sure there are less resource-consuming solutions available -
maybe you describe, what kind of original data you have, and what do you
want to get out of them.


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )



"Gregory" wrote in message
...
Is this advice? )))

I so prefer to store my algorithm (very flexible finance planning sheets
with my special formulas) which is very transportable

Of cause, i can transfer data to server (with RPC or other technology),
make recount, transfer back, but why i must do that?


"Tim Williams" <saxifrax@pacbell*dot*net ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ
ÓÌÅÄÕÀÝÅÅ: ...
Don't do it?

Tim

"Gregory" wrote in message
...
I discover that when i recounting more then 200 QT on one sheet with
this simply code



Set qt_data = result_sheet.QueryTables.Add("ODBC;DSN=Excel Files;DBQ="
+ ActiveWorkbook.Name + ";DefaultDir=" + ActiveWorkbook.Path +
";DriverId=790;MaxBufferSize=2048;PageTimeout= 5;",
result_sheet.range("AA1"), sql_str)

With qt_data
.PreserveFormatting = True
.FieldNames = False
.BackgroundQuery = False
.AdjustColumnWidth = False
.RefreshStyle = xlOverwriteCells
.Refresh
End With
qt_data.Delete



my EXCEL.exe process (2003 ver.) becomes very slow, "hard" (~300-400
Mb) and "unstoppable" (closing frames, but not unload process)

Can you give me any advice?