none
Excel 2010 changes relative link paths to absolute in files synced with Offline Files in Windows 7

    Question

  • 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?
    Monday, February 06, 2012 7:38 AM

Answers

  • 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

    Friday, February 10, 2012 6:56 PM
    Moderator

All replies

  • 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


    Rex Zhang

    TechNet Community Support

    Thursday, February 09, 2012 8:57 AM
    Moderator
  • 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

    Friday, February 10, 2012 6:56 PM
    Moderator
  • I have same problems too, Please fix or tell us workaround.


    --

    Sunday, April 01, 2012 6:07 AM
  • I have same problems too, Please fix or tell us workaround.


    --

    Same problem here ... I have a worksheet with links that are changing from http:\\tech.myserver.com\admin... to P;\admin which is an invalid file path

    Wednesday, April 11, 2012 9:01 PM
  • and how do I setup notification when an answer has been found for this issue?
    Wednesday, April 11, 2012 9:03 PM
  • Me too...would like a solution.
    Friday, April 20, 2012 7:41 PM
  • 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.

    Thursday, February 21, 2013 9:07 PM
  • 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!
    Friday, September 13, 2013 5:46 PM
  • 18 months later... still the same problem.  Ruined a week's worth of work, just emailing a spreadsheet back and forth.  UGH!
    Thursday, September 19, 2013 2:38 PM
  • How do I open a support case?  I have no pre-paid technical support agreements in place.
    Thursday, November 14, 2013 10:08 PM
  • 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.

    Thursday, February 06, 2014 8:58 AM