Creating a data entry form with the Excel Template Wizard

Applies to
Microsoft Excel 2002

The Excel 2002 Template Wizard is a downloadable add-in program that sets up a database to store data entered from a Microsoft Excel form. You can download the Excel 2002 Template Wizard add-in from Downloads on Microsoft Office Online. Follow the instructions on the Dowloads page to successfully download the add-in program, tmplwiz.exe, and to load it in Excel.

After loading the add-in program, the Template Wizard command is added to the Data menu in Excel. You can then use this wizard with any worksheet form that has cells designated for data entry.

From the workbook you create, the wizard creates a template you can use as the basis for other similar workbooks, and links the cells you specify to fields in a database it creates for you. When you create a new workbook from the template, enter data in the cells that are linked to the database, and then save that workbook, Excel creates a new record in the database containing the data you entered in the form.

For example, suppose that you want to track work-related injuries from multiple manufacturing sites. First, you would create an injury report form on a worksheet. Then you can use the Template Wizard to create a template from this form, and link it to a database. When workers fill out a new injury report in a form that is based on the template (and then save the form), they create both a printable report and a corresponding record in the database.

ShowCreating the database and adding data

When you use the Template Wizard, it creates a template (*.xlt) and a database from an Excel workbook. Each time you enter data in the template, it will be copied to the database. If you have other workbooks with data you'd like to include in the database, the Template Wizard can automatically add this data as long as it is arranged in the same way as the template that is linked to the database.

For example, if you indicate in the Template Wizard that the data in cell D4 of sheet Personal_Data is linked to the Employee ID field in the database, all existing workbooks with data that you want to add to the database must also have the employee number in cell D4 of sheet Personal_Data.

Database types

The database the wizard sets up for you can be an Excel workbook. It can also be a Microsoft Access, Microsoft FoxPro, dBASE, or Paradox database for which you have installed the necessary ODBC driver. Store the database in a network location that all users of the form have access to.

Create a data entry form for adding records to a database

  1. Open the workbook in which you'll enter the data you want to copy to a database.
  2. Enter text to serve as labels in the cells above or to the left of each cell where you want data entered on the worksheet. These labels will be used as field names in the database.
  3. On the Data menu, click Template Wizard.
  4. Follow the steps in the wizard. For help with a step, click the Help button on the wizard screen.

 Notes 

  • If you include records from existing workbooks when you create the database in step 4 of the Template Wizard, the locations of data in the existing workbooks must match the locations of the data fields in the template. For example, if you indicate that the data in cell D4 of sheet Personal_Data is linked to the Employee ID field in the database, all workbooks must have the employee number in cell D4 of sheet Personal_Data.
  • If the data locations do not match, the wizard may add incomplete or blank records to the database.
  • If you select dBASE or Microsoft FoxPro as the database type in step 2 of the Template Wizard, select only the folder that contains the database. Do not include a file name in the Type the location and name of the database box. Otherwise, an "Unexpected error" message is displayed when you select the database.
  • To make the template available to all users, store the template on an accessible network location. Then create a shortcut to the template and have users copy the shortcut to their Templates folders.

ShowUpdating records in a database and creating reports

You can use your database program as you would any database and create reports from the saved data. To change the data in a record, you can reopen and edit the saved copy of the form associated with the record. If you delete the copy of the form, the corresponding record is retained in the database and any future updates to the record can be done from within the database program. You can delete the record as you would any record in that type of database.

Change a record in a database created with the Template Wizard

  1. On the File menu, click Open.
  2. In the Look in box, click the folder that contains the saved copy of the form that's associated with the database record that you want to change.
  3. Click the workbook containing the form, and then click Open.
  4. Change any of the information in the form.
  5. On the File menu, click Save.
  6. Select whether to update the existing record or create a new record, and then click OK. You can also continue without updating the records.

 Notes 

  • If you don't have the saved copy of the form for the record you want to change, or aren't sure which workbook is associated with the record, use your database program to change the record from within the database itself.
  • If the database does not contain any records yet, the option to update the existing record will not be available.