Importing Selected Data From csv File into Excel using VBA & ADO

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 Sub

Be First to Comment

Leave a Reply