vba 将 ADO 记录集复制到 Excel 工作表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24699972/
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
Copying ADO recordset into excel worksheet
提问by user2888590
I'm trying to open a CSV file and query it and return the results into column A of the second worksheet of "ThisWorkbook". I'm not getting any errors so I do not see why it is not copying the record set into excel.
我正在尝试打开一个 CSV 文件并查询它并将结果返回到“ThisWorkbook”的第二个工作表的 A 列中。我没有收到任何错误，所以我不明白为什么它没有将记录集复制到 excel 中。
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
Dim currentDataFilePath As String
Dim currentDataFileName As String
Dim nextRow As Integer
currentDataFilePath = "C:\Users\M\folder\"
currentDataFileName = "csv-file"
con.Open "Provider=Microsoft.JET.OLEDB.4.0;" & _
"Data Source=" & currentDataFilePath & ";" & _
"Extended Properties=""text;HDR=NO;FMT=Delimited;IMEX=1"""
'rs.ActiveConnection = con
rs.Open "SELECT Name FROM [" & currentDataFileName & ".csv] WHERE Datatype ='TYPE3'",
con
ThisWorkbook.Worksheets("Sheet2").Range("A:A").CopyFromRecordset rs
rs.Close
con.Close
Set rs = Nothing
Set con = Nothing
End Sub
回答by telemark
You might refer to the CopyFromRecordset()method.
你可以参考这个CopyFromRecordset()方法。
Based on your code above, after the rs.Opencommand you would add something like this:
根据您上面的代码，在rs.Open命令之后，您将添加如下内容：
ActiveWorksheet.Range("A1").CopyFromRecordset rs
See more here: http://msdn.microsoft.com/en-us/library/office/ff839240%28v=office.15%29.aspx
在此处查看更多信息：http: //msdn.microsoft.com/en-us/library/office/ff839240%28v=office.15%29.aspx
