Hello! I'm wondering if anyone else has seen this problem: I have a large number of Excel 2010 and 2003 files in a folder on my file server. This whole folder is also synced to my computer using Offline Files in Windows 7. I have a lot of references between cells in different Excel files, and all referenced workbooks are physically in the same folder. This all works nicely when I create these files at work - all file paths referenced in the cells are created as relative paths and the documents open correctly. This is, I understand, the expected and default behavior when Excel creates links. When I edit these files at home, nothing seems odd until I get back to work and sync these files back to the file server. At this point, I discovered that Excel 2010 has, when I saved the files while away from the corporate network, changed /all/ the cell references in any offline-edited Excel files to point at absolute paths, and that these absolute paths point to somewhere in my %APPDATA% structure. So whenever I come to work and I try to open an Excel file that I have recently worked with offline, I get a bunch of error messages about referenced files that are missing, although clearly they exist in the same folder as the file I've opened, and I must edit all the file references again, whereupon they are again created correctly as relative paths (since all files exist in the same folder), which are promptly mangled into absolute C:\....\Offline Files\.....\..... paths whenever I save them at home (and since that works too, I don't notice it again until I come back to work and the offline files are synced back to the real network location). This seems to be a case of Windows 7's Offline Files not being able to fool Excel 2010 into believing it is working on a file server - apparently Excel 2010 can see through the fakery and decides on it's own to "fix" the problem (which obviously isn't a problem since the paths are relative to begin with) by saving the paths as absolute paths instead. Yes, really clever, Excel. The exepected behavior according to MSKB is that links are created as relative paths, so why does it change to absolute whenever Offline Files are involved? I know Offline Files only syncs, it doesn't actually change the files, so I can conclude that Excel is the program at fault here. Is there a fix for this, or a known workaround? Because frankly, this bug makes it impossible for me to work in any advanced manner with linked Excel files. The sad thing is that this worked perfectly fine with Office 2003 and Windows XP. Is there a patch for this problem that I might have missed (I am running the latest Service Pack and I get Office updates from Microsoft Update). If not, is there a workaround I can use to prevent Excel from corrupting my links when I edit the files offline?

Hello danceswithwindows,
 
Thank you for your post.
 
This is a quick note to let you know that we are performing research on this issue.

Sincerely

Rex Zhang

I was not able to find a bug on this problem. This may be by design as much of Excel changed from 2003 to 2007/2010. To adequately answer this question, you would need to open a support case with Microsoft Technical Support.

Will Buffington
Microsoft Excel Support

• Marked as answer by Rex ZhangModerator Wednesday, February 22, 2012 2:09 AM

I have same problems too, Please fix or tell us workaround.

and how do I setup notification when an answer has been found for this issue?

Me too...would like a solution.

Guys, I also deal with this issue, and - as soon as we don't know what is the source - here's a quick fix for all our broken links:

Sub RepairLinks()
Dim hLink As Hyperlink

For Each hLink In ActiveSheet.Hyperlinks
hLink.Address = Replace(hLink.Address, "SOURCE", "DESTINATION")
Next hLink

End Sub

e.g.:

SOURCE = C:\Users\username\AppData\Roaming\Microsoft\Excel\
DESTINATION = \\SERVER\shared\

This macro will repair your links in the active sheet.

Same here, only I'm working only locally at home - out of nowhere, the blue screen of death error, then when I reboot, all of my linked files have been resourced from from my designated directory to the user/.../appdata/... location.  VERY FRUSTRATING!

18 months later... still the same problem.  Ruined a week's worth of work, just emailing a spreadsheet back and forth.  UGH!

How do I open a support case?  I have no pre-paid technical support agreements in place.

That is a perfect answer, Will.

It solves the problem. It gives the customer the solution he or she was hoping for : "It is not my problem, go find your solution else where."

I think Napoleon invented this. It's called "administration". It's inert, it justifies its existence by keeping the problem alive and the situation stable.

And it works: samking confirmed on Thursday, November 14, 2013 10:08 PM that the problem is still alive and kicking.

For comparisons sake: If I ask something in my local grocery store, I get an answer, or the person in front of me asks a colleague to help me. But that is perhaps why they will always remain a small grocery store.

Problem still alive.

This is still absolutely happening.

Christ, how hard is this to duplicate?

Make an Excel file on a network drive. Insert a hyperlink within a table in the Excel file that links to another file on the network drive.

Make a Word file anywhere. Insert a link to the table in the first step (that has a hyperlink in it) using Paste with Link.

Look at the link. It no longer goes to the right place, and goes to your local temporary files.