Software

Create PowerPoint status reports from your Project file

Project status reviews are a necessary evil. Here's a macro to pull them into a PowerPoint presentation so that you can keep meeting attendees informed and engaged.


Many project managers don’t like detailed project status reviews, but they’re a fact of life—and often a good exercise in status communication and problem solving. Some favor pulling up the Microsoft Project file in the status meeting and walking through the plan, but I prefer using PowerPoint.

Over the years, I’ve learned that pulling up an MSP plan in front of people who are not MSP users can be distracting, to say the least. They see all the Gantt bars, link lines, etc., and they shut down. Getting the basic information into PowerPoint lets you offer the data necessary to start the discussion while shielding the group from too many details.

In this article, I’ll give you a VBA macro to run from inside PowerPoint that will let you create a status presentation from any MSP mpp file. I’ll also show you a cool utility macro that lets you easily use the Windows Common Open dialog in your VBA macros.

What the macro does for you
This macro opens the Windows Common Open dialog, which typically requires you to make several fairly complex Windows API calls. Unless you’re a Windows programmer, this can be kind of hard. I’m not a programmer (I’m just a guy who knows a little VBA), so I really like the utility macro that I’m also including that makes calling this dialog a snap.

Once you pick an mpp file and click OK, you’ll see an Input dialog box that asks you to enter either All, Summary, or Marked. This entry will control which tasks the macro will import from the mpp file. All will bring in every task, Summary brings in only summary tasks, and Marked brings in only tasks that have the Flag1 field set to True. After you make an entry and click OK, the macro will create a new slide for each task that is imported. It will title the slide with the task name and then enter the Start and Finish Dates and the Work Value.

If the task has started but not finished, the macro will insert the Actual Start date, the Actual Work, and the Percent Complete. If the task has finished, it will also include the Actual Finish Date. Regardless of the project’s status, the macro brings over the contents of the Notes field. Figure A shows a sample slide from the PowerPoint presentation created by this macro. The slide is first created in plain text, but then you can apply any of PowerPoint’s built-in design templates. The figure shown is in the Beam template.

Figure A
A formatted sample of the macro’s output


Many project managers don’t like detailed project status reviews, but they’re a fact of life—and often a good exercise in status communication and problem solving. Some favor pulling up the Microsoft Project file in the status meeting and walking through the plan, but I prefer using PowerPoint.

Over the years, I’ve learned that pulling up an MSP plan in front of people who are not MSP users can be distracting, to say the least. They see all the Gantt bars, link lines, etc., and they shut down. Getting the basic information into PowerPoint lets you offer the data necessary to start the discussion while shielding the group from too many details.

In this article, I’ll give you a VBA macro to run from inside PowerPoint that will let you create a status presentation from any MSP mpp file. I’ll also show you a cool utility macro that lets you easily use the Windows Common Open dialog in your VBA macros.

What the macro does for you
This macro opens the Windows Common Open dialog, which typically requires you to make several fairly complex Windows API calls. Unless you’re a Windows programmer, this can be kind of hard. I’m not a programmer (I’m just a guy who knows a little VBA), so I really like the utility macro that I’m also including that makes calling this dialog a snap.

Once you pick an mpp file and click OK, you’ll see an Input dialog box that asks you to enter either All, Summary, or Marked. This entry will control which tasks the macro will import from the mpp file. All will bring in every task, Summary brings in only summary tasks, and Marked brings in only tasks that have the Flag1 field set to True. After you make an entry and click OK, the macro will create a new slide for each task that is imported. It will title the slide with the task name and then enter the Start and Finish Dates and the Work Value.

If the task has started but not finished, the macro will insert the Actual Start date, the Actual Work, and the Percent Complete. If the task has finished, it will also include the Actual Finish Date. Regardless of the project’s status, the macro brings over the contents of the Notes field. Figure A shows a sample slide from the PowerPoint presentation created by this macro. The slide is first created in plain text, but then you can apply any of PowerPoint’s built-in design templates. The figure shown is in the Beam template.

Figure A
A formatted sample of the macro’s output


