PRJ4: Cannot Run CreatePivotTables Macro with Excel 7.0

WORKAROUND

Microsoft Excel 7.0

• 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

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

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