Excel Crash Leading To Broken Links

[BTilson]

We maintain a massive spreadsheet that has hundreds, if not thousands, of links to files all around our server document tree. It crashed during loading this morning, and it broke all of the links. They now all refer to a C:\users\... type link. Something like this happened once before, and I was able to use some VBA code to loop through the links and fix them, because they all had the same starting path, but that isn't the case in this spreadsheet.

So my next thought was to just pull it from last nights backup. The file was there, but when we opened it, the links there were all broken as well. If he opens it on his computer, it breaks the links and they refer to his C: drive, but if I open them, they refer to mine. We aren't having any connectivity issues, and our folder structure on the server hasn't changed.

Does anyone have any idea what is going on? Any suggestions on how to restore those links without having to remake them all? Any way to prevent this from happening again?

I'm at a loss here! Thanks in advance for any help!

Brooks Tilson
Database Development
Tilson Machine, Inc.

 

[SkipVought]

hi,

Do you have any other backup earlier than last night? A backup copy is about the only recourse.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MakeItSo]

Automatic calculation?
Perhaps opening a backup via some VBA with disabled re-calculation?

“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[MakeItSo]

Try something like this, with a backup file that hasn't been opened yet:

Application.Workbooks.Open yourfile, [b]UpdateLinks:=False[/b]

“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[BTilson]

We do indeed have more backups. Going to go dig into that now and see what I can make happen.

Thanks for all the help! Will update with how it goes.

Brooks Tilson
Database Development
Tilson Machine, Inc.

 

[BTilson]

No luck. It did the exact same thing. I used the VBA code that MakeItSo suggested.

Brooks Tilson
Database Development
Tilson Machine, Inc.

 

[SkipVought]

Seems your database development group has created an accident waiting to happen.

I'd code ALL external references in VBA. I'd assume that they are MS Query references, surely not cell references. I do this sort of thing routinely with my external references, and even if and when (because it is bound to happen) a workbook gets corrupted, I can easily reconstruct these links via code.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[BTilson]

Skip,

I alone am our "database development group", but this spreadsheet was created entirely without my input and has been in use for years. The links aren't query references or cell references, but literal hyperlinks to other files. Sometimes they're excel docs, but most of the time, they're scanned in .tif images of employee training records. So now, instead of these links pointing where they're supposed to, they all point to the local drive of whoever first opened the sheet after it crashed.

The part I find most confusing is why it is happening with backup copies that were saved out before it crashed this morning. I'd initially assumed that someone had changed our directory structure around in some fundamental way, but I have since verified that that is not the case.

I appreciate the input! It's looking like the only option we may have is to reconstruct all the links manually. That just seems really silly and pointless though, because as soon as it crashes again, we'll have to do it again. There really has to be a better way. I've suggested moving this (among multiple other instances of this same potential problem) into a database system, but I wear a lot of hats here, and programming time is basically only granted when absolutely necessary.

Brooks Tilson
Database Development
Tilson Machine, Inc.

 

[SkipVought]

Run Microsoft Office Diagnostics within Excel to see if a REPAIR can be accomplished on EXCEL.

The try opening some workbook.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[BTilson]

Unfortunately, from what I can tell, the office diagnostics was removed in 2010 (the version we have). If I am wrong, please feel free to correct me though!

Brooks Tilson
Database Development
Tilson Machine, Inc.

 

[MakeItSo]

Wrong.
It just isn't directly accessible through the help menu like it was before.
Now what you need to do is:

Click Start > Control Panel > Programs > Programs and Features.
Click the Office program you want to repair, and then click Change.
Click Repair > Continue.

From:

http://office.microsoft.com/en-us/powerpoint-help/repair-office-programs-HA010357402.aspx

Good luck!

“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[BTilson]

Well, I found and ran the repair, but it made no difference whatsoever. I am utterly mystified as to why this is happening. We maintain "versioned" backups as well, and I pull a copy of this document from over a month ago, just out of curiosity, and when I opened it, the links were broken.

Not sure there is really any other option left besides tell the user he's going to have to remake his links.

Thanks for the help!

Brooks Tilson
Database Development
Tilson Machine, Inc.

 

[CJTyo]

Brooks,

This isn't just you, your implementation, or your crash.

This is a problem with either Excel or a combination of Excel and Office. We have numerous Excel 2010 and 2007 files on our SharePoint server pointing to other files on the same SP server. Inconsistently we have a problem where someone will edit the file and suddenly all the links go from a path of SERVER/SP path/filename to a path of SERVER/local user computer path/filename. I have researched this extensively and it is disturbingly common - and random. However, there doesn't seem to be any type of consistent fix or solution that I've found. This is why I am here today...

Anyone with ideas on how to fix the URLs once they're broken - please help! A search-and-replace doesn't pick up the hyperlink to change it.

Anyone with ideas on how to prevent this - please, please, please help!

Thank you!
C



*~*~*~*~*~*~*~*~*~*~*~*~*~*
Insanity is a matter of Perception.

 

[BTilson]

CJTyo and everyone else,

I haven't found a solution to this problem, but I DID figure out how to make the backup copies work correctly. For whatever reason, they have to be in the same location as the original corrupt file for the hyperlinks to work. Initially, I was copying the file to my personal computer and opening it there, because a hyperlink is a hyperlink, right? Seemed to me that it shouldn't matter where the file storing the hyperlink was located. However, once I finally copied it into the folder on the server where the corrupted file was and opened it, all of the hyperlinks from the backup copy were fine.

Really bizarre and frustrating problem, with a bizarre and puzzling solution.

Anyway, maybe that will help some people out there somewhere!

Brooks Tilson
Database Development
Tilson Machine, Inc.

 

[MakeItSo]

Hi Brooks,

although the issue has ostensibly been resolved a year ago, it might still be an issue with the hyperlinks:
==>KB2597166!

http://social.technet.microsoft.com...tive-path-when-saving-as-xlsx-after-kb2597166

The workaround solution is to uninstall the patch. This can done Silently (or from SCCM) with the solution given below. I have tested it from cmd prompt and from SCCM, its working. Thanks to Kevin.

Silent Uninstall:
msiexec /I {90140000-0011-0000-0000-0000000FF1CE} MSIPATCHREMOVE={B76D8C6D-1F13-42A7-9931-D7504CB89D6D} /qn

Hope this helps,
Andreas

“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[BillPSU]

I am listening to all the heartbreak these massively complex spreadsheets cause and feel the pain everybody is suffering. The real solution is to stop using spreadsheets as databases and convert to a true database.

The spread sheet linkage rules and single user usage make it difficult for spreadsheets.

 

[SkipVought]

[highlight #FCE94F][/highlight]

Anyone with ideas on how to prevent this

1) Use each tool within the parameters of its design.
2) Use a proper tool for an appropriate task.
3) Do not try to use a tool for a purpose for which it is not intended.

You might, in a pinch, use a knife to turn a screw. I've done it, for instance, by using my pen knife to tighten a screw on my spectacles. But, guess what: althought it 'works' to some extent, a pen knife is not the right tool. Now, over many years of this abuse, the slot in the screw has become distorted and it has become more difficult to tighten the screw even using a proper screw driver.

Software, is a much more complex tool and the ramifications of abuse, attempting to use the software for unintended purposes, are much more devistating than what has happened to my screw, especially for a business relying on the data assets that the tool is used to manipulate and maintain.

All this windage to support the post that BillPSU just made.

Failing to spend the resources to acquire knowledgable talent, comprehensive planning & design, ample training and properly used systems (and not necessarily computer systems) is penny wise and dollar foolish, IMNSHO.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]