PRJ4: Cannot Run CreatePivotTables Macro with Excel 7.0 (161149)

WORKAROUND

Microsoft Excel 7.0

To correct this problem, do one of the following:

• Method 1. Upgrade to Microsoft Project 4.1 for Windows 95.
  
  
• Method 2. Modify the CreatePivotTables Macro, using the following steps:
  
  
  1. Start Microsoft Project.
  2. On the Tools menu, click Macros. In the Macro Names list select CreatePivotTables, and click Edit.
     
     The macro appears in the Module Editor view.
  3. Locate the following line:

     Const MS_EXCEL_APP = "Excel.Application.5"
     								

     and change it to this:

     Const MS_EXCEL_APP = "Excel.Application"
     							

  4. Locate the following block of code:

           api% = FindWindow(MS_EXCEL_CLASSNAME, lpNULL)
           If api% = 0 Then     ' FindWindow returns a non-zero ...
              MsgBox MB_RUNEXCEL, vbExclamation + vbOK, MB_TITLE
              End
           End If
     								

     and remark it out by typing an apostrophe character (') at the beginning of each line, like this:

            'api% = FindWindow(MS_EXCEL_CLASSNAME, lpNULL)
            'If api% = 0 Then     ' FindWindow returns a non-zero ...
            '  MsgBox MB_RUNEXCEL, vbExclamation + vbOK, MB_TITLE
            '  End
            'End If
     								

     NOTE: The complete comment on the second line is "FindWindow returns a non-zero value if it finds the window."
  5. Locate the following line:

           Set oExcel = GetObject(,MS_EXCEL_APP)
     								

     and replace it with the following two lines of code:

            Set oExcel = CreateObject(MS_EXCEL_APP)
            oExcel.Visible = True
     							

  6. On the View menu, click Gantt Chart (or another view) to exit the Module Editor.
  7. On the File menu, click Exit.
     Changes to the CreatePivotTable macro are automatically saved to Global.mpt.
  The modified CreatePivotTable macro always starts a new instance of Excel, even if Excel is already running. If you run the macro again, you may want to close the previous instance of Excel to avoid having multiple instances of Excel open.

Microsoft Excel 97

To allow Microsoft Project 4.0 to work with Microsoft Excel 97, modify the macro using the steps in Method 2 in the "Microsoft Excel 7.0" section and then make the following additional code changes:

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 to read as shown below:

   ' Clean up and exit.
       oExcel.Visible = True
       oXLAssign.Select
       oExcel.ScreenUpdating = True
       AppActivate oExcel.Caption
       oExcel.WindowState = MAXIMIZED
       Set oExcel = Nothing
       Exit Sub
				

These changes allow the CareatePiviotTable macro to interact with Microsoft Excel 97.

For more information, please see the following article in the Microsoft Knowledge Base: