return csv file as recordset

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

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?

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

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

return csv file as recordset

Related

Recent Posts