Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel project plans data integration into Microsoft Project file

Posted on 2013-10-30
2
Medium Priority
?
1,420 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 1500 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Progress

886 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