Paste Link Not Working between Project Proff 2010 and Excel 2010

Question

Project 2010 to Excel 2010

If I link data from project to Excel using Paste special - Paste Link the links are broken as soon as I close Project.

eg In Project: I copy the Start and Finish date for task 1

In Excel I paste Special-Paste Link-html(or Text I have tried both)

If I save and close the Excel file then re-open the links update

If I save and Close the Project file and the excel file then re-open them the link will not update and I get an error in the cell

I have been told that Paste Links are flakey and easily corrupted but this process worked perfectly between Project 2003 and Excel 2003.

I have a Spreadsheet which contains 200+ links so you can see why I am frustrated that this does not work any more.

I have even tried creating a brand new spreadsheet this does not work either.

Any help would be appreciated

Answer 1

Hi Rod,

I have set up your suggested soultion and at first all seem great but.....

I recently opened up my master plan and copied the data using a simple macro to filter and sort then copy (as usual) then found that 70% of the lookups could not find the unique id that I had specified. On closer inspection I discovered that the range of unique IDs had changed in my master plan for certain project so I went through and corrected all of the vlookups to look for these new unique ID's and all was well for a few days but then it happened again yesterday.

From the help system I found this "How Calculated    As you create new tasks, Project adds a unique number to each task in the project. This number is unique in that it is never rearranged or reused if the task is moved or deleted."

My Question, do you know why some projects in my master plan keep creating new Unique ID's when clearly from the help file extract above this should not happen

Once a project is inserted in to the Master plan they remain there until the project is formally closed so I am not constantly deleting and re-inserting projects

The only other bit of info that may have a bearing on the solution is that I save all the project files in 2000 -2003 file format because most of the PM's only have version 2003 installed where as I use version 2010

If you can shed some light on this I would be most greatful

Many thanks

Matt Collins

Answer 2

Hi Jan,

Yes you helped me on this before so now have no problems with the data displaying in excel I have just come to the conclusion that OLE does not work in Project 2010.

Thanks for your time again.

Regards

Matt

Answer 3

Hi Rod,

I will try your solution using Lookups but the VBA stuff is out of my league.

Thanks

Answer 4

Warning, paste links are very susceptible to corrupting your file(s). Do not ever move either file, rename them or over-write with a different copy. Every time you do you'll roll the file corruption dice. It's when not if.

Much better to copy paste manually all project data into a seperate sheet in Excel then use VLookups etc to read the data based on Task Unique ID. Either that or VBA macros to read data. Paste-links are risky, make sure your backups are rock solid as you may not detect a corruption for a week or two.

Answer 5

Hi Matt,

Just a hunch becaus e it's about Start ad Finish field.

Excel does not recognize some of Project's date formats (f.i. Mon 23/7/10) thus if the target cell is defined as date in Excel you get an error.