Solved

Excel project plans data integration into Microsoft Project file

Posted on 2013-10-30
2
1,226 Views
Last Modified: 2013-11-14
Hi !

I have excel files which currently are set up to be project plans by themselves. How can I integrate this excel data into a single Microsoft Project file (*.mpp). Ideally it would be able to sync itself back to the excel files but I'm not dreaming...

Please note the following :
-I cannot use the paste link method since I often add / remove tasks, etc. in the excel files and so I have a dynamic range, not a static one.
-I'm fine with importing the data each time the excel files change but I need to know how to do it, and I also have the following data to import :
Task Name | Resource Name | Predecessors | Start Date | Actual Work | Remaining Work | Actual Duration | Remaining Duration | Finish Date
The excel data includes tasks summary which I am able to determine directly in Excel by having a column that would act as the Outline Level (ie. 1, 2, 3, etc.) and so this needs to import fine as well.

How can I achieve the above?

Thanks
0
Comment
Question by:STIWasabi
2 Comments
 
LVL 12

Accepted Solution

by:
thausla earned 500 total points
Comment Utility
Hi,

you directly import and export from Microsoft Project to/from Excel. This can be done by File..Save & Send..Other file types/Microsoft Excel WorkBook. Save as Excel from MSP
After clicking on "Save as" you can enter the file name of the Excel file. Now the interesting part starts: as wizard takes you through the setup of the export schema (which fields you want to export). The essential piece is that you can use the exact schema again for importing from Excel. Excel export wizard 1 Excel export 2 Excel export 3
Now you can define which data you want to export Excel export 4. For the data you describe above, "Tasks" is enough". Finally you can select the fields to be exported. Please make sure you also add "Unique ID" (this will be used later during import as "key" to find existing tasks) and "Outline Level" to later identify the summary tasks.Excel export 5
Hope this helps

Thomas
0
 

Author Comment

by:STIWasabi
Comment Utility
I did an export to look at what the excel files should look like. However, since multiple excel files are to be added within the single project file, I have no way of determining unique #s (remember the source of data is Excel not Project). Also, I'm stuck with the excel templates and so these were not created from an export of Microsoft Project. So to me that means the predecessors is not "importable" and that I have several tweaks to do before being able to import the whole thing.

I'll play some more with this idea but in the meantime can you show me how to import from excel, which is the main idea here (also if the process could be automated a bit more ie. saved map, VBA code, etc.)

Thanks
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
It was Monday morning and while heading to work those familiar feelings of frustration began to rise: How was I ever going to get my yard work done?! At the end of every weekend I discovered that I spent more time trying to decide what to work on th…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now