I use this approach when I need to import only selected records or fields into Excel from a csv file.
This requires that you add a reference Microsoft Active Data Objects to support the ADO code.
The idea is to create an ADO connection to the csv files folder, then select the columns you want using a standard SQL statement in an ADO Recordset to link to the actual file. In the case below I’m selecting the FirstName, Surname and Age data from the csv file where Age is greater than 65.
The next section just copies the selected records to Sheet1 starting at the row below the last used row in the Sheet.
And then finally I close and dispose of the connection and recordset.
This relies on the csv file having headers on the columns being selected and in this example there is no error trapping to allow for no records being found, the file not existing, etc.
Sub GetMyCSVData() Dim xlcon As ADODB.Connection Dim xlrs As ADODB.Recordset Set xlcon = New ADODB.Connection Set xlrs = New ADODB.Recordset Dim currentDataFilePath As String Dim currentDataFileName As String Dim nextRow As Integer currentDataFilePath = "C:\My Data Folder\" currentDataFileName = "My Data File" xlcon.Provider = "Microsoft.Jet.OLEDB.4.0" xlcon.ConnectionString = "Data Source=" & currentDataFilePath & ";" & "Extended Properties=""text;HDR=Yes;FMT=Delimited;""" xlcon.Open xlrs.Open "SELECT FirstName, Surname, Age FROM [" & currentDataFileName & ".csv] WHERE Age > 65", xlcon xlrs.MoveFirst nextRow = Worksheets("Sheet1").UsedRange.Rows.Count + 1 Worksheets("Sheet1").Cells(nextRow, 1).CopyFromRecordset xlrs xlrs.Close xlcon.Close Set xlrs = Nothing Set xlcon = Nothing End SubAdvertisements