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;"""


xlrs.Open "SELECT FirstName, Surname, Age FROM [" & currentDataFileName & ".csv] WHERE Age > 65", xlcon
nextRow = Worksheets("Sheet1").UsedRange.Rows.Count + 1
Worksheets("Sheet1").Cells(nextRow, 1).CopyFromRecordset xlrs


Set xlrs = Nothing
Set xlcon = Nothing
End Sub
This entry was posted in Programming and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s