How do I link data between Project and Excel?

T

Todd Ellis, PMP

Sounds like a simple question, but...

I'm working on a very large-scale program and my client has a need to export
data from Project 2003 Pro to specific workbooks. It's very easy to export
specific cells into a workbook using the Export Wizard; no problem there.

What I want to do is embed Project data into Excel cells that will "change"
when parameters change in Project. For example, if a specific date for one
task is changed, I want that one date to change in the Excel spreadsheet,
too. I expect this would be done through a macro, but at the same time,
there's a lot of data...2,800 tasks and about 30 workbooks. So, running one
macro probably won't cut it...I'm going to have to export between 7 and 100
lines to a particular workbook. What I'd like to see is the same linking
functionality as one would see between Excel workbooks - lookups to other
workbooks that automatically update cells.

I have looked at OLE, but don't see a match for my needs, unless I'm not
understanding something.

Am I better off just running export maps individually to workbooks on
request? That solution seems to dictate a lot of administrative work (in
this prgram's case an hour or more each time, and that might be a daily
event) to update results. There's GOT to be a simpler way, or perhaps I'm
hitting the functionality limits of the program?

I'm scouring books on Project '03 and not seeing a good solution. Any ideas?
 
J

JackD

Sounds like a simple question, but...

I'm working on a very large-scale program and my client has a need to export
data from Project 2003 Pro to specific workbooks. It's very easy to export
specific cells into a workbook using the Export Wizard; no problem there.

What I want to do is embed Project data into Excel cells that will "change"
when parameters change in Project.
Click to expand...

You CAN do this by selecting the cell in project. Hit control+C to copy,
then go to excel and paste special and choose "paste link". But for a large
scale project you really don't WANT to do this.
For example, if a specific date for one
task is changed, I want that one date to change in the Excel spreadsheet,
too. I expect this would be done through a macro, but at the same time,
there's a lot of data...2,800 tasks and about 30 workbooks. So, running one
macro probably won't cut it...I'm going to have to export between 7 and 100
lines to a particular workbook.
Click to expand...

The beauty of a macro is that it can read an unlimited number of project
files and spit the data out where ever you like. A macro may be the best way
to do this.
What I'd like to see is the same linking
functionality as one would see between Excel workbooks - lookups to other
workbooks that automatically update cells.
Click to expand...

Really, you DON'T want to rely on this functionality.

What do these workbooks actually do with the information? That is the second
question you need to answer. The first is why do you want to do this?
I have looked at OLE, but don't see a match for my needs, unless I'm not
understanding something.

Am I better off just running export maps individually to workbooks on
request? That solution seems to dictate a lot of administrative work (in
this prgram's case an hour or more each time, and that might be a daily
event) to update results. There's GOT to be a simpler way, or perhaps I'm
hitting the functionality limits of the program?
Click to expand...

Storing the projects on a central database and having excel query that
database can be a simple solution. Writing a macro to push the data to excel
can be another solution. Either of these once set up can require little or
no administrative time.
I'm scouring books on Project '03 and not seeing a good solution. Any
Click to expand...
ideas?

Have you looked at project server? It may meet your needs. At this point you
haven't given enough information about what the purpose of this is for
anyone to get more specific without having to speculate. Tell us what and
why and we can give some suggestions on how.

-Jack
 
T

Todd Ellis, PMP

Jack: Great comments.

What is going on - the program involves four specific stages that involve a
series of product lines that provide a specific functionality to the client's
telecom customers. Each stage increases the product line's capabilities to a
specific level (ie. 100, 200, 300, 400 files). All files will be on a shared
network. (the program is about $200M in value and will take 2+ years to
complete - I'm doing the WBS). Project Pro '03 and Excel (for the workbooks)
were selected by the client based on past and similar experience. We're at
2,800 lines in MS Project, 30 workbooks, and growing.

The client is planning on depending on contractors to perform 90% of the
work. They do not feel comfortable with the contractors reporting in via MS
Project or Outlook, but can see them using Excel without a problem. They
modify dates and then send them in as "reports". The in-house administrator
takes the info, adjusts the work done/left elements, and then "pushes" the
new info from Project to the Excel workbooks.

Also, the workbooks have a bunch of other detailed information embedded,
including end customer information, locations, methodologies, deeper WBS, and
so forth. Thus the workbooks are absolutely necessary, and will also contain
the dates and milestones I'm working on within Project.

I'd also like to get it "right" this time around as I have not only re-built
the MS Project file three times to meet needs, but also I may end up being
the guy these contractors report to...

It is sounding liek a macro is the way to go. I was hoping not to have to
delve into that, but I may need to! Thanks...

Todd
 
Top