Sign in
 
Home20132010Other VersionsLibraryForumsGallery
TechCenter >
> 
> 
Excel 2010 changes relative link paths to absolute in files synced with Offline Files in Windows 7

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

  • Monday, February 06, 2012 7:38 AM
     
     
    Sign In to Vote
    1
    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?
     

All Replies

  • Thursday, February 09, 2012 8:57 AM
    Moderator
     
     
    Sign In to Vote
    0

    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

     
  • Friday, February 10, 2012 6:56 PM
    Moderator
     
     Answered
    Sign In to Vote
    0

    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

     
  • Sunday, April 01, 2012 6:07 AM
     
     
    Sign In to Vote
    0

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

    --

     
  • Wednesday, April 11, 2012 9:01 PM
     
     
    Sign In to Vote
    0

    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:03 PM
     
     
    Sign In to Vote
    0
    and how do I setup notification when an answer has been found for this issue?
     
  • Friday, April 20, 2012 7:41 PM
     
     
    Sign In to Vote
    0
    Me too...would like a solution.
     
  • Thursday, February 21, 2013 9:07 PM
     
      Has Code
    Sign In to Vote
    0

    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.