It is supposed to get data from a CSV file, without opening it, and paste the data at "A1" on the activesheet. However, even though the connection specified that it has Headers (HDR=Yes) only some headers are coming through for me. The first row is row 1 of the data in the CSV.
Do I need to get the Headers separately? If so, how? (SEE UPDATE BELOW)
Thanks for any pointers.
Public Function getData(fileName As String) As ADODB.Recordset Dim path As String path = "C:\ExcelOMG Cookbook 2016\Closed CSV\" 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;""") cN.Open ("Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & path & ";" & _ "Extended Properties=""text; HDR=Yes; FMT=Delimited; IMEX=1;""") RS.ActiveConnection = cN RS.Source = "select * from " & fileName Set getData = RSEnd FunctionSub GetDataFromCSV() Dim a As ADODB.Recordset Set a = getData("DataFile.csv") a.Open Range("A1").CopyFromRecordset a a.CloseEnd Sub
I changed the Provider to Provider=Microsoft.ACE.OLE
Now I get a row for the Headers but only some headers come through.
I'm going to dig into this a little more. See if the CSV file is odd.