4

Although I've been working with VBA for Excel for quite a long time, I've one problem I cannot solve by myself. I've described it below, hope to get some help or advice.
I'm using Excel 2007 and Windows XP, all updated with newest patches.

I'm very often using following code to get data from another workbook: 

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 simply as can be - just connect to file and get some data from it. It's working perfect as long, as the source file that is located on a common network drive (g:\source.xls) is not opened on another computer.
When some user on another computer has opened the file and I try to execute the following code, I notice one thing that I'd like to get rid off: the source Excel file is opened (in a read-only mode) on my computer and it's not closed after the connection to that file has been closed. What's worse, even if I close this source file manually, it leaves some garbage in my file, like it was never closed: see the picture after couple of code execution (the source files has been closed before):
enter image description here

I started to believe it's a bug that cannot be solved - hope I'm wrong :)

ZygD
  • 22,092
  • 39
  • 79
  • 102
mj82
  • 5,193
  • 7
  • 31
  • 39

3 Answers3

5

Your Excel version is 2007 or later?

if is use Microsoft.ACE.OLEDB.12.0 at provider and your problem is solved.

[]´s

Bruno Leite
  • 1,403
  • 13
  • 17
3

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 with VBA causes a memory leak to occur as the reference is not released even when closing the connection and clearing the object.

Kittoes0124
  • 4,930
  • 3
  • 26
  • 47
  • Well, if it's a bug then there's not much to do with it. I'll test both workarounds methods mentioned on the linked page and try to use OLEDB.12 when it's possible. – mj82 Feb 08 '12 at 20:24
  • Yeah, it sucks but it's still an EXTREMELY useful way of getting data out of Excel books. You just have to make little changes here and there to get around potential issues. Since most data that you'd access with this method is stuff that is better suited in a database anyways I usually just end up aggregating all of the stuff I want to pull into a little Access DB with this method and then publish whatever Excel sheet, PDF, etc from that. – Kittoes0124 Feb 09 '12 at 04:45
  • That URL was causing redirect loop. Try [https://support.microsoft.com/en-us/kb/319998](https://support.microsoft.com/en-us/kb/319998). – ChipsLetten Aug 11 '15 at 12:23
0

You would be much better to open your Excel data source using the built in Excel reference, rather than an ADO connection e.g:

Dim xlApp As New Excel.Application
Dim xlWrkBk As Excel.WorkBook

xlApp.WorkBooks.Open FILENAME
Set xlWrkBk = xlApp.ActiveWorkbook

And then go from here instead

Matt Donnan
  • 4,933
  • 3
  • 20
  • 32
  • 7
    Why do you think it's much better solution? I don't think so because of 2 things: 1st: opening the file takes much more time than simply ADO connection, and 2nd: I will not be able to use SQL queries on that, which is the main reason I'm using ADO (unless I connect with opened file, which is pretty much the same situation)... The only positive I found is that it should (not tested yet) get rid off that "garbage" refences I have now, but for me the open time increasing is not worth it... – mj82 Jan 24 '12 at 15:16
  • Why would your store database style information on which you can run SQL; in an Excel file, why not use Access or another purpose built database program? The only reason I prefer this method is because you can step through different fields on the sheets and ultimately capture any possible bad data more easily. – Matt Donnan Jan 24 '12 at 16:05
  • 3
    That's because I'm working with already existing user's Excel files. It's not time-economic to rebuild every single file they're already using, just to put the same data into Access. And - more important - most of the users don't know Access and VBA, so they prefer working with the tool they know and that they can use to modify or adjust on their own - which is simply Excel file. Of course, where I can and when I design an application from scratch, I prefer using database as well, but in most cases I try to "integrate" different data sources, including .xls files, which cause that problem... – mj82 Jan 24 '12 at 16:27