XL: Template Wizard Writes a Record to All Tables in Database

• 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.

This behavior is by design of Microsoft Excel.

MORE INFORMATION

To see an example of the behavior, follow these steps.

Creating the Database

1. Create a new Microsoft Access database called Test.mdb with 2 tables called Table1 and Table2.



2. Add one Name field to Table1 and one Amount field to Table2.



3. Do not create a primary key for either table. Close the database.

Creating the Template

1. Create a new workbook in Microsoft Excel and enter Name in cell A1 of Sheet1.



2. Save the workbook as Test.xls.



3. On the Data menu, click Template Wizard.



4. In the "Template Wizard - Step 1 of 5" dialog box, leave the default values and click Next.



5. In the "Template Wizard - Step 2 of 5" dialog box, click Access Database as the database type.



6. Click Browse, locate and select the Access database from step 1 (in the previous section). Click Open, and then click Next.



7. 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

1. Click Finish. On the File menu, click New. Double-click Test.xlt.
   
   A copy of the template is opened.



2. If a macro warning dialog box appears, click Enable Macros.



3. Enter any text into cell B1 of Sheet1, and then click Save on the File menu.



4. Click "Create a new record," and then click OK.
   
   This step writes a record to both Table1 and Table2 in the database.



5. 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.



6. Open the Microsoft Access database.

REFERENCES

For more information about the Template Wizard, click the Index tab in Microsoft Excel Help, type the following text

template

Additional query words: XL97

Keywords : kbtool xltemp xlwiz
Version : WINDOWS:7.0,7.0a
Platform : WINDOWS
Issue type : kbprb