Clearing Links To Excel

[Andrew Newell]

Hi,

I have a project plan for last year which had links to an excel sheet using
the copy > paste special command. I have saved this plan and date adjusted
it to use as a template for a similar project this year.

The project file recognises that it has links to another file because when I
delete tasks that are no longer required I get a message similar to "this
task has data that is dynamicaly linked to another file, deleting this task
will cause the data to be lost etc."

The links do not appear in Edit > Links so is there a way of clearing all
the links in this file - could I export to access etc. and delete reference?
If so how do I go about this?

Any help would be greatly appreciated

Regards

Andrew

 

[John]

Hi,

I have a project plan for last year which had links to an excel sheet using
the copy > paste special command. I have saved this plan and date adjusted
it to use as a template for a similar project this year.

The project file recognises that it has links to another file because when I
delete tasks that are no longer required I get a message similar to "this
task has data that is dynamicaly linked to another file, deleting this task
will cause the data to be lost etc."

The links do not appear in Edit > Links so is there a way of clearing all
the links in this file - could I export to access etc. and delete reference?
If so how do I go about this?

Any help would be greatly appreciated

Regards

Andrew

Andrew,
Ah, one of the problems with Paste Links, they can be troublesome at
best and cause corruption at worst. Saving a Project that has links for
use as a template is a bad idea. As you see, the links don't just
disappear. Links consist of two parts. One part resides in the source
file (in this case Project) and the other part resides in the
destination file (Excel). For whatever reason the link table is only
visible in the destination file.

Depending on what has transpired since the links were last updated this
process may or may not work. Open the Project file and open the Excel
file to which it is linked. Unfortunately there isn't a Break Link
option in the Excel link table so the only way to effectively break the
links (that I know of) is to select the cells that have the links in the
Excel spreadsheet and delete the cell contents. Then do a Save As for
the Excel file and a Save As for the Project file. If you are lucky, all
link parts will be deleted cleanly.

Disclaimer - I have not worked with Paste Links between Project and
Excel but the above process (except for the Break Link option) is
effective for properly and completely breaking Paste Links between
Project files. If someone has a better method, I invite them to respond.
The important point is that both ends of the link must be broken in
order to avoid link fragments and the file corruption that follows.

If the above process does not work, it is likely either or both files
(Project template and Excel spreadsheet) are already corrupt. In that
case, I suggest you go to the MVP website at:
http://www.mvps.org/project/faqs.htm
and look at FAQ 43 - file bloat? Could be corruption.

It just doesn't get any more fun than this.

John
Project MVP

 

[John]

Andrew,
Ah, one of the problems with Paste Links, they can be troublesome at
best and cause corruption at worst. Saving a Project that has links for
use as a template is a bad idea. As you see, the links don't just
disappear. Links consist of two parts. One part resides in the source
file (in this case Project) and the other part resides in the
destination file (Excel). For whatever reason the link table is only
visible in the destination file.

Depending on what has transpired since the links were last updated this
process may or may not work. Open the Project file and open the Excel
file to which it is linked. Unfortunately there isn't a Break Link
option in the Excel link table so the only way to effectively break the
links (that I know of) is to select the cells that have the links in the
Excel spreadsheet and delete the cell contents. Then do a Save As for
the Excel file and a Save As for the Project file. If you are lucky, all
link parts will be deleted cleanly.

Disclaimer - I have not worked with Paste Links between Project and
Excel but the above process (except for the Break Link option) is
effective for properly and completely breaking Paste Links between
Project files. If someone has a better method, I invite them to respond.
The important point is that both ends of the link must be broken in
order to avoid link fragments and the file corruption that follows.

If the above process does not work, it is likely either or both files
(Project template and Excel spreadsheet) are already corrupt. In that
case, I suggest you go to the MVP website at:
http://www.mvps.org/project/faqs.htm
and look at FAQ 43 - file bloat? Could be corruption.

It just doesn't get any more fun than this.

John
Project MVP

Andrew,
A correction to my suggested process. It appears that merely saving the
Project file after the Excel link is broken does not fully break the
link part residing in Project. It appears the only way to break that
side of the link is by deleting the task and that pretty much defeats
the purpose of what you are trying to do.

Sorry for the bad info. Maybe someone else has a good idea.

John

 

[DonL]

Try downloading and using Bill Manville's FindLink utility at
http://www.oaltd.co.uk/MVP/Default.htm

This utility will find and let you "break" the links in Excel.

 

[John]

http://www.oaltd.co.uk/MVP/Default.htm

Don,
Thanks for jumping in. I took a look at link you suggested but I did not
download the utility. Reading the description however seems to indicate
it will break links FROM a workbook which, if I understood correctly,
isn't exactly what Andrew has, but perhaps the utility will still be of
use.

John
Project MVP

 

[DonL]

John,

I have used this "add-in" to search out and delete links from MSP TO Excel,
and it does a great job.