Creating dynamic links between MS Excel and MS Project

[Sydrae]

Hello. I am a relatively new project user. I am trying to create dynamic link
between Excel and Project so our working group can update two files at the
same time. I am using a regular Gantt chart containing updated project
information from the original MS Excel source file. As part of my testing, I
tried to copy the MS Excel file in MS Project; however, the output is pasted
in the Gantt chart field instead of name, S-F,etc..

What am I doing wrong? Secondly, is it better to embed in lieu of linking?
Thanks in advance for your efforts.

 

[JackD]

Both linking and embedding are problematic with Project. They seem fine at
first, but when they break (due to changing of file names, editing the
project or spreadsheet etc.)they are difficult to troubleshoot and repair.

If you are using excel to summarize the information in Project, then I'd
work out an export map which works for you, or use some VBA to automate the
export. In essence, this would create an excel spreadsheet when you need it,
instead of trying to maintain synchronization between the two.

 

[Sydrae]

Understand some of the challenges with linking different file types. MS Excel
is the source file for select working group members managing at different
level wheras the client is requesting Gantt chart format. Candidly, I am not
a VB developer. Other options? What can I do establish a dynamic relationship
between the two files? Thanks again.

 

[Sydrae]

Thanks gentlemen. I will be working on it tonight. I have access to a VB
developer; however, she is working on another task. Currently, I am trying to
resolve the issue myself instead of requesting additional hours from my
project manager.

Thanks again,
Sydrae

 

[John]

Understand some of the challenges with linking different file types. MS Excel
is the source file for select working group members managing at different
level wheras the client is requesting Gantt chart format. Candidly, I am not
a VB developer. Other options? What can I do establish a dynamic relationship
between the two files? Thanks again.

Sydrae,
Don't say Jack didn't worn you, and I agree with him because I've been
there and done that. You have two choices, well, maybe three. First,
take your chances with links. Second, learn VBA and be glad you did
because it will be more flexible and stable. Third, contract someone to
create the VBA code for you.

For option 1, the basic process is as follows. Select the appropriate
cell of the source data in Excel and perform a Copy. Then in Project
select the receiving task row and field cell and go to, Edit/Paste
Special. Select the "Paste Link" button and hit "OK". Then, be extremely
careful, consistent and disciplined when you open, update and save.
Other than that, its a piece of cake. Oh yeah, don't rename or move the
Excel or Project file, save the source file before the destination file
for any link changes and hope for the best.

For option 2, go to the MVP website at:
http://www.mvps.org/project/links.htm
Look for the link at the bottom of the page for, "Project 98 Visual
Basic Environment Training Materials". Even though it says it is for
Project 98, it is equally applicable to all current versions of Project.
Jack also has several good examples of VBA on his website at:
http://masamiki.com/project/macros.htm

For option 3, post a query on the newsgroup,
"microsoft.public.project.developer" saying you need someone to develop
some VBA code for you.

Hope this helps.
John
Project MVP

 

[Sydrae]

Thanks again for yours and Jack's help. Until I obtain permission from my
project manager, I will try to create dynamic links vis a vis MS Excel to MS
Project or visa versa. Of course at night, I will review documentation
contained in Option 2. I may solicit inputs pertaining to Option 3. The
dilemma is we are on a short suspense. One of many issues is that we
developed the MS Project file around an existing MS Excel file where some
fields and their titles or formats were different from source to destination.

With that said, one of the client's POC will need to determine if they want
to link from MS Excel to MS Project or visa versa. It appears to me not
withstanding the maintaining links, etc, that I would recommend using MS
Excel as the source file and create additional text fields. I will keep
working at it. Other thoughts?

 

[John]

Thanks again for yours and Jack's help. Until I obtain permission from my
project manager, I will try to create dynamic links vis a vis MS Excel to MS
Project or visa versa. Of course at night, I will review documentation
contained in Option 2. I may solicit inputs pertaining to Option 3. The
dilemma is we are on a short suspense. One of many issues is that we
developed the MS Project file around an existing MS Excel file where some
fields and their titles or formats were different from source to destination.

With that said, one of the client's POC will need to determine if they want
to link from MS Excel to MS Project or visa versa. It appears to me not
withstanding the maintaining links, etc, that I would recommend using MS
Excel as the source file and create additional text fields. I will keep
working at it. Other thoughts?

Sydrae,
Just for you reference, before Project 98, there were no provisions for
linking tasks between independent project files other than with Paste
Links. I worked on a program at our company wherein we had 70+
independent files that were linked using Paste Links between schedule
dates. Using that scheme, we effectively were able to create what is now
known as a dynamically consolidated master. As mentioned by both Jack
and myself in earlier posts, Paste Links are very fragile and I learned
a lot of discipline in working with them on that master file for a few
years. However, at the time that method was the only choice available.
With current versions of Project there are much better, (more efficient
and more stable), ways of exporting or otherwise tying data between
files whether it is between Project files or between Project and some
other application (e.g. Excel, Access, etc.).

I think the reason some people develop schedules in Excel initially is
that Excel is inherently more user friendly and most people are familiar
and comfortable with it. However, if the end goal is to create a
schedule for managing a project, Excel is not the application to use.
Project is the Microsoft offering best suited for this element of
project management. The first step then involves translating the initial
schedule information from Excel to Project. Unfortunately, Project is
not a quick learn application but once the fundamentals are understood,
it will provide great help in managing cost and schedule for a program.
If more detailed financial accounting or specialized reporting is
required, Project is limited and it is often desirable then to export
some of Project's data to a more appropriate application such as Excel.

Those are my thoughts.
John
Project MVP