The information in this article applies to:
- Microsoft Excel 97 for Windows
- Microsoft Excel for Windows 95, versions 7.0, 7.0a
SYMPTOMS
When you save a workbook that was created by using the Microsoft Excel
Template Wizard, Microsoft Excel writes records to tables in the database
that is linked to the workbook.
CAUSE
When you use the Template Wizard to create a template that is linked to a
database, Microsoft Excel saves a record to every table in the database.
Therefore, when you save a workbook that is linked to a database, a new
record is written to all tables in the database. This behavior occurs even
if you did not link worksheet cells to fields in all tables in the
database.
STATUS
This behavior is by design of Microsoft Excel.
MORE INFORMATION
To see an example of the behavior, follow these steps.
Creating the Database:
- Create a new Microsoft Access database called Test.mdb with 2
tables called Table1 and Table2.
- Add one Name field to Table1 and one Amount field to Table2.
- Do not create a primary key for either table. Close the database.
Creating the Template:
- Create a new workbook in Microsoft Excel and enter "Name" (without
the quotation marks) in cell A1 of Sheet1.
- Save the workbook as Test.xls.
- On the Data menu, click Template Wizard.
- In the "Template Wizard - Step 1 of 5" dialog box, leave the default
values and click Next.
- In the "Template Wizard - Step 2 of 5" dialog box, click Access
Database as the database type.
- Click Browse, locate and select the Access database from step 1 (in the
previous section). Click Open, and then click Next.
- Click cell B1 on Sheet1 to link that cell to the Name field in Table1.
NOTE: Do not link any cell to the Amount field in Table2.
Creating the Workbook That Is Based on the Template:
- Click Finish. On the File menu, click New. Double-click Test.xlt.
A copy of the template is opened.
- If a macro warning dialog box appears, click Enable Macros.
- Enter any text into cell B1 of Sheet1, and then click Save on the File
menu.
- Click "Create a new record," and then click OK.
This step writes a record to both Table1 and Table2 in the database.
- In the Save As dialog box, specify a location for Test1.xls, and then
click Save.
This step saves a copy of the Excel template.
- Open the Microsoft Access database.
The text you entered in cell B1 of Sheet1 is displayed in the Name field of
Table1. Table2 also contains a record but the Amount field is empty.
REFERENCES
For more information about the Template Wizard, click the Index tab in
Microsoft Excel Help, type the following text
template
and then double-click the selected text to go to the "What is the Template
Wizard with Data Tracking?" topic.
|