locked
Excel file Link changes to C Drive from an UNC Path - a bug in excel 2010? RRS feed

  • Question

  • All the Excel network links created in 2007/2003 change c:\... when open up in Excel 2010. It is impossible to update them all manually as we have 10000+ files each contains 10000000000 links +.

     The file and links work ok from a Windows XP machine with Excel 2007.

     For example:

     G:\Finance\Finance Drive - Restructured\Reporting\FY10 is replaced by C:\Users\nsapala\AppData\Roaming\Microsoft\Excel\XLSTART

    We have changed the startup location but it didn't seem to fix the issue.

     

    Help Please!!!..Questions or ideas very much welcome.

     

    Thursday, August 26, 2010 7:26 PM

Answers

  • Hello,

    I created a source file in 2007, saved it to a mapped drive.  I then created a destination file in 2007 that does a vlookup to the source file.  saved and closed both so the destination vlookup has the mapped drive in the vlookup formula.

    I then opened the destination file in Excel 2010, and it retained the correct path, and when I enabled external links, it updated properly. 

    I don't know of any issues with linking, nor any changes to linking functionality in Excel 2010.  I pinged some of my peers and the concurred that the only way we know of that this kind of thing could happen is if the file was opened from a local cache location, such as from an email attachment or from a browser.

    Other things that you can look for are third party addins that interact with Excel, both Excel addins and COM addins.

    Also, be sure to take a look at several files, including making sure that you check files that you are absolutely certain have not been previously opened in the new version.

    I hope this helps. If you need further assistance on this, please open a support case with us. You can review assisted support options here:

    http://support.microsoft.com/select/Default.aspx?target=assistance

    Thanks,

    Wendal Dorsey, MSFT

    Thursday, September 2, 2010 9:03 PM
    Answerer

