The Template Wizard with Data Tracking is an add-in for Microsoft Excel.
The Template Wizard allows you to transform a workbook into an online
template or form, and to 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
Microsoft Excel workbook.
The add-in discussed in this article is provided "as is" and Microsoft
does not guarantee that it can be used in all situations. Although
Microsoft Technical Support can help with the installation and existing
functionality of this add-in, they will not modify the add-in to provide
Each time 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.
To use the Template Wizard, you must first install it. Then, in the Add-
Ins dialog box, select the Template Wizard With Data Tracking check box.
To link templates to dBASE or Microsoft FoxPro databases, you must also
install the appropriate Open Database Connectivity (ODBC) driver. In
addition, you must select the ODBC Add-In option in the Add-Ins dialog
For additional information, please see the following article in the
Microsoft Knowledge Base:
: XL7: "Unexpected Error" Appears When You Use 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.
Creating a Template
- Open the workbook that contains the layout of the data columns you
plan to use in the template. If you have not created a workbook, do
so before you start the wizard.
- To start the wizard, on the Data menu, click Template Wizard.
In the first step in the Template Wizard, you need to 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.
- Click the name of the workbook that contains the layout of the data,
and then click Next.
- In step 2 of the wizard, in the Database Type box, select the type of
database you plan to create. The default is Microsoft Excel Workbook.
However, you can select other Open Database Connectivity (ODBC) file
- In the Database Location And Name box, select a folder location and
name for the database file. Click Browse to change the database name
or folder location, or to select an existing database that does not
appear in the current folder. Then, click Next.
- 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 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. Note that you can change the name
of the field name by typing over it.
- 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.
- 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 decide to merge
an existing workbook at a later time. (See the "Merging Existing
Data" section in this article.)
- Click Next. Then, in Step 5 of the wizard, click Finish.
NOTE: The Template Wizard automatically saves the template and the
Using 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 save the workbook.
To create a workbook based on the template, follow these steps:
- Verify that the template is in the Office\Templates folder.
- On the File menu, click New, select the template, and then click OK.
NOTE: If Macro Virus Protection is enabled, you will receive a macro
virus warning. Click Enable Macros to continue.
- Type the data you want to add to the database file.
- On the File menu, click Save.
In the dialog box that appears, the following options exist: 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.
- 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 click Create A New
Record. You can modify an existing record by clicking Update The
Merging 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 identical to successfully merge workbooks.
To merge data after you create the template, follow these steps:
- On the File menu, click Open, and open the Templates folder (located
in the folder that contains Office).
- In the Files Of Type box, click Document Templates (*.dot), and open
the template you created using the Template Wizard.
- On the Data menu, click Template Wizard, and continue to click Next
until you see step 4 of the wizard.
- In step 4 of the wizard, click "Yes, Include," and then click Next.
- In the next dialog box that appears, click Select.
- In the Select Files To Convert dialog box, select the workbook that
contain the data you want to add to the database file, click Open,
and then click Finish.
"Getting Results with Microsoft Excel for Windows 95," pages 67-72
For more information about the Template Wizard, click the Index tab in
Microsoft Excel 7.0 Help, type the following text
and then double-click the selected text to go to the "Template Wizard"
For more information about the Template Wizard, click Contents and Index
on the Microsoft Excel 97 Help menu, click the Index tab in Help, type the
Template Wizard with Data Tracking
and then double-click the selected text to go to the "What is the
Template Wizard with Data Tracking?" topic. If you are unable to find the
information you need, ask the Office Assistant.
Article ID: 148314 - Last Review: August 17, 2005 - Revision: 1.3
- Microsoft Excel 97 Standard Edition
- Microsoft Excel 95 Standard Edition
|kbhowto kbualink97 KB148314|Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.