PRJ: Cannot Run CreatePivotTables Macro with Excel 97 (163081)

WORKAROUND

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

1. Start Microsoft Project.
2. On the Tools menu, click Macros.
3. In the Macro Name list, select CreatePivotTables, and click Edit.
   
   The macro is displayed in the Module Editor window.
4. Locate the following line of code:

   Const MS_EXCEL_APP = "Excel.Application.5"
   						

   and change it to this:

   Const MS_EXCEL_APP = "Excel.Application"
   					

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

6. On the View menu, click Gantt Chart (or another view) to exit the Module Editor.
7. On the File menu, click Exit.
   
   The changes to the CreatePivotTables macro are automatically saved to Global.mpt.

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.