All replies

  • We have asked in various places and tried all different fixes no one seems to be able to come up with a solution yet. Please can you help?

    For more details of this question on the case we are working on, please click on the click below. 

    http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26429571.html#a33526033

     

    Thanks!

    Thursday, August 26, 2010 7:30 PM
  • Why is there no reply... I losing my hope...
    Friday, August 27, 2010 12:48 AM
  • Hello,

    I have tried without success to reproduce your issue. I linked to a file on a mapped drive, then disconnected the mapped drive. I was not able to get them to change in this manner. IF Excel cannot find the drives, it gives you the choice to update links. IF you do not, it keeps the previous values there. I am not certain how this issue is occuring. The following documents how links are handled in Excel:

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;328440

    If a file has links to a mapped drive and someone who doesn't have those drives mapped opens the file and saves it, this can change the links because Excel no longer knows where the linked files are.

    If the file is send and opened from email, or opened from a web browser, the links can change.

    Please review the document at the link above and see if your users are updating the links as expected. Reply with additional questions.

    Thanks,

    Wendal Dorsey, MSFT

    Tuesday, August 31, 2010 9:58 PM
    Answerer
  • Hi Wendalmsft,

    thank you for your reply. Did you test this on 2010 or 2007? We did not have this problem when we migrated this from office 2003 -> 2007 but we only have the problem after we migrated from 2007 - 2010.

    We did not try to open the file from an email attachment or a brower. We have lots of shared files on the network drives and they are accessed using different version of excel. Excel 2003 and 2007 open up the files file but opening with Excel 2010 changes the entire path:

     G:\Finance\Finance Drive - Restructured\Reporting\FY10 is replaced by C:\Users\nsapala\AppData\Roaming\Microsoft\Excel\XLSTART

    Please note that this path is used in a VLookup function. Changing the start up location doesn't help... What I am confused about is they both open fine using 2003 and 2007 so why can't we open it in 2010... ?

     

    Wednesday, September 1, 2010 7:07 PM
  • Hello,

    I created a source file in 2007, saved it to a mapped drive.  I then created a destination file in 2007 that does a vlookup to the source file.  saved and closed both so the destination vlookup has the mapped drive in the vlookup formula.

    I then opened the destination file in Excel 2010, and it retained the correct path, and when I enabled external links, it updated properly. 

    I don't know of any issues with linking, nor any changes to linking functionality in Excel 2010.  I pinged some of my peers and the concurred that the only way we know of that this kind of thing could happen is if the file was opened from a local cache location, such as from an email attachment or from a browser.

    Other things that you can look for are third party addins that interact with Excel, both Excel addins and COM addins.

    Also, be sure to take a look at several files, including making sure that you check files that you are absolutely certain have not been previously opened in the new version.

    I hope this helps. If you need further assistance on this, please open a support case with us. You can review assisted support options here:

    http://support.microsoft.com/select/Default.aspx?target=assistance

    Thanks,

    Wendal Dorsey, MSFT

    Thursday, September 2, 2010 9:03 PM
    Answerer
  • I have a similar problem to caramelslice.

    I had a 2003 Excel spreadsheet  that I opened in Excel 2007. It opened in the compatibility mode and I used it and saved some new entries.

    Opened and closed the file which resided in my D: drive with no problems and then one day I noticed that some of the links changed from

    ..\data\aircraft400\Air Canada 747.jpeg

    to

    C:\User\AppData\Roaming\Microsoft\data\aircraft400\Air Canada 747.jpeg

    I think it happened when I left the file open and overnight my computer did an automatic Windows 7 update that required a restart.

    Peter Chin

    Saturday, March 19, 2011 4:01 AM
  • I too am seeing the same issue. In my case I'm using Excel 2007 on Vista Ultimate. All latest service packs as of today. This is my setup:

    I have created my own personal add-in. The addin is trivial, it just defines some simple standalone functions and nothing else. One of the functions is called XYZW(). The location of the add-in file is on the network drive.

    Now, I have created a brand new spreadsheet in the 2007 format that is very simplistic. That spreadsheet simply has "=XYZW()" in one of its cells. Over the past 6 months it happened twice that excel on its own changed my function call to "=C:\User\AppData\Roaming\Microsoft\path-on-my-network-drive!XYZW()" which is totally bogus.

    Why would excel EVER want to change the contents of the cell? This doesn't make any sense whatsoever.

    Tuesday, March 22, 2011 3:49 PM
  • For the previous post, just to clarify, bot the add-in and the brand new excel file that calls the add-in function reside on the exact same network location (NAS). Not sure if this is relevant, but i'm just throwing it out there. So neither of the files lives on the local C: drive.
    Tuesday, March 22, 2011 4:15 PM
  • I am having the same issue here

    A user complained that she had #REF errors in a certain excel file which has links to other excel files.

    These excel files are all located on our san.

    Her colleague however was not having this problem.

    Person 1 (with the problem) is running office 2010, person 2 is running office 2003

     

    I updated all the links in this excel to the correct UNC path (office 2007), yet when i open the file on an office 2010 the path changes to c:

    the changes are as followed

    i enter the UNC: \\server\share\folder

    office 2010 changes it to: c:\folder 


    Tuesday, March 29, 2011 9:58 AM
  • Hi,

    We are having the same issue since we migrated to excel 2010.

    We lose the links randomly and we are currently unable to work properly.

    Unfortunatly the file we use contain more than 500 links on an excel .xls file on a network share and we are only few people making modification on it

    Excel version we use are 2003/2007/2010

    We tryed to convert the file to .xlsx but we got the same issue

    This issue started after we upgraded to excel 2010 on one of win7 pc.

     

    Thursday, July 21, 2011 12:04 PM
  • any news ?
    Monday, July 25, 2011 9:32 AM
  • Seems not, I have same problem with my excel files located on network store, links just seem to change randomly to C: drive. I have one file with 8 external links, and at the moment 5 of them are changed and 3 are not. I really hope that MS folks can reproduce the problem and fix it.
    Friday, August 5, 2011 1:30 PM
  • I have the same problem too - MS can we agree this is officially a bug that needs to be fixed?
    Monday, September 12, 2011 11:35 PM
  • We are experiencing the same problem too!

    It randomly affects hyperlinks within the excel workbook.

    It seems to happen after excel recovers from a crash.

    Wednesday, September 14, 2011 11:40 AM
  • This bug is very easy to reproduce!

    Create two workbooks on a mapped drive - my example is on O:\

    Add some data to Sheet1 in workbook1 - say random numbers in A1 to E10

    In workbook2 add a formula to A1 - ='[Workbook1]Sheet1'!A1 - then copy to the range A1 - E10

    At this point you should have two workbooks that look the same

    Save and close them - if you reopen workbook2 the formulae wills till show a link to O:\ and this is confirmed if you look at [Edit Links] or [Name Manager]

    Now heres the weird bit! Copy workbook2 to your local drive and open it. Now look at the formulae. They are now pointing at C:\Documents and Settings\myname\My Documents\'[Workbook1]Sheet1'!A1

    Clearly this isnt going to work.

    I've looked at all the options in Excel 2007 and cannot find a way to prevent this.

    Looking forward to a response!

     

    Thursday, September 15, 2011 9:55 AM
  • Its been a week and I was hoping to have some kind of response from MS on this fundamental issue described in my previous post. Maybe MS are busy working on a fix to include it in the next raft of Office updates? The same problem occurs in Excel 2003.

    Friday, September 23, 2011 8:50 AM
  • Has anyone found a solution for this? We have the exact same problem with Excel 2010.  I must ask, are any of you finding this happening when you open the spreadsheet on a computer that has no network connectivity?  That is, someone saved the spreadsheet on their desktop and then went home and worked on it.  Then came back and uploaded the spreadsheet onto the network again? 

    I could see this causing a problem as Excel would try to update the hyperlinks.  Now the question is where did they move the setting to not update hyperlinks on save?  We are using Office 2010 SP1.


    Thanks

     

    Tuesday, October 4, 2011 1:17 PM
  • We are having the same issue with UNC paths inside files opened by Excel 2007/2010. No replies from Microsoft. Anyone have the solution?


    Matheus Cavalieri Carvalho
    Wednesday, October 5, 2011 4:09 PM
  • We ecountered the same problem.

    After testing we found that the actual =HYPERLINK function does not get replaced with the local temp redirection, or to ensure the links do not change to the local disk you can set your hyperlink base on the workbook properties.

     

    Monday, October 10, 2011 11:41 AM
  • We've also just run into this same issue, it seems to only occur when opening a excel file sent as an e-mail attachment.  I will suggest the HYPERLINK function as a possible fix, however I'd like to know if Microsoft will be responding to this issue and providing a way to stop it from happening.  Does anyone have that information?

    Tuesday, November 29, 2011 4:29 PM
  • We think we have found the cause, it occurs when one user has the spreadsheet open for editing, and then another user opens a read only copy and ask to be notified.  The read only copy user modifies the spreadsheet somehow, even a small change, and they are notified that the first user is out of the spreadsheet.  So the second user saves their changes. 

    It seems that Excel copies the links to the second users computer and changes the links to reflect where it was copied to.  I dunno, but we stopped doing the read only copy editing and have stopped having the problem.

     

    Hope this helps,

    Mike

    Monday, December 5, 2011 8:40 PM
  • Havoc64,  In our case the file is being opened as an e-mail attachment, so if I understand you correctly that would not be the cause in our case.  That is unless you mean the file that is being linked to is opened at the time the e-mail attachment is opened.  Even then, I think i "fixed" this by re-formatting the column in question and re-saving it on the network.  I think we need a serious investigation by MS.
    Friday, December 9, 2011 2:59 AM
  • Add another user experiencing the issue of lost hyperlinks. Think it is occurring when synching the workbook to a central server. Hyperlinks to files suddenly have "\AppData\Roaming\Microsoft\Excel\" added to the front of the link and obviously, I then get "Cannot open the specified file" error message. I say "think" as have just done that without loosing the hyperlinks - it may be when the file is updated by another user and then synched similar to Havoc64's issue?

     

    Wednesday, January 18, 2012 9:43 AM
  • This is a bug that I run into sometimes with sharepoint files. I am not sure why the issue happens as it is very intermitent. But I can confirm it is not just 2010, it happens in 2007 also.
    Thursday, February 9, 2012 4:18 PM
  • Why did you mark your own response as answered when in fact it hasn't answered anything.  Based on the number of replies here, it isn't an isolated case.

    To replicate, create a new file on a network drive using a UNC path (ie \\server\a\b\c) that links to another file via a UNC path.

    Map a drive to a lower level of the UNC path (ie \\server\a) and open the created file via the mapped drive, eg M:.  Check the links in that file now, it will have been changed from \\server\a\b\c\2ndfile to M:\a\b\c which is incorrect.

    Wednesday, March 21, 2012 1:35 AM
  • We have what sounds like a similar issue.  If I put a link to another workbook in a formula, it works fine as long as I am the only one that ever opens the workbook.  If the file is opened from a different computer, the new user gets the error message that the links can't be updated.  They all have to have the path re-established.  Although, that process works much quicker using Prepare and Update links from the office button than by going through the menus that pop up by clicking directly on the formulas, it still is not practical.  (Using the Prepare...Update links process will update multiple links that use the same workbook file from the first link that looks at a particular workbook.  Look at lots of different workbooks, and you'll be there a while.)  Open a Word document that has portions of a spreadsheet with these formulas after someone else has opened and edited the worksheet, and you could be there all day.  Just a half-hour or so if you have automated updating of links AND hotfix 981045 for Word 2007 installed.

    Here's an example formula. =VLOOKUP(C511,'\\slater.ad.cob.org\acctgsf\Data\Budget\Ordinances\2012\[ordinance lookup list.xlsx]Sheet1'!$A$1:$I$65535,9,FALSE)   One of the things that happens is the network path (in bold) will spontaneously change to the mapped drive letter (or vice versa) if another user has opened the file.  I have use find & replace to make all the formulas the path and all the formulas the letter consistently throughout the file and either one will work for me if I'm the only one that has opened the file.  If someone else has opened the file the links they updated may or may not have the path or drive letter changed, but will still need to be updated when I get them back.

    We have several Excel files with linked formulas and Word documents that have links to ranges of Excel worksheets - some containing formulas with links and some not.  Neither of these work correctly since upgrading to 2007.  We have been so long in dealing with these problems that people are now switching back to double entering data and pasting ranges into documents without linking them. 

    We now have some computers with 2010, and our files don't seem to work any better on there, although I didn't convert them to 2010 files - just opened them up. 

    Wednesday, March 21, 2012 8:45 PM
  • We are having same issue.  We have a Excel 2010 (.xlsx) spreadsheet which has been around since the very early days of Excel.  As we upgrade Excel from one version to the next the Internet hyperlinks has always worked as entered all the way through version 2007.  Now that we are on 2010 and saved it into the .xlsx format the Internet hyperlinks randomly turn into a file:///\\servername\sharename\spreadsheetname.xlsx link.  If you hightlight the cell, click in the fx box above,then immediately click on the check mark, the link now turns back into the correct link and works file.  You can save the file and the next time you open it the Internet hyperlink might be still set to the correct Internet link or reverted back to the file:///\\ .... link again.

    As with everyone else, we would like to see a fix to correct this problem.

    Tuesday, April 24, 2012 5:30 PM
  • Same problem here. We have a template to calculate prices, containing various vlookups that lookup prices in pricelists.

    Vlookup formulas refer to pricelists with UNC names.

    The most extreme I'm seeing is the following:

    =vlookup(x,'\\server\share\a\b\pricelists\pricelistA.xlsx!PriceTable',4,false) is in a sheet that is itself stored on \\server\share.

    Just copy this excel sheet to a local drive. Suddenly, the formula says:

    =vlookup(x,'d:\a\b\pricelists\pricelistA.xlsx!PriceTable',4,false).

    Huh? We COPIED the (closed) file, and someone/something decides that the lookup files are somewhere else?

    I know one thing for certain: when all the above is done with Excel 2007 running on an XP machine, the sheet can be placed anywhere without getting the vlookup changed. Even if some users have the '\\server\share\  mapped to a drive letter and other don't, this is not a  problem. It seems that WHEN the share is mapped to a drive letter, Excel displays the vlookup in 'drive letter'  format, but the formula is not really changed. When opened again on a machine without drive mapping, the UNC reappears.

    However, opening the sheet from Excel 2007 or Excel 2010 on a Windows 7 machine and saving it again (without touching the vlookup formula) strange things happen. In some cases \\server\share\a\b\ changes into \\server\share\b\ leaving out the first directory level.

    The 'edit links' function suddenly show multiple lines referring to the price list, with different directory paths, where one path was shown before. The variation being in different directory path levels suddenly missing.

    This problem is now becoming a real pain in the neck, as with have several thousands of these calculation sheets, that each can become crippled if someone dares to change anything in the sheet and resaving it. 

    I have now spent all afternoon experimenting, and I'm beginning to doubt that Excel is the real culprit. The fact that all is well on Windows XP working with the same server files makes me think that Windows 7 has something to do with it.

    I've read somewhere that some work has been done in Windows 7 (maybe already in Vista, but I'm glad we skipped that one) to make sure that links to removable media (like USB drives) are not made worthless when such drives are assigned a different drive letter.

    Could it be that Windows is mistakenly trying to repair something that isn't broken? And could it be that the effect is not (yet) too bad when using Excel 2007 with Windows 7, but that it gets much worse when Excel 2010 and Windows 7 are combining efforts to make our lives miserable?

    I recognize what TBS Consultants describe that sometimes all lookup formulas show '#N/A', but that 'updating' a single formula by changing nothing makes all #N/A's going away.

    Hope these observations help us to find out whats happening here.

    To Wendall (MSFT), if you're still here: if you haven't been able to reproduce this, bad luck. But the problem exists for sure, you bet.

    I hope this will help a little in unraveling this mistery and helping many people and organisations out of their misery.

    Wednesday, May 16, 2012 5:12 PM
  • However, opening the sheet from Excel 2007 or Excel 2010 on a Windows 7 machine and saving it again (without touching the vlookup formula) strange things happen. In some cases \\server\share\a\b\ changes into \\server\share\b\ leaving out the first directory level.

    The 'edit links' function suddenly show multiple lines referring to the price list, with different directory paths, where one path was shown before. The variation being in different directory path levels suddenly missing.

    I'd just like to drop in that we are having the exact same problem at my organisation, with paths being changed (seemingly upon opening of excel) with no additional changes being made. Directory levels are being removed, for no particular reason. I've not been able to reproduce it consistently, and have experimented with many different users (although all on Win 7, Excel 2010), but the files have been relatively unchanegd through upgrades from Win XP and Excel 2003/2007/2010. They had been working perfectly on Win 7/Excel 2010 until around 2 weeks ago or so, which makes me think Happy 300 may be correct about it being a Win 7 upgrade issue rather than specifically an excel issue
    • Edited by grubbstar Tuesday, May 22, 2012 10:55 AM
    Tuesday, May 22, 2012 10:51 AM
  • If you create shorcut for your XLSx file for example from network drive "Z" on the desktop, so in properties of the shorcut lnk file set the link in UNC.

    For example:

    shorcut has set:

    destination: "z:\Danek\Seznamobyvatel.xlsx"

    run in: "z:\Danek\"

    and you set this:

    destination: "\\olymp\Danek\Seznamobyvatel.xlsx"

    run in: "\\olymp\Danek\"

    And if run this shorcut, then the links updated automatically and promptly. Testing in Excel 2010.

    Sorry my horible "czech! english : -)

    Honza

    • Proposed as answer by Sanchel1 Thursday, June 14, 2012 2:45 PM
    • Unproposed as answer by Sanchel1 Thursday, June 14, 2012 2:45 PM
    Tuesday, June 5, 2012 9:29 AM
  • We too were having this issue at our company with some files that were created as far back as 2002 utilizing Excel 2000.  We are now running Windows 7 and Excel 2010 and recently begun having issues with files linked to our Q:\ drive were getting relinked to the C:\Users\"username"\AppData\Roaming\Microsoft\Excel\XLSTART.  I went into Excel Options-->Advanced-->General and changed the option for "At Startup, open all files in:" and added Q:\ in the text box to the right.  I hit OK and the next time I opened the excel files that were previously getting remapped to C:\Users\"username"\AppData\Roaming\Microsoft\Excel\XLSTART it was mapped to the proper location on our Q:\ drive.  Also, instead of putting in Q:\ in that path I tried it with the fully qualified network name \\servername-us\vol7\busgrps\ and that worked too.  Hope this helps someone out.
    • Proposed as answer by Sanchel1 Thursday, June 14, 2012 2:54 PM
    Thursday, June 14, 2012 2:54 PM
  • I have two PCs; PC One and PC Two. PC One acts as a file server. PC One runs Excel 2007 and PC Two has Excel 2010.

    Workbooks on the server (PC One) have macros that link to other workbooks also saved on PC One. If I create a workbook on PC One, I save links as full network paths eg '\\ERAHPPC1\Shared\Payroll\[My File.xlsm]Sheet1'!$A$1

    Opening and saving files from PC One gives me no problems.

    If I open a file from PC Two, it finds the linked files and works correctly. However, when I save the file on PC Two and then open it on PC One, the paths have been changed to 'C:\Payroll\[My File.xlsm]Sheet1'$A$1. Consequently, next time I open the workbook on PC Two, it looks for the linked files on its local disk and fails to find them.

    The only way I have found to correct this is to manually edit the links everytime I save the workbooks from PC Two. Given that each workbook has around 40 links and I have a few hundred workbooks this is a nightmare.

    I assume my problem is the same as reported by others here but, so far, I can't see any resolution. Can anyone help PLEASE!!


    Monday, June 25, 2012 3:16 PM
  • The issue is easy to reproduce. Just create a new workbook, point at a cell, press Ctrl+K to create/edit hyperlink, select Link to: Existing File or Web Page, pick a file somewhere on your C-drive and OK it. For now, all is well and the clicking the link goes to the right place.

    Now save the workbook, close and reopen it and try clicking the link again. Oops! The path has been edited by Excel on the assumption that your workstation is linked to some corporate domain and everything that you access locally is in fact stored in your roaming profile and synched to some network drive. Now I don't know if this happens in all possible circumstances but I do know two workarounds that work for me:

    1) Rather than a straight link, use the =HYPERLINK("full path and filename") or just store the fullpath and file in one cell, e.g. K7 and use =HYPERLINK(K7) and Excel won't remap your stuff.

    2) If the file you are linking to is on the same drive as the Excel sheet linking to it, and that relationship won't change, use relative paths. This works particularly well if the files you are linking to are either in the same directory as the sheet you are editing, or in sub-directories under it, which is often the case with me. To do this, when you hit Ctrl+K to add a hyperlink, edit the target path before clicking OK and put .. in front (meaning go to directory above) then specify path from that point on only.

    If you are using functions that reference other workbooks then read  http://support.microsoft.com/kb/328440 to see the rules Excel follows in modifying  paths in order to avoid problems.

    Thursday, July 5, 2012 2:48 AM
  • @Sanchel1

    Great answer.  Thanks.  I had a customer that uses a mapped drive to save a LOT of linked sheets and workbooks.  This solved the issue without having to run VB scripts on each sheet to fix the issue.

    • Edited by DLang100 Thursday, July 12, 2012 1:41 PM
    Thursday, July 12, 2012 1:39 PM
  • I am having the same issue here

    A user complained that she had #REF errors in a certain excel file which has links to other excel files.

    These excel files are all located on our san.

    Her colleague however was not having this problem.

    Person 1 (with the problem) is running office 2010, person 2 is running office 2003

    I updated all the links in this excel to the correct UNC path (office 2007), yet when i open the file on an office 2010 the path changes to c:

    the changes are as followed

    i enter the UNC: \\server\share\folder

    office 2010 changes it to: c:\folder 


    I have very similar problem. When I copy excel file from a network location to the local machine all the links inside the file changes from '\\xxx\xxx\folder\... to 'C:\folder...

    I use excel 2007 only.

    • Edited by yuri-b Tuesday, August 28, 2012 2:05 PM
    • Proposed as answer by tomkingtx Tuesday, September 25, 2012 4:17 PM
    • Unproposed as answer by tomkingtx Tuesday, September 25, 2012 4:18 PM
    Tuesday, August 28, 2012 2:03 PM
  • In excel 2010,<o:p></o:p>

                Go to file tab- click options button second up from the bottom- then click on save button on the left of the menu. Notice the auto recovery section and checkboxs, by default excel 2010 saves the file to c:\ apps data. Simply change the location to the E:\g:\ect drive location of the stored file. <o:p></o:p>

     

    There you go, good luck.<o:p></o:p>

    Tom King Texas<o:p></o:p>

    Tuesday, September 25, 2012 4:20 PM
  • Happening for me in Excel 2013 :-(
    Wednesday, December 5, 2012 4:05 AM
  • Happening to me as well, still no fix ? I'm using Office 2010

    Wednesday, December 5, 2012 3:40 PM
  • I am having a similar issue although I'm not accessing workbooks on a mapped network drive. When using 'save target as' from a sharepoint site to save a Excel 2010 workbook to my desktop, the server address links in the workbook change to the C: drive. The path does not show "C:\Users\..." it shows a truncated version of the server address.

    The server has... Windows Server 2008 R2 - Sharepoint Services 3.0 - Excel 2010

    The workstation ... Windows 7 Professional 64-bit - Office 2010

    Wednesday, December 5, 2012 7:03 PM
  • Hi,

    Did you ever get this issue resolved? I have just hit this issue now with a lot of our documents since moving to 2010. 

    Never had this issue with 2007.

    Thursday, December 6, 2012 4:10 PM
  • Ran into this same problem today when I copied to a new drive folder then copied back over to local drive.

    As someone mentioned below... simply enter the base  hyperlink.  I can only assume the original hyperlinks are fine as long as you don't change the file path.  But if you enter in a hyperlink base path (advanced properties of the file) then all the hyperlinks will defer to that as the base path and navigate from there.

    \\server\master\level1\filelocation.ext is where the original file was.  After I copied back and forth from network to local drive the path went to ..\level1\filelocation.ext.  I assumed I could add \\server\master\ and it would start there.  Turns out the ..\ is taking the path back one level, so I had to enter \\server\master\level1\ for this to work.

    • Proposed as answer by Jeff Nick Friday, December 21, 2012 7:52 PM
    Friday, December 21, 2012 7:41 PM
  • Yes we have been having this issue as well, in 2010 version using 2003, 2007 and 2010 created files.

    What I have found is that excel is not able to find the linked file, if you check:

    Data : Edit Links : Check Source

    This will most likely advise an "Error:Data Source not Found".   (this is what has happed to me).

    What it looks like it's doing is creating a temporary file at the time that the link is dropped so that the result of the link remains viable for your worksheet.   This means that it is a completly new file, not the origional file that you linked to in the first place.

    Fixing this there are two viable options at this point from the "Data : Edit Links" box:

    1. Break the Links - This will remove the link to the temporary file that was created and insert the last value that you had for each link - useable if you are workign from a fixed point
    2. Change the Data Source - link the temporary files back to the origional file that you used on your network.  This will update all links using file location that you are changing, and should update your sheet to the latest values.

    Sorry people this is no guarantee that the error will not happen again, but is a solution that has worked for me in the past.


    • Proposed as answer by Chris Hankey Sunday, February 10, 2013 10:18 PM
    • Edited by Chris Hankey Monday, February 11, 2013 12:39 AM
    Sunday, February 10, 2013 10:18 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.
    • Proposed as answer by Maxx747 Wednesday, March 5, 2014 3:14 PM
    Thursday, February 21, 2013 9:06 PM
  • I am having the same issue: "..\..\..\..\..\AppData\Roaming\Microsoft\Excel\" was mysteriously appended to all hyperlinks in one of my Excel files.  I am using Windows 7 and Microsoft Office Home and Student 2010.  I am not on a networked PC; I am the only user; I haven’t moved, emailed, or done anything unusual with the Excel file, so the conditions that other posters think might be causative don’t apply here.

    Like others I am tearing my hair out here because the scale of the damage is impossible to fix manually, and I can’t work with the file as it is.  Seems to be a MS bug that MS should have fixed 2 years ago.

    I would love to use AdrianPL’s subroutine to restore the file but I need some basics: Where and how do you run this?  Is it even possible with the Home and Student edition?

    Thanks!

    Tuesday, April 2, 2013 1:03 AM
  • It can be resolved by turn off File "update Links on save".  

    How to get there in excel 2010: 

    File > Options > Advanced > Scroll down to "General", then Click "Web Options" > Files, then uncheck "update Links on save". 

    Wednesday, April 17, 2013 12:51 AM
  • Had same issue in Excel 2007. Solved!

    In "Excel Options" go to "Advanced".

    There uncheck "Save external link values".

    Regards, Zvika Melamed


    zvikamelamed

    Friday, August 23, 2013 4:49 AM
  • Thankyou for this macro. 

    Instead of using the replace function however, in my worksheet the text being displayed was also the hyperlink target, so I just used:

    hLink.Address = hLink.TextToDisplay

    In the for loop.

    To prevent it from happening in the future I also used a lot of the solutions here to change the excel settings... hopefully one of them sticks.

    Tuesday, December 10, 2013 12:44 AM
  • The best way I've found to avoid this issue is to put the hyperlink location as text  in a cell in the same workbook  and then use the hyperlink command  to point to the cell with an indirect command.

    For example, in  cell B20 in a spreadsheet named "Data" (or what ever your sheet name is) place the text:

    ..\Folder1\Folder2\Filename

    ( this is the relative path pointing to the file you want to link to)

    or

    \\diskname\Folder0\Folder1\Folder2\Filename

     (this is the full path pointing to the cell you want to link to)

    then 

    in the cell that you want to click on and go to the hyperlinked address, place:

    =HYPERLINK(INDIRECT("Data!B20"),"LINK")

    Even better if you want to create many links, you can make the row address 20 a variable:

    =HYPERLINK(INDIRECT("Data!B"&(A1)),"LINK")

    where cell  A1 contains the row number...this makes copy and pastes work if you're doing a lot of hyperlinks...Cell A1 contains the number 20, Cell A2 contains the next number etc...

    Thursday, March 13, 2014 6:49 PM
  • replace your Hyperlink you just created with the full network link to server ex:

    the hyperlink you just created look like
    /file/blabla.pdf  
    replace it by :
    \\serverIPadress/ShareDriveLocation/file/blabla.pdf

    just copy \\serverIPadress/ShareDriveLocation and paste in front of all your damaged hyperlink

    Tuesday, April 8, 2014 1:02 PM
  • This is exactly what i was looking for.

    Thanks. Problem solved.

    Monday, April 14, 2014 9:34 AM
  • We too were having this issue at our company with some files that were created as far back as 2002 utilizing Excel 2000.  We are now running Windows 7 and Excel 2010 and recently begun having issues with files linked to our Q:\ drive were getting relinked to the C:\Users\"username"\AppData\Roaming\Microsoft\Excel\XLSTART.  I went into Excel Options-->Advanced-->General and changed the option for "At Startup, open all files in:" and added Q:\ in the text box to the right.  I hit OK and the next time I opened the excel files that were previously getting remapped to C:\Users\"username"\AppData\Roaming\Microsoft\Excel\XLSTART it was mapped to the proper location on our Q:\ drive.  Also, instead of putting in Q:\ in that path I tried it with the fully qualified network name \\servername-us\vol7\busgrps\ and that worked too.  Hope this helps someone out.

    I was running two machines.  A 32 bit with xl 2010 and a 64 bit with xl 2013.  I noticed that when I opened my drawings catalog on the 64 bit system, all the links (about 14,000 of them) were reset to my C: drive.  Like others here, I started resetting them manually. Then I found a handy macro that let me do a find and replace on hot links which got me out of the woods until it happened again and then a third time. I am amazed that something like this was coded (what possible use could it serve?) and that MS hasn't lifted a finger to help resolve it.  This problem could bring a business to its knees.  The first time it happened to me, I didn't sleep a wink that night believing that I would be seriously reprimanded for have 14,000 dead links in my catalog. 

    What finally fixed my issue... I did the two steps in the quote above and it appears to be fixed.  Thanks to whomever discovered it.  Maybe MS will get some use out of it and then take credit for it, who knows.  Hopefully they will see that they seriously dropped the ball on this one. 

    Tuesday, June 17, 2014 6:03 PM
  • Excellent. Great solution. It worked for me. Thanks a lot
    Friday, July 18, 2014 2:43 PM
  • Dear Grubbstar

    I hope you can help me.

    I've a similar problem

    Excel file with internal links in UNC format

    (ex. \\server\sharename\dir1\dir2\.. )

    Sometimes it happens, when I reopen the excel file, that the path change in:

    \\server\dir1\dir2 ..), and the indication of the sharename disappear.

    We correct this link, but after sometimes, this happened again.

    We use Excel Vers. 14.0.7128.5000 (32bit) in MS Office Professional Plus2010, with Windows 7 Professional SP1.

    The server is Windows Server 2003 Enterprise x64 Ed, Vers. 5.2, SP2

    Thank you and to everyone can help us.

    Bye Filippo

    Thursday, September 25, 2014 11:53 AM
  • This happens to me all the time.  It always happens after the program has crashed and restarted, and asks me to restore the desired copy (original, last-saved, or auto-saved, I think).  I think I usually end up choosing the autosaved and the hyperlinks - over a hundred of them - have all been redirected to the C:\Users\(username)\AppData\Roaming\Microsoft\Excel\ location.  It's crippling.
    Friday, February 20, 2015 8:26 PM
  • Dear god, thank you, I have spent 5 hours trying to fix this, staring in horror, rambling at my workmates for changing things, watching Excel change this value literally as soon as I enter it.

    I actually had one further issue before I could do this properly. While using the indirect text hyperlink workaround it seemed to do it something - it turns out that if I already had tried to use the hyperlink jump while the same cell already had a previous hyperlink inserted, it seemed to do the same thing. If I clear the cell and then enter it all fresh it works fine.

    Microsoft may as well delete the regular =hyperlink function because it is now USELESS. There better be a good security/functionality reason for this behaviour (I've seen good intentions do bad things before), because this was depressing.

    • Proposed as answer by GrStPo Thursday, January 24, 2019 1:14 PM
    • Unproposed as answer by GrStPo Thursday, January 24, 2019 1:14 PM
    • Proposed as answer by GrStPo Thursday, January 24, 2019 1:14 PM
    Friday, October 23, 2015 9:52 AM
  • My problem was same. Here is the simple answer how to get rid of that bug.

    use "ip" adress of target hyperlink instead of words

    \\groups\boards\.......\.....\        to   \\10.15.09.1\boards\.......\.....\

    this is all you have to do   :)



    Tuesday, October 11, 2016 8:04 AM
  • Although it sounds as if I do not have nearly as many hyperlinks as you I do I have alot, I have tried without success as well and I have given up.  I have decided to just relink the cell as needed. but I did figure out why it happened so that it will not happen again.  apparently my docs was saved inadvertently in an older version and therefore all hyperlinks went to roam on my local computer, and now no hyperlink is useful nor was I able to open spreadsheet in edit mode.  I hope you solve your issue, but I for one am very sad.  
    Wednesday, October 4, 2017 9:16 PM