Export MS Project to Excel, Update, Then Import Updates Back Into MS Project

 
Project Management: A Systems Approach to Planning, Scheduling, and Controlling
  
 
Ultimate Study Guide: Foundations Microsoft Project 2010 (Exam 70-178)

 
PMP Exam Prep: Rita's Course

  
  
PgMP: Program Management Professional Exam Guide

 
Implementing and Administering Microsoft Project Server 2010

MS Project: Export MS Project to Excel, Update, Then Import Updates Back Into MS Project
Posted on Wednesday, March 11 @ 00:05:11 CDT by webadmin
1. Open the project in MS Project
2. Click on >File, >Save As. Choose the location where you would like this file to be saved.
3. Click on the dropdown for Save as type and select Microsoft Excel Workbook (*.xls)


5. The Project Export Wizard will open, click on Next
6. Choose Selected Data and click on Next
7. Choose New Map. Click on Next.
8. Select Tasks and ensure Export includes headers. Click on Next.
9. The Task Mapping dialogue box will appear. Click on the dropdown in the first row under “From: Microsoft Office Project field” and select ID.
10. Click on the dropdown in the second row and input Name.
11. Continue adding the following fields:
ID
Name
Start
Finish
% Complete
Resource Names

13. Click on Save Map (for future reuse)
14. Name this map, then click on Save.


16. Let’s say I am the Project Manager for this project and I would like to get updates from my team on their progress. I could attach this Excel file to an email and send it to the team members.
17. Let’s say that the date is 11/07/08 and Angie Krause (a team member) would like to update the progress on her tasks.
18. Open the file in Excel. Click on >Data, >Filter
19. Click on the dropdown for the Resource Names column, uncheck “Select All” and check Angie Krause. Click on OK.
20. Now Angie sees only the tasks assigned to her.

21. The next step is to update the Start date, the Finish date and the % Complete of these tasks. The objective is to bring this information in line with reality.
22. The first three tasks started and completed as expected, so Angie input 100% on those tasks.
23. The fourth task (Task ID 10: Identify budget requirements) is only 50% Complete and instead of completing on 11/07/08, Angie updates the Finish date to be Tuesday 11/11/08.

24. Angie then saves and closes this file and sends it back to the project manager.
25. To incorporate Angies updates back into the project, we must first open the project in MS Project.
26. Within MS Project, click on Open and browse to where Angies updated Microsoft Excel file has been saved. You will need to click on the dropdown in the bottom right to see “All files”.
27. Select Angies updated file in Excel and click on Open. The Import Wizard will again open. Click on Next.
28. Select Use Existing Map and click on Next.
29. Select the map that we saved earlier (Export to Excel) in step 14 and click on Next.
30. Select Merge data into active project and click on Next.
31. Ensure Task and Import includes headers is checked. Click on Next.
32. All fields should already be mapped correctly.
33. Click on ID and then click on Set Merge Key
34. Click on Next, then click on Finish
35. If we drill into the details of this project file and insert the % Complete field, we can see Angies updates have been applied.
36. By examining the Tracking Gantt view, we can see the impact of Angies updates to our baseline.


1. This approach forces Microsoft Project to impose a Finish No Earlier than constraint on any task that is not 100% Complete. As a work-around, the project manager could modify the Excel file prior to distributing so only current period tasks are included.
2. Because we are exporting Task level information, this approach only works with one resource assigned to each task. If you have multiple resources assigned to tasks, you would want to work with the Assignments table on the Export map instead of the Task table.
3. You may want to password protect all columns within the Excel document except Start, Finish and % Complete to keep these fields from being altered.
4. Do NOT add new tasks to your project until after importing the updates. Remember that alignment between the Excel and the MS Project file data is established by the ID column. Inserting a new task will create a new ID thus causing your files to be out of alignment.

Social Bookmark this page

RSS Feed for Exclusive Articles

Average Score: 4.38
Votes: 31

Associated Topics

Sorry, Comments are not available for this article.

View the original article here