The guts of the macro
Let’s look at the code that builds this presentation. It runs from inside PowerPoint and starts with this line of code. It’s setting the strProjPath string variable to equal the result returned by the dhFileDialog function. This is a special function that calls the Windows Common Open dialog to allow you to easily pick an mpp file from which to create the presentation. This is great because it’s familiar; you don’t have to learn a new interface or create some cheesy dialog for other users to type their file path into. (Note: This macro is taken, with permission, from the 1997 Sybex book The VBA Developers Handbook by Ken Getz and Mike Gilbert. It’s a great reference for using VBA to build Office-based solutions.)

The dhFileDialog function has many parameters, but you’ll only use a few. The strInitDir sets up which directory the Common Open dialog should open with. strFilter tells the dialog which file type to look for. Here, it’s “*.mpp” & vbNullChar & *.mpp”, which tells the dialog to look for mpp files and only show mpp files. The strDialogTitle argument allows you to specify the title of the Common Open dialog that you’ll see. Figure B shows the dialog that is called by the above call.

Figure B
File selection dialog


When you click Open, you’ll be shown the input box shown in Figure C.

Figure C
Specify which tasks you want to import.


Here, you enter Summary (the default), Marked, or All. The entry can be made in any case; the macro will convert whatever is entered into lowercase. This input box is called by the SetImport function, which calls the input box, converts the entered value, and then returns a number (1, 2, or 3) that determines which of the tasks in the specified mpp file will be imported into the presentation. 1 means import every task, 2 means import only summary tasks, and 3 means import only marked tasks.

Next, based on which tasks should be imported, the macro looks at each task, decides whether it meets the import criteria (All, Summary, or Marked), and then looks at whether the task is unstarted, started, or completed. It uses this to decide whether it should import only the Name, Start, Finish, Work, and Notes or whether it should also bring in the Actual Start, Actual Work, and % Complete (in the case of a started task) or Actual Start and Actual Finish (in the case of a completed task).

When the import of tasks is finished, the macro comes to these lines.

The blnFound variable is only set to True if at least one task met the import criteria. If it is true, you’ll insert a title slide at the beginning of the presentation. You do this at the end of the macro because if you try to do it at the start, there is a title slide even if no tasks were imported, and you don’t want that.

The guts of the macro
Let’s look at the code that builds this presentation. It runs from inside PowerPoint and starts with this line of code. It’s setting the strProjPath string variable to equal the result returned by the dhFileDialog function. This is a special function that calls the Windows Common Open dialog to allow you to easily pick an mpp file from which to create the presentation. This is great because it’s familiar; you don’t have to learn a new interface or create some cheesy dialog for other users to type their file path into. (Note: This macro is taken, with permission, from the 1997 Sybex book The VBA Developers Handbook by Ken Getz and Mike Gilbert. It’s a great reference for using VBA to build Office-based solutions.)

The dhFileDialog function has many parameters, but you’ll only use a few. The strInitDir sets up which directory the Common Open dialog should open with. strFilter tells the dialog which file type to look for. Here, it’s “*.mpp” & vbNullChar & *.mpp”, which tells the dialog to look for mpp files and only show mpp files. The strDialogTitle argument allows you to specify the title of the Common Open dialog that you’ll see. Figure B shows the dialog that is called by the above call.

Figure B
File selection dialog


When you click Open, you’ll be shown the input box shown in Figure C.

Figure C
Specify which tasks you want to import.


Here, you enter Summary (the default), Marked, or All. The entry can be made in any case; the macro will convert whatever is entered into lowercase. This input box is called by the SetImport function, which calls the input box, converts the entered value, and then returns a number (1, 2, or 3) that determines which of the tasks in the specified mpp file will be imported into the presentation. 1 means import every task, 2 means import only summary tasks, and 3 means import only marked tasks.

Next, based on which tasks should be imported, the macro looks at each task, decides whether it meets the import criteria (All, Summary, or Marked), and then looks at whether the task is unstarted, started, or completed. It uses this to decide whether it should import only the Name, Start, Finish, Work, and Notes or whether it should also bring in the Actual Start, Actual Work, and % Complete (in the case of a started task) or Actual Start and Actual Finish (in the case of a completed task).

