Sign Up for Training |
Jet Express Support |
Jet Reports Company Site
Jet Reports Financials
Jet Professional
Jet Enterprise
Downloads
Community
Submit a Request
Give Feedback

Using an Excel workbook as a Jet data source


Expert - requires Admin permissions

Setup & Pre-Requirements

Using an Excel workbook as a data source can be a powerful tool for your Jet reports.

Here is an example of a workbook configured for that purpose - tabs are equivalent to database tables, and columns are set up as fields:

excelworksheet.png

To use the workbook as a Jet data source, we must first configure a Windows ODBC data source and then configure the Jet Excel add-in to use that data source:

Creating a Windows ODBC Data Source

  1. Run the Windows ODBC Manager.

    odbc1.png
    If you have 64-bit Excel, run the 64-bit ODBC.  Otherwise, run the 32-bit version
  2. Create a System DSN using the Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)

    odbc2.jpg
     
  3. Give the data source a name and then press Select Workbook... and choose the workbook you want to use as your data source
    odbc3.jpg
    odbc4.jpg
  4. In Excel, go to the Jet ribbon and click Data Source Settings.

    On the Data Source Settings dialog, click Add.

    Give your data source a unique name and select the Other (Universal) database type.  Click OK.

    odbc5.jpg

    If the Other (Universal) database type is not available, you will need to contact your regional Jet Reports representative to purchase a license for that connector type.
  5. Click Configure on the Connection tab...

    odbc6.png

    and choose Microsoft OLE DB Provider for ODBC Drivers on the Provider tab of the Data Link Properties dialog.

    odbc7.jpg

     

  6. On the Connection tab of the Data Link Properties dialog, choose the data source name you created in the ODBC Manager:

    odbc8.jpg

  7. Ensure that the correct file is listed in Enter the initial catalog to use:

    and then click Test Connection. 

    odbc9.jpg

    Click OK on the test message and then click OK on the Data Link Properties dialog.
  8. Click Test Connection on the Data Source Settings windows.  Then click OK to respond to the test message.

    odbc10.png

    Click the Set as Default button and then click OK.

    You are now able to use your Excel workbook as a Jet data sources.

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments