return csv file as recordset

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

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

More Related Contents:

  1. SumIf using Excel VBA
  2. How can I call a macro which contains a loop but only run one iteration of that loop?
  3. VBA comma separated cells, needt last entry only
  4. How can I find last row that contains data in a specific column?
  5. Copy from one workbook and paste into another
  6. Open Excel file for reading with VBA without display
  7. Wait for shell command to complete [duplicate]
  8. How to return the number of dimensions of a (Variant) variable passed to it in VBA [duplicate]
  9. How to add a named sheet at the end of all Excel sheets?
  10. How to remove all non alphanumeric characters from a string except period and space in excel?
  11. Find last used cell in Excel VBA
  12. VBA general way for pulling data out of SAP [closed]
  13. Does Dir() make any guarantee on the order of files returned?
  14. Call a Sub with a String
  15. Assign on-click VBA function to a dynamically created button on Excel Userform
  16. Using a wildcard to open an excel workbook
  17. How to add parameters to an external data query in Excel which can’t be displayed graphically?
  18. ADODB Connection String for .csv
  19. Excel macro -Split comma separated entries to new rows [duplicate]
  20. Get text from clipboard using GetText – avoid error on empty clipboard
  21. Benchmarking VBA Code
  22. The MacScript function is not working well in Office for Mac 2016! Any ideas?
  23. Display milliseconds in Excel
  24. VBA editor auto-deletes spaces at the ends of lines
  25. Application.Quit command not closing the entire Excel Application
  26. Trying to open the workbook in separate instance
  27. Excel VBA – select multiple columns not in sequential order
  28. How to check whether certain sheets exist or not in Excel-VBA? [duplicate]
  29. Excel – Using COUNTIF/COUNTIFS across multiple sheets/same column
  30. Delete all data rows from an Excel table (apart from the first)

Leave a Comment