Welcome to PMConnection

     

Menu
· Home
· The Project Management Search Engine
· Exclusive Articles

Related Sites

Related Books 1
 
A Guide to the Project Management Body of Knowledge: PMBOK 5th Edition 2013

 
PMP Exam Prep: Rita's Course
 
  
  
Microsoft Project 2016 for Dummies

   
  
Microsoft Project 2016



  
Ultimate Study Guide: Foundations Microsoft Project 2013


 
The Ultimate Application Administrators Guide for Project Online

MS Project: Export MS Project to Excel, Update, Then Import Updates Back Into MS Project
Posted on Tuesday, March 10 @ 23:05:11 CDT by webadmin

PMConnection Articles



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)

clip_image002a

4. Click on Save

5. The Project Export Wizard will open, click on Next

clip_image004a


6. Choose Selected Data and click on Next

clip_image006a


7. Choose New Map. Click on Next.

clip_image008a


8. Select Tasks and ensure Export includes headers. Click on Next.

clip_image010a


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.

clip_image012a


10. Click on the dropdown in the second row and input Name.

clip_image014a


11. Continue adding the following fields:

ID
Name
Start
Finish
% Complete
Resource Names


clip_image016a

12. Click on Next

13. Click on Save Map (for future reuse)

clip_image018a


14. Name this map, then click on Save.

clip_image020a


15. Click on Finish

clip_image018[1]a

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

clip_image022a


19. Click on the dropdown for the Resource Names column, uncheck “Select All” and check Angie Krause. Click on OK.

clip_image024a


20. Now Angie sees only the tasks assigned to her.

clip_image026a

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.

clip_image028a

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”.

clip_image030a


27. Select Angies updated file in Excel and click on Open. The Import Wizard will again open. Click on Next.

clip_image032a


28. Select Use Existing Map and click on Next.

clip_image034a


29. Select the map that we saved earlier (Export to Excel) in step 14 and click on Next.

clip_image036a


30. Select Merge data into active project and click on Next.

clip_image038a


31. Ensure Task and Import includes headers is checked. Click on Next.

clip_image040a


32. All fields should already be mapped correctly.

clip_image042a


33. Click on ID and then click on Set Merge Key

clip_image044a


34. Click on Next, then click on Finish

clip_image046a


35. If we drill into the details of this project file and insert the % Complete field, we can see Angies updates have been applied.

clip_image048a


36. By examining the Tracking Gantt view, we can see the impact of Angies updates to our baseline.

clip_image050a

*A few Cautions:

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.







Note: You may find this book helpful:
Ultimate Study Guide: Foundations Microsoft Project 2013

 
Related Links
· More about PMConnection Articles
· News by webadmin


Most read story about PMConnection Articles:
300 Project Manager Interview Questions


Article Rating
Average Score: 4.43
Votes: 39


Please take a second and vote for this article:

Excellent
Very Good
Good
Regular
Bad


Options

 Printer Friendly Printer Friendly


Associated Topics

PMConnection Articles

Sorry, Comments are not available for this article.


Copyright 2005-2017 PMConnection.com. All Rights Reserved.
http://www.pmconnection.com a
PHP-Nuke Copyright © 2005 by Francisco Burzi. This is free software, and you may redistribute it under the GPL. PHP-Nuke comes with absolutely no warranty, for details, see the license.
Page Generation: 0.07 Seconds