Integrating Microsoft Project and Microsoft Excel

[KT]

Hi,

I am trying to find out the option of linking Microsoft Project and Excel. I
created some activities and milestone in the project. Let say one of the
milestone got pushed further to a new date and I want this change to get
updated to a field in excel where additional calculations are performed. Can
I achieve this process of updating fields? If so, can somebody tell me the
way to do it? Thanks

KT

 

[John]

Hi,

I am trying to find out the option of linking Microsoft Project and Excel. I
created some activities and milestone in the project. Let say one of the
milestone got pushed further to a new date and I want this change to get
updated to a field in excel where additional calculations are performed. Can
I achieve this process of updating fields? If so, can somebody tell me the
way to do it? Thanks

KT

KT,
There are at least three ways to "link" the two applications and there
may be more.

One way is to use Paste Links between the relevant data points. Let's
say you want to link the Finish date of a Project milestone to a cell on
an Excel Worksheet. First select the Finish date in Project and do a
"copy". Then select the cell in Excel and go to Edit/Paste Special.
Select "Paste Link" as "Text" and hit "OK". The main problem with this
method is that it is prone to corruption if you are not very careful.
For example, do not change the location of either file. I have also
found that any time an update is made, save the source (Project) and
then the destination file (Excel). You must also use great care when
breaking links or one file may be left with a "dangling" link fragment.

A second method for updating Excel is to use an export map in Project to
save selected elements of the Project data to an Excel Worksheet. To
find out more about this approach, look in the Project help file under
"export".

A third method that provides great flexibility and is not subject to
corruption is to use VBA to export data from Project to Excel. With VBA
you can do whatever you want but it does require some VBA knowledge.

Hope this helps.
John
Project MVP

 

[KT]

Thanks!! John

KT,
There are at least three ways to "link" the two applications and there
may be more.

One way is to use Paste Links between the relevant data points. Let's
say you want to link the Finish date of a Project milestone to a cell on
an Excel Worksheet. First select the Finish date in Project and do a
"copy". Then select the cell in Excel and go to Edit/Paste Special.
Select "Paste Link" as "Text" and hit "OK". The main problem with this
method is that it is prone to corruption if you are not very careful.
For example, do not change the location of either file. I have also
found that any time an update is made, save the source (Project) and
then the destination file (Excel). You must also use great care when
breaking links or one file may be left with a "dangling" link fragment.

A second method for updating Excel is to use an export map in Project to
save selected elements of the Project data to an Excel Worksheet. To
find out more about this approach, look in the Project help file under
"export".

A third method that provides great flexibility and is not subject to
corruption is to use VBA to export data from Project to Excel. With VBA
you can do whatever you want but it does require some VBA knowledge.

Hope this helps.
John
Project MVP