How to import Microsoft Project files into Microsoft Excel
Have you ever managed a larger project with several subprojects, hundreds of tasks and milestones, dozens of members in the project team and even more other staff contributing to the project, spread all over almost every department of the company? If so, you might have used Microsoft Project to plan and monitor the project.
Since there were so many different people contributing, you may have encountered the following challenge: You had to distribute the actual project plan to all team members at least once a week, but not everyone had Microsoft Project installed on his computer. So you couldn’t simply send the Microsoft Project file.
Sounds familiar? What do you usually do?
You copy the project plan as a picture and paste it into a PowerPoint file? I don’t think so. If it is a really big project, I bet you tried this only once.
You create a PDF? Well, this looks better, but still needs some PDF-creating software installed on your computer. Even if you have a PDF-creator, PDFs are quite inflexible.
This is still a workaround, but wouldn’t it be better to provide a copy of the project plan in Microsoft Excel? Usually everyone has Excel installed on his computer. And the team members would be able to copy and paste tasks and other data, to do their own side calculations, to filter the project plan, to define the print area and so forth.
We need a little tool for importing Microsoft Project Files into Excel with ease.
Of course Microsoft Project provides a built-in function to export data from a MPP-file to Microsoft Excel by clicking “Save As” and selecting “Microsoft Excel Workbook”.
The result usually looks like this:
Compelling? Not really. A starting point, but it would still be a long tramp to transfer this into something useful like this one:
Project plan example taken from Microsoft’s Office Online Website (project management plan)
Looks better, doesn’t it? And yes, this is Excel.
What if you could produce this with only a couple of clicks?
Well, here is a way to do this:
The used Excel functions and features:
-
A few named ranges
-
Conditional formatting to highlight the first three PSP-levels in different shades of grey
-
MIN function to calculated the project start date
-
ROUNDDOWN function to round start and end dates to full days
(used in the hidden columns L and M) -
Conditional formatting to create the pseudo Gantt chart
(black fill color for tasks, orange fill color for milestones)
Advanced Excel features, but no rocket science.
Additionally there is – of course – some VBA code managing the automatic import from Microsoft Project. But even this is only 90 lines of code, including a nice progress indicator I “borrowed” from John Walkenbach's brilliant website. Thanks John!
The VBA code (started when clicking on the worksheet button) is not too complicated. Here is what it does:
-
Let the user select the project file to import (in a standard open-file-dialog)
-
Delete all exiting data in the Excel file
-
Open the Microsoft Project application (if installed)
-
Loop through all the tasks of the Project file and write the defined information to the Excel worksheet
-
Close the Microsoft Project application
For anyone who is interested in the code in detail: the workbook provided for download is without password protection. If you want to, go and figure it out.
A short summary
The advantages
-
Import Microsoft Project files into Excel with a couple of mouse clicks
-
Display your complete project plan in a professional Excel template without any further effort on formatting, etc.
-
Provide all your project team members with the actual project plan in Excel and enable them to do more than just print
The shortfalls
-
The tool uses a hardcoded structure of imported project columns (WBS, task name, duration, start, end, predecessor, successor, resource initials). If you want to change this, you will have to change the VBA code and – in case you need additional columns – also the structure of the workbook
-
It is a one-way street: if anyone changes the project plan in Excel, you would have to manually update the Microsoft Project file
-
The file for download is restricted to 150 tasks and 200 days. You can easily change that by inserting the number of required rows and columns somewhere in the middle of the existing table and copying down and right the formulas and formats from above and left
The tool for free download:
Download Import MPP into XLS (Excel 97 – 2003, 152.5K)
The tool has been tested with Excel 2003/2007 and Microsoft Project 2003. You need to have Microsoft Project installed to run the macro.
Leave a Reply to Trevor Cancel reply