Using ADODB to access an open xls file

Question

Although I have been working with VBA for Excel for a long time, I have one problem that I cannot solve myself. I described it below, hope to get help or advice.
I am using Excel 2007 and Windows XP, all updated with new fixes.

I very often use the following code to get data from another book:

Set conn = New ADODB.Connection conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=g:\source.xls;Extended Properties=Excel 8.0;" Sql = "SELECT Field1, Field2 FROM [Sheet1$]" Set rst = New ADODB.Recordset rst.Open Sql, conn, adOpenForwardOnly Worksheets("Results").Range("A2").CopyFromRecordset rst rst.Close Set rst = Nothing conn.Close Set conn = Nothing 

As simple as it can be - just connect to the file and get some data from it. It works for so long that the source file, which is located on a shared network drive (g: \ source.xls), does not open on another computer.
When a user on another computer opened a file and I try to execute the following code, I notice one thing I would like to get rid of: the original Excel file (in read-only mode) opens on my computer and it is not closed after how the connection to this file was closed . To make matters worse, even if I close this source file manually, it leaves garbage in my file as if it never closed: see Image after executing the code (source files were closed earlier):

I began to believe that this is a mistake that cannot be resolved - I hope that I am wrong :)

Answer 1

This is actually a known bug, see http://support.microsoft.com/default.aspx?scid=kb;en-us;319998&Product=xlw . Querying an open Excel workbook using VBA causes a memory leak because this link does not open even when you close the connection and clear the object.

Answer 2

Is your version of Excel 2007 or newer?

if the provider Microsoft.ACE.OLEDB.12.0 is used by the supplier and your problem is resolved.

[] s

Answer 3

It would be much better to open the Excel data source using the built-in link in Excel, rather than for an ADO connection, for example:

 Dim xlApp As New Excel.Application Dim xlWrkBk As Excel.WorkBook xlApp.WorkBooks.Open FILENAME Set xlWrkBk = xlApp.ActiveWorkbook 

And then go from here