When the import of tasks is finished, the macro comes to these lines.

The blnFound variable is only set to True if at least one task met the import criteria. If it is true, you’ll insert a title slide at the beginning of the presentation. You do this at the end of the macro because if you try to do it at the start, there is a title slide even if no tasks were imported, and you don’t want that.

Installing the macro
Installation is pretty easy. From inside PowerPoint, press [Alt][F11] to open the VBA editor. On the left side, you’ll see something like the object in Figure D.

Figure D
Select VBAProject from this screen.


Right-click on the VBAProject line and select Insert | Module from the menu. Your screen will look like Figure E.

Figure E
Here, you’ll add the code.


In the lower pane (see Figure E), click on Module1 and enter CommonOpen. Double-click on CommonOpen in the tree view and paste in the contents of Listing A.

As mentioned previously, the code in Listing A makes the Common Open easier to use, and without it, you would have to make Windows API calls to open the Common Open dialog. Repeat the above procedures to create the second module, called ProjectImport, into which you’ll paste the code in Listing B.

With one of the modules opened in the code window, click Tools | References. Here, you’ll add references to the Object libraries that you’ll need to make the code in these macros work properly. You’ll add references to the Microsoft Project 9.0 or 10.0 library, depending on your version. You’ll also add one to the Microsoft Visual Basic for Applications Extensibility library. To do this, scroll down the list until you find the one you need and check the box to its left. When you’re finished, you should have a dialog box that looks like the one shown in Figure F.

Figure F
VBA References dialog box


Visual Basic for Applications, Microsoft PowerPoint 10.0 (or 9.0, if you have Office 2000), OLE Automation, and the Microsoft Office 10.0 (or 9.0) Object Library should already be checked when you open the dialog box.

Remember to add these references. If you don’t, the macros won’t work and you’ll get the error message shown in Figure G.

Figure G


If you see this error message, refer to the directions above on adding references.

How do you present your project status?
Do you have slick ways to get the message across when it comes to communicating project status? Share your tips and tricks. Send us an e-mail with your ideas or post a comment below.

 

Installing the macro
Installation is pretty easy. From inside PowerPoint, press [Alt][F11] to open the VBA editor. On the left side, you’ll see something like the object in Figure D.

Figure D
Select VBAProject from this screen.


Right-click on the VBAProject line and select Insert | Module from the menu. Your screen will look like Figure E.

Figure E
Here, you’ll add the code.


In the lower pane (see Figure E), click on Module1 and enter CommonOpen. Double-click on CommonOpen in the tree view and paste in the contents of Listing A.

As mentioned previously, the code in Listing A makes the Common Open easier to use, and without it, you would have to make Windows API calls to open the Common Open dialog. Repeat the above procedures to create the second module, called ProjectImport, into which you’ll paste the code in Listing B.

With one of the modules opened in the code window, click Tools | References. Here, you’ll add references to the Object libraries that you’ll need to make the code in these macros work properly. You’ll add references to the Microsoft Project 9.0 or 10.0 library, depending on your version. You’ll also add one to the Microsoft Visual Basic for Applications Extensibility library. To do this, scroll down the list until you find the one you need and check the box to its left. When you’re finished, you should have a dialog box that looks like the one shown in Figure F.

Figure F
VBA References dialog box


Visual Basic for Applications, Microsoft PowerPoint 10.0 (or 9.0, if you have Office 2000), OLE Automation, and the Microsoft Office 10.0 (or 9.0) Object Library should already be checked when you open the dialog box.

Remember to add these references. If you don’t, the macros won’t work and you’ll get the error message shown in Figure G.

Figure G


If you see this error message, refer to the directions above on adding references.

How do you present your project status?
Do you have slick ways to get the message across when it comes to communicating project status? Share your tips and tricks. Send us an e-mail with your ideas or post a comment below.

 

Editor's Picks

Free Newsletters, In your Inbox