Connecting with Excel

Accessing Microsoft Excel

You can connect to Excel databases using an ODBC driver, an ODBC DSN, or OleDB.

Using ODBC

Select and name a range of rows and columns in the worksheet. This will allow the software to read the information as a table. Choose one of the following naming processes for your version of Microsoft Excel:

For Microsoft Office 2007:

Right-click the selection and choose Name a Range....

For Microsoft Office 2003

In Microsoft Excel, go to Insert → Name → Define.... 

Name the selection (e.g., "itemsTable"). The Excel file is ready to be used. 

There are several methods for communicating using ODBC:

Using ODBC Microsoft Excel Driver

• • In the datasets namespace, choose the DBs tab and create a new provider by clicking Create new.
  • Select the "Odbc Data Provider" in the "Provider" data field. 
  • For the "Database" field, choose Microsoft Excel Database.
  • Click Ok.

• • A new row is created in the data grid, click the ConnectionString column.
  • In the pop-up window, enter the path and the filename in the "Dbq" field.
  • Click the Test button to ensure that the connection is OK.

Using ODBC with a DSN

• • Go to your computer's Control Panel and select Administrative Tools.
  • Double-click "Data Sources (ODBC)". 

• • In the "ODBC Data Source Administrator" window, click Add. You are prompted to select a driver. 
  • Select "Microsoft Excel Driver (*.xls)". 
  • Click Select Workbook and select the name of the Excel file that was previously created. 
  • Name the Data Source, (e.g., "excelDatasource"). 
  • For Write access, uncheck the "ReadOnly" checkbox. 

• • In the Datasets namespace, choose the "DBs" tab and create a new provider by clicking create new.
  • Under the "Odbc Data Provider" options, choose "ODBC using DSN". Then, click OK.
  • Click the ConnectionString column of the new row. Then, enter the DSN in the "DSN" field.

Using OleDB

• • In the Datasets namespace, choose the DBs tab.
  • Select the "OleDb data provider" option in the combo-box and create a new provider by clicking create new. 
  • Choose the "Microsoft Excel Database". Then, click OK.
  • Click the ConnectionString column of the new row, then enter the path and the name of the Excel (.xls) file in the "DataSource" field. 

In this section...