Importing Data from Excel into a master project in MS Project 07

[ARBrock]

Good afternoon,

The backbone of my team has been an excel spreadsheet that contains a LARGE
amount of data about all of our projects. To help oversee all the projects, I
have consolidated them into a Master Project (I have anywhere from 30-50
projects at any give time). We are a residential remodel company -- the data
in the excel spreadsheet is data about our subcontractors (who is going
where, when, and what they are doing).

I want to minimize the impact to the team member who is managing the
scheduling of the subcontractors and allow her to continue to use this
spreadsheet -- even though we have implemented MS project.

I created an import map to map specific fields into our master project file.
The import works beautifully... where I am stuck now is trying to figure out
how to get the imported data to be associated with the projects they are a
part of. For purposes of clarification, I am importing the data as TASKS that
have resources assigned. I have a custom field Text3 that I have listed as
Job Nickname... the reason for this is that my Project Name field is the
ENTIRE project name ... in the excel spreadsheet, my team member uses a
shorthand name.

Any help on getting the imported data to update into the projects they are
associated with would be helpful.

Thanks!

-AR

 

[Jim Aksel]

There's probably an easier solution, but this is what comes to mind at this
late hour.

Each line in a project file has a UniqueID field. It is an integer and
guaranteed never to change once assigned to a task. Might you construct a
macro to match that UniqueID field to a corresponding field also in the Excel
file? You would need to add that UniqueID field to the Excel file. You will
have a problem if the Project.mpp is actually a Master Project with inserted
..mpp files. If it is a stand alone Project.mpp file this technique might
work for you.
--
If this post was helpful, please consider rating it.

Jim

Visit http://project.mvps.org/ for FAQs and more information
about Microsoft Project

 

[ahelp]

If you're comfortable with VBA (I'm not!), you might find this helpful:
http://msdn2.microsoft.com/en-us/library/bb738433.aspx (and you thought Jim's
answer was complicated...).

 

[ARBrock]

Hmmm...

Actually, yes... the master project is a compilation of many sub projects
.... I set it up that way because we have projects starting and ending
weekly... it made the most sense to do it that way...

Any other suggestions?

-AR

 

[ARBrock]

I'm getting comfortable with VBA...so thanks for the link... it seems a bit
overwhelming right now. I'm reading Rod Gill's book (its excellent) and
trying to wrap my mind around it all.

I'm going to find a solution... I'm going to find a solution...

 

[ahelp]

I just started Ron's book, so maybe I'll get comfortable yet...