Best way to execute an SQL Query on an Excel Worksheet

[Guest]

Hi,

Several times in the past I have queried Excel Spreasheet from .NET code
using an OleDbConnection and a connection string of the type :
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mypath\myfile.xls Extended
Properties="Excel 8.0;HDR=Yes;";". And this worked just fine.

Now I'm trying to do the same thing, but from the vba code of the Excel file.
I have several queries ( all of them are of the type "Select SUM(field)",
and they returne only one value ) that I need to execute on the data of the
current Excel file.

I have first used the same approach with the following :

Dim conCalculsExcel As String
conCalculsExcel = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
pathExcel & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";"

Dim conExcel As ADODB.Connection
Set conExcel = New ADODB.Connection
conExcel.Open conCalculsExcel

Dim rset As ADODB.Recordset
Set rset = New ADODB.Recordset

Dim myQuery As String
myQuery = "SELECT SUM(TOTAL) FROM ...."

rset.Open myQuery, conExcel, adOpenStatic, adLockOptimistic

.....

rset.Close
Set rset = Nothing


All this works for a couple of times...
But I need to execute this about 700 times in one process,
and each time I open a Recorset it take about 4 Megs of RAM...
So I quickly run out of memory...

How do I release the memory taken by a Recorset after I used it ?

Or is there a better way to do this when you want to query directly in the
current Worksheet ?

Thanks a million for your help!!!


Jenny

 

[RB Smissaert]

There is a bug (memory leak) with this and you will have to run the query on
a closed workbook. If you do that it will be fine.

RBS

 

[Guest]

What is the cause of the memory leak ?
Is it something wrong in my code ? Or a bug in Excel API ?

If I execute the queries from another excel Workbook ( with the Workbook
containing the data close ), will this work ?

 

[RB Smissaert]

I think it is a bug in ADO. Nil to do with your code.
Just have to make sure the workbook you run the SQL on is closed.
If you do that it will be fine.

RBS

 

[Guest]

You are right!
I am executing the code from a different workbook and
it works perfect!

Thanks a lot!!!

 

[bart.smissaert]

No trouble, nice to help somebody out.

RBS

You are right!
I am executing the code from a different workbook and
it works perfect!

Thanks a lot!!!