Use ADO to Load Excel Data into the DataGrid

This page shows how to load data from an Excel worksheet into a grid, namely, the Microsoft DataGrid.

One way to read Excel data into a grid is to use the Excel Object Model to churn through the worksheet, extract its data, and add it to a grid such as the Microsoft FlexGrid. This approach works well and is one I've used many times.

The FlexGrid, however, is read only. To edit the data you need to float a textbox over the grid. While this works, it requires a lot of extra code. To see how to do this, visit my Edit Data in a Read Only Grid page.

Presented here is a simpler solution using the read/write DataGrid. The DataGrid, however, must be bound to an ADO record source.

Since Excel is ODBC compliant, you can connect to it using ADO then retrieve the worksheet's data into a recordset by issuing a SQL query - just like you would open a recordset against a database table.

In this way, all the methods and features of the recordset object are available to process the Excel data. The recordset can then be bound to the DataGrid, via the ADO Data Control, and the grid will be populated automatically. Any changes you make to the grid will be reflected in the recordset.

Download Source Code

To use the ADO Data Control and DataGrid select them from the Projects | Components menu. You also need to select Microsoft ActiveX Data Objects and the Microsoft Data Binding Collection under Project | References. Lastly, add a reference to the Microsoft Excel Object Library.

Add a DataGrid and ADO Data Control to your form. Do not change the column properties on the property page for the DataGrid. Doing so affects its data binding behavior. Bind the DataGrid to the Data Control by selecting the Data Control in the DataGrid's DataSource property.

Onto the code. When you click the Open button a dialog lets you select an Excel file. I have supplied a sample file to work with. The core logic appears in the pLoadExcelData routine. It may help to step through that routine.

You need to establish an ADO connection to Excel just as you would with any other database. The difference is in the connection string. I set up a system DSN for this.

To create a DSN, open the ODBC applet in Control Panel, click the System DSN tab and press Add. Choose the Microsoft Excel Driver (*.xls) and click Finish. Type in the word "Excel" for the Data Source Name and description. The DSN name must match what you use in your connection string. Select a workbook and save your DSN. Although the DSN references a specific workbook, you can change it in code. See my Create DSN sample to programmatically create a system DSN.

In code I use a standard Excel connection string with place holders for DBQ and DefaultDir. I then set these to point to the previously selected Excel file. Note the DSN parameter must match the name of your system DSN.

Armed with a connection string, an instance of an ADO connection object is created. Its ConnectionString property is set as are a few other properties. To establish the connection to Excel its Open method is called and the ADO errors collection is examined. I wrapped this functionality in a class module to pretty it up.

Once connected we are almost ready to retrieve the worksheet's data. First, we need the worksheet's name. Think of Excel as your database and the worksheet as your table. We need its name to build a SQL Select statement.

The OpenSchema method of my class module opens an ADO recordset via the ADO connection object's OpenSchema method. Passing the adSchemaTables parameter to this method instructs ADO to fill the recordset with the schema or table/worksheet information from the Excel file.

Reading the name of the worksheet in this way is more flexible than hard coding it. Note that the worksheet name ends with a dollar sign ($) and is wrapped with quotes.

Now a SQL SELECT statement is built to retrieve the data. Note that the Excel file has a blank row that I don't want. The WHERE predicate in the SELECT statement filters this out.

A call to the OpenRecordset method of the class module executes the SELECT statement and populates an ADO recordset with the Excel data. If you want to be able to edit your data once it is in the grid, open the recordset with a dynamic cursor type.

The record set is then bound to the ADO Data Control by assigning it to the control's Recordset property. As soon as this statement executes, the grid is populated with the Excel data. Cool!

Normally I despise bound controls. I like to write my own code and know exactly what is happening, why and when. Thus, I disconnect the recordset by setting its ActiveConnection to Nothing.

A few grid properties are set. The notable ones are those allowing adds, deletes, and updates. Also to update, the column's locked property must be true.

If the recordset was not disconnected and the ODBC driver supported it, your changes would be saved back to the Excel worksheet. More commonly, you read and display the Excel data, allow changes then write the data to a real database.

To do this you can connect to the database and loop though the Excel recordset. For each record, build a SQL INSERT or UPDATE statement and execute it against your database.

You may get "Run time error -2147467259 (80004005)' : [Microsoft][ODBC Excel Driver]Selected collating sequence not supported by the operating system" when you run this in the IDE for the first time. This is a known bug which does not occur in the .Exe. See MSDN article Q246167.

Download the project. Create a system DSN for Excel as described above. Press F5 to run the program, stepping through the pLoadExcelData routine.

Change some grid values and use the debug or Locals windows to verify that the values in the mrst recordset have indeed changed.