No trouble, nice to help somebody out.
RBS
Jenny C. wrote:
> You are right!
> I am executing the code from a different workbook and
> it works perfect!
>
> Thanks a lot!!!
>
>
> "RB Smissaert" wrote:
>
> > 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
> >
> > "Jenny C." <(E-Mail Removed)> wrote in message
> > news:16F0D332-EF4F-495C-8A8C-(E-Mail Removed)...
> > > 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" wrote:
> > >
> > >> 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
> > >>
> > >> "Jenny C." <(E-Mail Removed)> wrote in message
> > >> news:E76CA48A-1F90-4B37-840A-(E-Mail Removed)...
> > >> > 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
> > >> >
> > >> >
> > >>
> > >>
> >
> >
|
Similar Threads
