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
Advertisements
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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s