Microsoft KB Archive/161149

= PRJ4: Cannot Run CreatePivotTables Macro with Excel 7.0 =

Article ID: 161149

Article Last Modified on 8/17/2005

-

APPLIES TO


 * Microsoft Project 4.0 Standard Edition
 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 95 Standard Edition

-



This article was previously published under Q161149



SYMPTOMS
The following message occurs when the Microsoft Project CreatePivotTables macro is run:

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.0 does not work with Microsoft Excel for Windows 95, version 7.0 or later. It was designed specifically for use with Excel for Windows, version 5.0.



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:

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

The macro appears in the Module Editor view.  Locate the following line: Const MS_EXCEL_APP = "Excel.Application.5" and change it to this: Const MS_EXCEL_APP = "Excel.Application"   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." </li>  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 </li> On the View menu, click Gantt Chart (or another view) to exit the Module Editor.</li> On the File menu, click Exit.

Changes to the CreatePivotTable macro are automatically saved to Global.mpt.</li></ol>

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.</li></ul>

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:

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

Additional query words: 4.00 pivot pivottables table create

Keywords: kbbug kbfix KB161149

-

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

© Microsoft Corporation. All rights reserved.