使用ADODB访问打开的xls文件 [英] Using ADODB to access opened xls file

问题描述

尽管我一直在使用VBA for Excel很长一段时间，但我有一个我自己无法解决的问题。我已经在下面描述了，希望得到一些帮助或建议。

我使用的是Excel 2007和Windows XP，所有的更新都是最新的补丁。

我经常使用以下代码从另一个工作簿获取数据：

  Set conn = New ADODB.Connection 
 conn.OpenProvider = Microsoft.Jet.OLEDB.4.0; Data Source = g：\source.xls;扩展属性= Excel 8.0; 
 
 Sql =SELECT Field1，Field2 FROM [Sheet1 $]
 
设置rst =新建ADODB.Recordset 
 rst.Open Sql，conn，adOpenForwardOnly 
 
工作表（结果）。范围（A2）。CopyFromRecordset rst 
 
 rst.Close 
设置rst = Nothing 
 
 conn 。关闭
设置conn = Nothing 
  

只要连接到文件和从中获取一些数据。它的工作时间很长，因为位于公用网络驱动器（g：\source.xls）上的源文件未在另一台计算机上打开。

当另一台计算机上的某些用户已打开文件，我尝试执行以下代码，我注意到一件事我想要摆脱：源Excel文件在我的电脑上打开（在只读模式），它不关闭后与该文件的连接已关闭。更糟糕的是，即使我手动关闭这个源文件，它在我的文件中留下了一些垃圾，就像从来没有关闭过的那样：执行代码之后看到图片（源文件已经关闭）：

我开始相信这是一个无法解决的错误 - 希望我错了：）

解决方案

这实际上是一个已知的错误，请参阅： http://support.microsoft.com/default.aspx?scid=kb;en-us;319998&Product=xlw 。使用VBA查询打开的Excel工作簿会导致内存泄漏发生，因为即使关闭连接并清除对象，引用也不会释放。

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):

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

解决方案

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.

这篇关于使用ADODB访问打开的xls文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！