Microsoft KB Archive/107705
XL5: Cannot Use Crosstab Add-in Macro in Worksheet Object
The information in this article applies to:
- Microsoft Excel for Windows, version 5.0
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.
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.
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.
- 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.
- Start Microsoft Word for Windows version 6.0.
- 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.
- In the embedded worksheet, enter the following data:
- 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).
- 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.
Control is returned to the embedded worksheet, but the crosstab table is not created. You do not receive an error message alerting you as to why the crosstab table is not created.
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
Additional query words: officeinterop w4wexcel
Keywords : kbprg kbhowto
Version : 5.00
Platform : WINDOWS
Issue type :
Last Reviewed: April 9, 1999