VBA - 从电子表格的内容创建 ADODB.Recordset
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2484516/
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
VBA - Create ADODB.Recordset from the contents of a spreadsheet
提问by robault
I am working on an Excel application that queries a SQL database. The queries can take a long time to run (20-40 min). If I've miss-coded something it can take a long time to error or reach a break point. I can save the results to a sheet fine, it's when I am working with the record sets that things can blow up.
我正在开发一个查询 SQL 数据库的 Excel 应用程序。查询可能需要很长时间才能运行（20-40 分钟）。如果我错误地编码了某些东西，则可能需要很长时间才能出错或到达断点。我可以把结果保存到一张纸上，当我处理记录集时，事情可能会爆炸。
Is there a way to load the data into a ADODB.Recordset when I'm debugging to skip querying the database (after the first time)?
当我调试跳过查询数据库（第一次之后）时，有没有办法将数据加载到 ADODB.Recordset 中？
Would I use something like this?
我会使用这样的东西吗？
Query Excel worksheet in MS-Access VBA (using ADODB recordset)
回答by robault
I had to install the MDAC to get the msado15.dll and once I had it I added a reference to it from (on Win7 64bit):
我必须安装 MDAC 才能获取 msado15.dll，一旦我有了它，我就从（在 Win7 64 位上）添加了对它的引用：
C:\Program Files (x86)\Common Files\System\ado\msado15.dll
C:\Program Files (x86)\Common Files\System\ado\msado15.dll
Then I created a function to return an ADODB.Recordset object by passing in a sheet name that exists in the currently active workbook. Here's the code for any others if they need it, including a Test() Sub to see if it works:
然后我创建了一个函数，通过传入当前活动工作簿中存在的工作表名称来返回 ADODB.Recordset 对象。这是任何其他需要的代码，包括 Test() Sub 以查看它是否有效：
Public Function RecordSetFromSheet(sheetName As String)
Dim rst As New ADODB.Recordset
Dim cnx As New ADODB.Connection
Dim cmd As New ADODB.Command
'setup the connection
'[HDR=Yes] means the Field names are in the first row
With cnx
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source='" & ThisWorkbook.FullName & "'; " & "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"
.Open
End With
'setup the command
Set cmd.ActiveConnection = cnx
cmd.CommandType = adCmdText
cmd.CommandText = "SELECT * FROM [" & sheetName & "$]"
rst.CursorLocation = adUseClient
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic
'open the connection
rst.Open cmd
'disconnect the recordset
Set rst.ActiveConnection = Nothing
'cleanup
If CBool(cmd.State And adStateOpen) = True Then
Set cmd = Nothing
End If
If CBool(cnx.State And adStateOpen) = True Then cnx.Close
Set cnx = Nothing
'"return" the recordset object
Set RecordSetFromSheet = rst
End Function
Public Sub Test()
Dim rstData As ADODB.Recordset
Set rstData = RecordSetFromSheet("Sheet1")
Sheets("Sheet2").Range("A1").CopyFromRecordset rstData
End Sub
The Sheet1 data: Field1 Field2 Field3 Red A 1 Blue B 2 Green C 3
Sheet1 数据： Field1 Field2 Field3 Red A 1 Blue B 2 Green C 3
What should be copied to Sheet2: Red A 1 Blue B 2 Green C 3
应该复制到 Sheet2 的内容：红色 A 1 蓝色 B 2 绿色 C 3
This is saving me a HUGE amount of time from querying against SQL every time I want to make a change and test it out...
每次我想进行更改并对其进行测试时，这都为我节省了大量时间来查询 SQL...
--Robert
——罗伯特
回答by wqw
Easiest would be to use rs.Save "filename"and rs.Open "filename"to serialize client-side recordsets to files.
最简单的方法是使用客户端记录集rs.Save "filename"并将rs.Open "filename"其序列化为文件。
