Solved

Excel project plans data integration into Microsoft Project file

Posted on 2013-10-30
2
1,286 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:
Dr. Thomas Henkelmann earned 500 total points
ID: 39614369
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
ID: 39614524
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Learn how ViaSat reduced average response times for IT incidents from 10 minutes to 30 seconds.
When you’re making plans to join the modern business race, you should analyze various details that may affect your results. Nowadays, millions of businesses are trying to grow into established and appreciated professional enterprises.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

685 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