XL5: Cannot Use Crosstab Add-in Macro in Worksheet Object

• Microsoft Excel for Windows, version 5.0

SYMPTOMS

In Microsoft Excel, you cannot create a crosstab table in an embedded Microsoft Excel 5.0 worksheet object. The Crosstab ReportWizard appears to go through all the steps to create a crosstab table, but the table is not created and you do not receive any message informing you that the table cannot be created.

CAUSE

When you create a crosstab table, the crosstab add-in macro creates a new workbook to store temporary data. Because the creation of a new workbook is not allowed when you are in an embedded worksheet object, the crosstab table creation fails.

WORKAROUND

To work around this situation, do either of the following:

• Create a pivot table to display your data in the embedded Microsoft Excel 5.0 worksheet object. A pivot table is created successfully in an embedded worksheet, and the pivot table feature in Microsoft Excel version 5.0 is easier to use than the crosstab table feature when you are working with large amounts of data.

  -or-

• If you need to use the crosstab feature in a Microsoft Excel 5.0 worksheet object, use the following steps:

  1. Create the crosstab table in a new worksheet in Microsoft Excel

        version 5.0.
  

  2. Save the worksheet.

  3. Insert the saved worksheet as an object in the server application. To

        do this, in the Object dialog box, select the Create From File tab
        rather than the Create New tab.
  

Steps to Reproduce Problem

NOTE: The following steps assume you already have the crosstab feature set up and working correctly in Microsoft Excel version 5.0.

1. Start Microsoft Word for Windows version 6.0.

2. From the Insert menu, choose Object, and Select the Create New tab. From the Object Type list, select Microsoft Excel 5.0 Worksheet and choose OK.

3. In the embedded worksheet, enter the following data:

         A1: a
         A2: 1
   

4. Select the range A1:A2 and from the Insert menu, choose Name, and choose Define. In the Names In Workbook box, type "database" (without the quotation marks).

5. Do the following to create the crosstab table:

   a. From the Format menu (the Data menu if you are using Microsoft Excel

         4.0 menus), choose Crosstab to run the Crosstab ReportWizard.
   

   b. In the Introduction screen, choose Create New Crosstab.

   c. In the Row Categories screen, from the Fields In Database list,

         choose a, and choose Add. Choose Next.
   

   d. In the Column Categories screen, choose Next.

   e. In the Value Categories screen, from the Fields In Database list,

         choose a, and choose Add. Choose Next.
   

   f. In the Final screen, choose Create It.

For more information about using the Crosstab command in Microsoft Excel version 5.0, query on the following words in the Microsoft Knowledge Base:

   crosstab and pivot and table