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.

 

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!

Why is there no reply... I losing my hope...

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

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... ?

 

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

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

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.

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.

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 

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.

 

any news ?

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.

I have the same problem too - MS can we agree this is officially a bug that needs to be fixed?

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.

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!

 

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.

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

 

We are having the same issue with UNC paths inside files opened by Excel 2007/2010. No replies from Microsoft. Anyone have the solution?

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.

 

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?

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

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.

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?

 

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.

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.

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. 

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.

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.

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

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

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 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!!

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.

@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.

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.

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>

Happening for me in Excel 2013 :-(

Happening to me as well, still no fix ? I'm using Office 2010

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

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.

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.

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.

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.

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 havent moved, emailed, or done anything unusual with the Excel file, so the conditions that other posters think might be causative dont apply here.

Like others I am tearing my hair out here because the scale of the damage is impossible to fix manually, and I cant 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 AdrianPLs 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!

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". 

Had same issue in Excel 2007. Solved!

In "Excel Options" go to "Advanced".

There uncheck "Save external link values".

Regards, Zvika Melamed