Excel 2010 changes relative link paths to absolute in files synced with Offline Files in Windows 7
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?
February 6th, 2012 7:38am
Thank you for your post.
This is a quick note to let you know that we are performing research on this issue.
February 9th, 2012 8:57am
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.
Microsoft Excel Support
Marked as answer by
Wednesday, February 22, 2012 2:09 AM
February 10th, 2012 6:56pm
I have same problems too, Please fix or tell us workaround.
April 1st, 2012 6:07am
I have same problems too, Please fix or tell us worka
April 11th, 2012 9:01pm
and how do I setup notification when an answer has been found for this issue?
April 11th, 2012 9:03pm
Me too...would like a solution.
April 20th, 2012 7:41pm
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:
Dim hLink As Hyperlink
For Each hLink In ActiveSheet.Hyperlinks
hLink.Address = Replace(hLink.Address, "SOURCE", "DESTINATION")
SOURCE = C:\Users\username\AppData\Roaming\Microsoft\Excel\
DESTINATION = \\SERVER\shared\
This macro will repair your links in the active sheet.
February 21st, 2013 9:07pm
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!
September 13th, 2013 5:46pm
18 months later... still the same problem. Ruined a week's worth of work, just emailing a spreadsheet back and forth. UGH!
September 19th, 2013 2:38pm
How do I open a support case? I have no pre-paid technical support agreements in place.
November 14th, 2013 10:08pm
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.
February 6th, 2014 8:58am
Problem still alive.
October 24th, 2014 7:27pm
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.
March 6th, 2015 6:25pm