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.

 

• Edited by caramelslice Thursday, August 26, 2010 7:31 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

• Marked as answer by Wendal DorseyMicrosoft employee, Editor Thursday, September 02, 2010 9:04 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

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

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

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

• Edited by Bill Flavell Monday, June 25, 2012 4:20 PM

@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

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

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

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

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 05, 2014 3:14 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 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!

• Proposed as answer by Abdussalam Areacode Monday, March 10, 2014 6:44 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.

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

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

This is exactly what i was looking for.

Thanks. Problem solved.

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. 

Excellent. Great solution. It worked for me. Thanks a lot

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

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.