vba 将 csv 文件作为记录集返回
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11635526/
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
return csv file as recordset
提问by
I have an external program that exports data into CSV files. My users would like to have access to this data through a VBA function in excel. In order to do this, I thought about wrapping the CSV file read into a function that returns a ADODB.Recordset. My code is
我有一个将数据导出到 CSV 文件的外部程序。我的用户希望通过 Excel 中的 VBA 函数访问这些数据。为了做到这一点，我考虑将读取的 CSV 文件包装到一个返回 ADODB.Recordset 的函数中。我的代码是
Public Function getData(fileName As String) As ADODB.Recordset
Dim path As String
path = "C:\testDir\"
Dim cN As New ADODB.Connection
Dim RS As New ADODB.Recordset
cN.Open ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & path & ";" & _
"Extended Properties=""text; HDR=Yes; FMT=Delimited; IMEX=1;""")
RS.ActiveConnection = cN
RS.Source = "select * from " & fileName
Set getData = RS
End Function
I am trying to call this function using
我正在尝试使用
Dim a As ADODB.Recordset
Set a = getData("testFile.csv")
a.Open()
At this point, I get a compile error saying '=' expected. Could someone point me in the right direction on how I should call my function and loop through the data?
此时，我收到一个编译错误，提示“=”预期。有人能指出我应该如何调用我的函数并遍历数据的正确方向吗？
回答by
Solved it with some tweaks of my own along with input from Tim Williams. Here is the code for anyone else who might need help
通过我自己的一些调整以及 Tim Williams 的意见解决了这个问题。这是可能需要帮助的其他人的代码
Public Function getData(fileName As String) As ADODB.Recordset
Dim path As String
path = "C:\testDir\"
Dim cN As ADODB.Connection
Dim RS As ADODB.Recordset
Set cN = new ADODB.Connection
Set RS = new ADODB.Recordset
cN.Open ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & path & ";" & _
"Extended Properties=""text; HDR=Yes; FMT=Delimited; IMEX=1;""")
RS.ActiveConnection = cN
RS.Source = "select * from " & fileName
Set getData = RS
End Function
Now, the function can be called as
现在，该函数可以称为
Dim a As ADODB.Recordset
Set a = getData("testFile.csv")
a.Open
MsgBox(a.GetString())
a.Close
