Geek asks and answers

Return csv file as a recordset

I have an external program that exports data to CSV files. My users would like to access this data through the VBA function in excel. To do this, I thought of a wrapper for a CSV file read into a function that returns ADODB.Recordset. My code

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 compilation error with the expression "=". Can someone point me in the right direction, how should I name my function and skip the data?

+5
source share
3 answers

Solved this with some of my own changes along with Tim Williams' input. Here is the code for anyone 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
+5

, , , ..

0

Excel can directly import CSV files. So why not just give your users a CSV file and let them open it in Excel. Then they can run their scripts as needed, like any other spreadsheet?

Did I miss something?

-1
source

More articles:


All Articles