Connect to Microsoft Excel using the Database Component

You can use Microsoft Excel as a readable data source in HotDocs Developer.

HotDocs does not support writebacks to Excel.

To connect to Microsoft Excel using the Database Component

  1. Open an Excel document to use as a data source.
  2. In the Excel document, define Names and Ranges for the data you wish to appear in the HotDocs interview. For example:

In this example, the Name of the entire range of cells is EmployeeDetails. This Name appears as the name of the database table in HotDocs. The column headings EmployeeID, FirstName, Surname appear as the column headings in the database table. The data underneath the column headings appears as data in the table.

For more details on defining Names and Ranges in Excel, see the Excel help file.

  1. Create a new ODBC Data Source for Excel. Navigate to Administrative Tools > Data Sources (ODBC) > User DSN. Click Add. The Create New Data Source wizard appears.

If you are using a 64-bit operating system, you must create a 32-bit DSN. To do this, run the 32-bit version of the ODBC Data Source Administrator, which you can find in C:\Windows\SysWoW64\Odbcad32.exe. For more information, see http://msdn.microsoft.com/en-us/library/ms712362.aspx

  1. Select the required Microsoft Excel driver from the driver list. Click Finish. The ODBC Microsoft Excel Setup dialog appears.
  2. In the dialog, enter a new Data Source Name and Description. Click the Select Workbook button and navigate to the Excel document from Step 1. Click OK. Click OK to close the Excel Setup dialog. Close the ODBC Data Source Administrator dialog.
  3. Open HotDocs. Right-click on the template in which you wish the Excel data to appear and select Component Manager.
  4. In the Component Manager, create a new Database component. In the Database Component Editor:
    • Enter a Component name
    • In the Connection Properties are defined by drop-down list, select an ODBC data source.
    • In the Command type drop-down list, select Simple.
    • In the ODBC Data Source Name (DSN) drop-down list, select the Data Source created in Step 4.
    • Navigate to the Field Map tab. In the Table name drop-down list, select the required table. Link HotDocs variables to the required database columns and make any other changes to the database component.
    • Click Test. An interview window appears displaying the new database component. Check the correct data appears in the component. Once you are finished, close the interview window.
    • Click OK to save and close the database component. Close the component manager.

An ADO data source can also be used to connect to Excel. See Create a Database Component Using ADO for more details on using ADO data sources.