Share via

Using ADO with Excel Data Sources

  • Article

ADO is used for performing client/server data access between a data consumer and a data provider. ADO is a good choice, in many instances, for working with Excel data because it eliminates the sometime lengthy process of having to open Excel. Using ADO also makes it possible to use SQL to retrieve and manipulate data. When you use ADO to access Excel workbooks, you use the same OLE DB provider that you use to access data from Microsoft Access (tabular data sources with rows and columns). ADO can be used on both opened and closed workbooks with closed workbooks being the most common.
When working with Excel data, the workbook is like the Access database and the worksheet or named data range is like a table. Look at a connection string used to connect to an Access database versus an Excel workbook:
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=C:\Files\Northwind.mdb;"

sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=C:\Files\Sales.xls;" & _
           "Extended Properties=Excel 8.0;"

The only difference between the two, other than the filename, is that with the Excel string, you must specify the name of the data source in the Extended Properties argument.
As with Access, you use SQL statements to query data from Excel worksheets. However, the way you specify the table is different in Excel queries than those in Access. There are four different ways as follows:
When specifying the worksheet name by itself, you must use the worksheet name suffixed by the $ character and surrounded by square brackets:
    sSQL = "SELECT * FROM [Sales$]"
When specifying a worksheet-level range, prefix the range name with the name of the worksheet:
    sSQL = "SELECT * FROM [Sales$SheetLevelRangeName]"
When specifying a specific range address, use the following syntax:
    sSQL = "SELECT * FROM [Sales$A1:D10]"
When specifying a workbook level range name (a globally named range), use the name directly without the brackets:
    sSQL = "SELECT * FROM BookLevelName"
Note that the workbook can contain as many named ranges and worksheets as you want. You just need to know which one you want.
By default, the Jet provider assumes that the first row contains field names for the data. If this is true, you can perform other queries such as those using WHERE and ORDER BY clauses. Conversely, if the first row doesn't contain field names, you must let the provider know that as well or you will lose the first row of data. You do this by including the additional setting HDR=No to the Extended Properties argument:
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=C:\Files\Sales.xls;" & _
           "Extended Properties=""Excel 8.0;HDR=No"";"

Note that when you include multiple settings to the Extended Properties argument, the entire setting must be surrounded by double quotes with the individual elements delimited by semi-colons. If your data table doesn't contain a header, you will be limited to plain SELECT queries.

Hopefully, you find this information somewhat useful.

Comments

  • Anonymous
    August 10, 2004
    VEry nice and helpful