Microsoft KB Archive/163081

= PRJ: Cannot Run CreatePivotTables Macro with Excel 97 =

Article ID: 163081

Article Last Modified on 10/7/2003

-

APPLIES TO


 * Microsoft Project 4.1 Standard Edition
 * Microsoft Excel 97 Standard Edition

-



This article was previously published under Q163081



SYMPTOMS
When you run the CreatePivotTables macro in Microsoft Project, you receive the following error message:

An error has occurred during macro execution. This macro will close now. Please try again.



CAUSE
The CreatePivotTables macro included with Microsoft Project for Windows version 4.1 is incompatible with Microsoft Excel 97 for Windows or later. The macro was designed to be used with Microsoft Excel for Windows 95, version 7.0.



WORKAROUND
To correct this problem modify the CreatePivotTables macro using these steps:

 Start Microsoft Project. On the Tools menu, click Macros. In the Macro Name list, select CreatePivotTables, and click Edit.

The macro is displayed in the Module Editor window.  Locate the following line of code: Const MS_EXCEL_APP = "Excel.Application.5" and change it to this: Const MS_EXCEL_APP = "Excel.Application"   Locate the following block of code ' Clean up and exit. oExcel.Visible = True oXLAssign.Select oExcel.ScreenUpdating = True AppActivate MS_EXCEL oExcel.WindowState = MAXIMIZED Set oExcel = Nothing Exit Sub and change the code so that it looks like this: ' Clean up and exit. oExcel.Visible = True oXLAssign.Select oExcel.ScreenUpdating = True AppActivate oExcel.Caption oExcel.WindowState = MAXIMIZED Set oExcel = Nothing Exit Sub  On the View menu, click Gantt Chart (or another view) to exit the Module Editor.</li> On the File menu, click Exit.

The changes to the CreatePivotTables macro are automatically saved to Global.mpt.</li></ol>

NOTES: The new CreatePivotTables macro always starts a session of Microsoft Excel, even when Excel is running. To avoid running multiple instances of Microsoft Excel, close the active instance of Microsoft Excel before you run the macro.

The default workbook in Microsoft Excel 97 must contain a minimum of five worksheets. To change the number of default worksheets in a workbook, use the following steps:


 * 1) Start Microsoft Excel 97.
 * 2) On the Tools menu, click Options, and click the General tab.
 * 3) Change the Sheets In New Workbook value to five, and click OK.
 * 4) Exit Microsoft Excel 97.

<div class="moreinformation_section">

MORE INFORMATION
For more information, please see the following articles here in the Microsoft Knowledge Base:

138723 XL7: Code to Access MS Excel Doesn't Work in Version 7.0

161149 Cannot Run CreatePivotTables Macro with Excel 7.0

Additional query words: 97 Pivottables create pivot table

Keywords: kbmacro kbprb KB163081

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.