vba 使用 ADODB 访问打开的 xls 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8985580/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using ADODB to access opened xls file
提问by mj82
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.
虽然我使用 VBA for Excel 已经很长时间了，但我有一个问题无法自己解决。我已经在下面进行了描述，希望得到一些帮助或建议。
我使用的是 Excel 2007 和 Windows XP，它们都更新了最新的补丁。
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):
尽可能简单 - 只需连接到文件并从中获取一些数据。只要位于公共网络驱动器 (g:\source.xls) 上的源文件没有在另一台计算机上打开，它就可以完美运行。
当另一台计算机上的某个用户打开该文件并且我尝试执行以下代码时，我注意到我想摆脱的一件事：源 Excel 文件在我的计算机上打开（以只读模式）并且在与该文件的连接关闭后它不会关闭。更糟糕的是，即使我手动关闭了这个源文件，它也会在我的文件中留下一些垃圾，就像它从未关闭一样：在执行几次代码后查看图片（源文件之前已关闭）：
I started to believe it's a bug that cannot be solved - hope I'm wrong :)
我开始相信这是一个无法解决的错误 - 希望我错了 :)
采纳答案by Kittoes0124
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.
这实际上是一个已知错误，请参阅：http: //support.microsoft.com/default.aspx?scid=kb; en-us;319998& Product=xlw。使用 VBA 查询打开的 Excel 工作簿会导致内存泄漏，因为即使关闭连接并清除对象，引用也不会释放。
回答by Bruno Leite
Your Excel version is 2007 or later?
您的 Excel 版本是 2007 或更高版本？
if is use Microsoft.ACE.OLEDB.12.0 at provider and your problem is solved.
如果在提供商处使用 Microsoft.ACE.OLEDB.12.0，您的问题就解决了。
[]′s
[]的
回答by Matt Donnan
You would be much better to open your Excel data source using the built in Excel reference, rather than an ADO connection e.g:
使用内置的 Excel 引用而不是 ADO 连接打开 Excel 数据源会更好，例如：
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
然后从这里开始
