How to use the Template Wizard with the Data Tracking add-in in Excel 2000

This step-by-step article describes how to use the Template Wizard with Data Tracking add-in for Microsoft Excel.

With the Template Wizard, you can transform a workbook into an online template or form, and link it to a designated database. The Template Wizard walks you through a five-step process of creating the database template that you use to automatically add data to a database or a Excel workbook. The add-in that is discussed in this article is provided as-is. Microsoft does not guarantee that it can be used in all situations. Although Microsoft Support Professionals can help with the installation and existing functionality of this add-in, they will not modify the add-in to provide new functionality. Each time that you type new data in a copy of the template and save it as a separate workbook, the wizard automatically adds the data to a database that you specify.

How to Use the Template Wizard

1. Start Microsoft Excel.
2. On the Tools menu, click Add-Ins.
3. Click to select the Template Wizard with Data Tracking check box, and then click OK.
   
   You may be prompted for your compact disc if this is the first time that you select the template.

The process for using the Template Wizard includes creating a workbook that contains the layout for the data, defining a template based on the layout in the workbook and an associated database, deciding whether to consolidate existing data from separate workbooks, and saving the template and the database.

Create a Template

1. Open the workbook that contains the layout of the data columns that you plan to use in the template. If you have not created a workbook, do so before you start the wizard.
2. To start the wizard, click Template Wizard on the Data menu.
   
   In the first step in the Template Wizard, you must define the name of the template. The name of the active workbook appears in the first drop-down list in the dialog box. Note that the list includes the names of all open workbooks.
3. Click the name of the workbook that contains the layout of the data, and then click Next.
4. In the Select the type of database you want the wizard to create list, select the type of database that you plan to create. The default is Microsoft Excel Workbook. However, you can select other database file types.
5. In the Type the location and name of the database box, select a folder location and name for the database file. Click Browse to select an existing database that does not appear in the current folder, and then click Next.
6. In step 3 of the wizard, define the links between the cells in the workbook and the fields in the database file. To define the links between cells and fields, click the Cell No. 1 box, click the cell in the workbook in which you will type new data, and then press the TAB key.
   
   When you press the TAB key, the wizard automatically types a name in the Field Name box. The name that the wizard uses is determined by the name in the workbook that is above or to the left of the cell that you specified in the Cell box. If you want to change the field name, type over it.
7. Continue to define the links between all the cells in the workbook you want to link to the database, and the fields in the database file. Use a different Cell box for each cell. When you finish establishing the links, click Next.
8. In step 4 of the wizard, determine whether to merge existing workbooks into the specified database. To merge information, click Yes, include. If you do not want to merge an existing workbook, click No, skip it.
   
   NOTE: To merge an existing workbook, the data in the workbook must be arranged in the same format as the template. You can merge an existing workbook later. (See the Merge Existing Data section in this article.)
9. Click Next. In Step 5 of the wizard, read the information very carefully, and then click Finish.

NOTE: The Template Wizard automatically saves the template and the database file.

Use the Template

To automatically add data to the database file, you can open a workbook based on the template that you created with the wizard, type new data in the workbook, and then save the workbook.

To create a workbook based on the template, follow these steps:

1. Verify that the template is in one of the following folders, depending on which operating system you are using.

   Microsoft Windows 95 or Microsoft Windows 98

   C:\Windows\Application Data\Microsoft\Templates\

   Windows 95 or Windows 98 with User Profiles Enabled

   C:\Windows\Profiles\username\Application Data\Microsoft\Shared\Templates

   Microsoft Windows NT 4.0

   C:\Windows\Profiles\username\Application Data\Microsoft\Shared\Templates

   Microsoft Windows 2000

   C:\Documents and Settings\username\Application Data\Microsoft\Templates
2. On the File menu, click New, select the template, and then click OK.
   
   NOTE: If Macro Virus Protection is turned on, you receive a macro virus warning. Click Enable Macros to continue.
3. Type the data that you want to add to the database file.
4. On the File menu, click Save.
   
   In the dialog box that appears, there are following options: Create a new record and Continue without updating. When you type new data in the same workbook and save the file again, a third option, Update the existing record, appears.
5. To add the data, click Create A New Record, and then click OK.
   
   Note that you can use the same workbook to continue adding more data to the database file. To reuse the workbook in this way, retype new data in the linked cells, save the file, and then click Create a new record. You can modify an existing record by clicking Update the existing record.

Merge Existing Data

You can merge data from other workbooks either when you create the template or after you create the template.

NOTE: To merge data from another workbook to the database file, the data must be arranged with the same layout as the template. If the data in the workbook has a different layout, the wizard adds a blank record to the database file without displaying a warning message. The worksheet names must also be the same to successfully merge workbooks.

To merge data after you create the template:

1. On the File menu, click Open, and then locate the Templates folder (located in the Application Data folder previously described).
2. In the Files of type box, click Templates (*.xlt), and then open the template that you created by using the Template Wizard.
   
   NOTE: If Macro Virus Protection is turned on, you receive a macro virus warning. Click Enable Macros to continue.
3. On the Data menu, click Template Wizard, and then continue to click Next until you see step 4 of the wizard.
4. In step 4 of the wizard, click Yes, Include, and then click Next.
5. In the next dialog box that appears, click Select.
6. In the Select files to convert dialog box, select the workbook that contains the data that you want to add to the database file, click Open, and then click Finish.

For more information about the Template Wizard with Data Tracking, click Microsoft Excel Help on the Help menu, type What is the Template Wizard with Data Tracking? in the Office Assistant or the Answer Wizard, and then click Search to view the topic.