Back to Index

 

How to: Connecting to Excel Files using the standard Oledb object

Besides using the custom Excel object, you can also use the OleDb data object in conjunction with the Jet provider.  The MDAC (Microsoft Data Access Components) includes the Jet Oledb data provider that you can use to connect to Excel and other data sources such as Access databases, dBase, Paradox and even Text Files.

For this example, let's assume you have an Excel file located at c:\myss.xls and the spreadsheet contains 1 tab called Budget.  The spreadsheet contains 4 columns shown below.

The properties for the above Excel file might look something like this:

Pay particularly close attention to the ConnectStringOtherParams, Tablename and Server properties.

ConnectStringOtherParams:  Given that the Jet provider handles multiple types of data sources, you need to give the provider a little more detail about what you are trying to access.  In the case of Excel, you need to specify some Extended Properties that tells the provider that you will be accessing an Excel file and, in our example, the First row of the spreadsheet represents the header (column names).   Extended Properties="Excel 8.0;HDR=YES"

Tablename:  Another thing to note when accessing Excel files is that you need to specify the tab to read or write.  To specify a tab you simply wrap it [] brackets and append the $ sign.   This tells the provider that you want to access the Budget tab, in case you have more than one tab in the spreadsheet.

Server:  Since you are accessing local files, you need to specify the path and file name.  You do this in the Server property.


© 2003 - 2007 Relational Solutions, Inc. - All rights reserved