As discussed here but not resolved http://social.technet.microsoft.com/Forums/en-US/excel/thread/fc470dd1-70df-4bad-98db-3d94a68206c7. On opening a 2003.xls fle that contains linked cells to a network location the link paths are changed to the current local users appdata path. This really is an issue for us, we are a large coporate user with thousands of linked workbooks often with links to several other workbooks, it's not really an acceptable solution to have to manually update the links each time a 2010 users opens an existing 2003 doc.

Can anybody offer a solution?

 

 

Have not experienced this yet, only time I have seen this is where there are hyperlinks in files and if the workbook crashes all the links are set to the local machine.

Could it be that excel 2010, when attempting to open the 2003 file, suffers a crash, writes links to local machine then reopens this file?

Can't offer a solution, will do some testing later.

 

Matt

 

Create the file containing the data on the network, then create the file that needs to link it on a local machine, or in a seperate network location.

You should then be able to move this file to other locations whilst maintaining the links to the backend.

Seems to work ok on my machine doing it this way.

Matt

Matt,

This isn't related to moving documents or Excel crashing.

We have an established network of approx 150 workstations and 200 users. All of the workstations for approx the last five years have run XP and Office 2003. The back office is a mix of Novell Netware and Server 2003. We have just begun to upgrade some of the exisiting XP workstations to Office 2010. If one of the workstations with 2010 opens an existing 2003 workbook that contains linked cells it initially opens with a Security Warning stating that "Automatic update of links has been disabled" it gives the user the option to "enable" the content.  When you click to enable content you are given an error message telling you that the links cannot be updated, the options are to continue or to 'edit links'. Whether you choose to continue or to edit the links the path to the worksheet containing the links will have changed from whatever network location it was to the appdata path of the local user, for example if the path to the linked workbook in excel 2003 was H:\Finance\accounts\2010 when opened by user djs with Excel 2010 it will change to  C:\Documents and Settings\djs\Application Data\Microsoft\Excel\XLSTART\Finance\accounts\2010 (note: whatever the current network path is it is appended to C:\Documents and Settings\djs\Application Data\Microsoft\Excel\XLSTART)

To recreate this issue:- Create two new workbooks on a mapped drive using Excel 2003 from an XP workstation, link some cells of one to the other and then save. Now either upgrade the workstation to 2010 or use a different workstaion with 2010 already installed and from the mapped network drive open the 2003 workbook that contains the links that you have just created.... the paths will have changed to c:\docs n settings\local user\appdata\ms\xl\xlstart\original network path\   .......

Regards,

Dan

• Edited by djstanley Tuesday, November 29, 2011 3:55 PM

Dan,

Tried that with two 2010 files and if they are created in the same directory excel assumes the links are local, thus when opened they are ok.  However when I copy the one that contains links to an alternate directory this is when it points the links to the local machine.  Opening them in the same directory that they are created in they work ok.

If I create two 2003 files, one with links, the other with data on a mapped network drive, if I then open the wb in 2010 it opens in compatibility mode, prompts me with a security warning, I click enable content, I am then asked if I want to make it a trusted document, selecting yes opens the workbook.  For me the links remain pointed to my networked copy and not my local machine.  The only time I break the links is by physicly moving the workbook.

 

Matt

Hiya,

We are experiencing this with every single 'linked' 2003 workbook that we open with 2010 without fail. None of them have been moved, with regard to the directorys, the workbooks are not local to each other (ie although stored on the same network drive they are not in the same folder). One thing to note is that all of the documents are stored on a none MS server (Netware 6 and 6.5). Also, I can move them where ever I like and then open them with 2003 and the links remain consistent and correct but as soon as I open one with 2010 whether from it's original location or not I get the above.....

Regards,

Dan

As well as moving from Office 2003 to Office 2010 we are also moving from away from the Novell E-directory to a 2008 based domain, to that end I have a test enviroment already setup of several Windows clients (mix of XP and 7) with a 2008 R2 Server and Exchange 2010 in a pure MS domain.

To see if this is related to the files being stored on a NetWare volume or something the Novell client is doing I have moved the files to a share on the 2008 server (using an USB HDD) I have then assigned some users rights and created a mapped drive (using logon.bat via group policy) so that from a client machine the path is exactly as it was in the Novell environment - H:\Finance\accounts\2010.

Installed on one of the XP clients is Office 2003, if I logon to this XP client as a domain user and open the any linked workbook created in 2003 (I've copied the entire accounts dept directory structure over!) it retains the correct link paths. On the 7 client is Office 2010, when I open the workbook here I get the issue above except now the path is c:\Users\djstanley\AppData\Roaming\Microsoft\Excel\XLSTART\Finance\accounts\2010.... (obviously as this is a 7 machine)  ...... one thing I did notice, when opening the file with Excel 2010 on the Windows 7 client the status bar displayed "downloading H:\Finance\accounts\2010\Project Tracker.xls"

Dan,

Tried that with two 2010 files and if they are created in the same directory excel assumes the links are local, thus when opened they are ok.  However when I copy the one that contains links to an alternate directory this is when it points the links to the local machine.  Opening them in the same directory that they are created in they work ok.

If I create two 2003 files, one with links, the other with data on a mapped network drive, if I then open the wb in 2010 it opens in compatibility mode, prompts me with a security warning, I click enable content, I am then asked if I want to make it a trusted document, selecting yes opens the workbook.  For me the links remain pointed to my networked copy and not my local machine.  The only time I break the links is by physicly moving the workbook.

Matt

Is there any further advances on this?? .... this is a real problem that is enough to make us re-consider renewing our OVS, software licensing and chosen software provider.... there are many reports of this from around the web, all from people with existing 2003 documents who have moved up to 2010....   document wise absolutely nothing has been moved, emailed, copied, converted, edited or in anyway changed.... we have simply upgraded an exisiting workstation from Office 2003 to Office 2010 (and then applied the updates)........

If you've found this thread through google etc because you are experienceing the same please post as I get the feeling MS don't believe this is happening......

Thanks for all your help.

 

Hello,

I am sorry that you continue to experience this problem. As Wendalmsft explained in previous posts the issue is not easily reproduced. I am not able to reproduce the issue myself either. In researching I found that third party add-ins may be related. If you are running any third party add-ins, remove them entirely and test to see if the issue will occur.

These are the links for expected behavior of linking in Excel:
     284869 Universal naming convention path of files in shared XLSTART folder points to local folder
http://support.microsoft.com/default.aspx?scid=kb;EN-US;284869

     328440 Description of link management and storage in Excel
http://support.microsoft.com/default.aspx?scid=kb;EN-US;328440

Additionally, Office 2010 Service Pack 1 and October Cumulative Updates have been released. You could install and test SP1 then the Cumulative Updates to ensure your issue has not already been addressed.

If you continue to reproduce this issue from scratch, we would most definitely appreciate the opportunity to review the issue in more depth. Please open a support ticket with Microsoft to report the issue and work with an engineer directly.

http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone

Thank you!
Amber

• Proposed as answer by Amber Me - MSFTMicrosoft employee, Editor Thursday, December 15, 2011 5:17 PM

I have 'found this thread through google etc because  ..' I am experiencing a similar (but by no means identical) problem. I keep a program development diary in a Word document. The diary goes back three years, so I think it probably started in Word 2007. I am now using Word 2010 SP1.

The diary has links to many files and folders on my local machine where I have stored useful nuggets of information over the years. None of them are working at present, as their targets all point to my user AppData\Roaming\Microsoft folder rather than the original folders.

Sadly I have no idea when or how this happened. I have spent an hour or two researching, and came across this post among many others. It seems likely that there are pervasive problems within Office 2010, and I am posting in support of djstanley in the hope that MS will pursue the problem.

I just had all of my hyperlinks in Excel 2007 redirected to "C:\Documents and Settings\Application Data\Microsoft\Excel\....." The only thing I know is that yesterday I had my spreadsheet open when my system lost power so I was not able to close the file properly. I now have 5000+ invalid hyperlinks. I will most likely write a VB app to open the file, sort through each cell, retrieve the hyperlink, and then apply a new path. Fortunately I am a programmer so this should not be a big deal although it should not be necessary. I will ask this while I am here, why is it with the popularity of the iPod, iPad, iPhone, etc., people continue to buy pc's and continue to support Microsoft crap? Has MS support ever really helped anyone or do you just continue to get the "we can't duplicate" that excuse? I made the move to Mac at home and will never go back. I only wish we could do the same at work!  Good Luck all.

This issue to me seems to have started with Office 2007.  My wife has the same issue with Excel changing the path.  She has a very elaborate spreadsheet that is running macros which accesses another spreadsheet to get the data for the original spreadsheet.

In the original spreadsheet, the macro says this:

=IF(A4>=4000,VLOOKUP(A4,'C:\Users\%username%\Documents\Tax Year 2012 Documents\Tax Year 2012 Documents\[Client List2012.xls]Individuals-N'!$A$1:$D$898,3),VLOOKUP(A4,'C:\Users\%username%\Documents\Tax Year 2012 Documents\Tax Year 2012 Documents\[Client List2012.xls]Business-N'!$A$1:$C$353,3))

It gets changed to this:

=IF(A8>=4000,VLOOKUP(A8,'C:\Users\%username%\AppData\Roaming\Microsoft\Excel\Tax Year 2012 Documents\[Client List2012.xlsx]Individuals-N'!$A$1:$D$918,3),VLOOKUP(A8,'C:\Users\%username%\AppData\Roaming\Microsoft\Excel\Tax Year 2012 Documents\[Client List2012.xlsx]Business-N'!$A$1:$C$357,3))

Something within Office is changing the path to the AppData path. To fix it, she has to manually edit all lines in the spreadsheet so the macro will run correctly.  A royal pain. 

This is obviously a change in Microsoft code telling Excel where to get the data.  Like a profile change or something.

Microsoft Word has the same issues with "assuming" it knows what the user wants.  I do not like how Word just makes assumptions on how I want to format my document with double spacing, etc.

Microsoft, what is the fix for this?

Matt, I think you are correct.  Sorry I cannot find the reference, but I believe to have read where the suggestion is to place linked files in the same location and to make sure the location is added defined as a trusted location.

Dan,

Were you able to find a resolution to this issue?  I am seeing the identical issue at one of my clients.  

One of the things that I am wondering is if the links are broken due to Excel changing the links from drive letter to UNC path.  Excel will not let you use a drive letter for the trusted location and I think because of the difference in the way it is called, it is breaking the links.  It does not trust H:\ but will trust \\Server\HDrive.  While I understand the argument for an against UNC over drive letters, the fact that it is a problem remains.

I have tried some of the registry settings from previous versions to turn off the UNC path conversion feature, but have not found one that works with Excel 2010.

Thanks

David

Check that post again.  Sachel1 found an acceptable work around/solution.  Go into Excel, Options, Advanced.  Scroll down to the general tab and enter the start in path for your network documents.  This works for my customer and thankfully, they were saving their spreadsheets to the same root directory and we were able to make one update that fixed 1000+ sheets with links.

• Proposed as answer by DLang100 Thursday, July 12, 2012 1:45 PM

It appears that the Auto-Recovery feature is somehow involved, as it is the Auto-Recovery path that gets subbed into the links.  Can anyone verify if using the 'Disable AutoRecover for this workbook only' checkbox prevents future changes to the links?

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 linamishan 1 hour 57 minutes ago

AdrianPL,

Thanks for your post, this looks like it is what i need but I can't seem to create the macro, and get it to run thru the sheet.  I substituted in my paths for the SOURCE and the DESTINATION, but that didn't seem to work.  I don't get any errors.

here is my specific edit to your example, i created a excel macro and entered the following:

Sub RepairLinks()
Dim hLink As Hyperlink

For Each hLink In ActiveSheet.Hyperlinks
hLink.Address = Replace(hLink.Address, "c:\users\sw\AppData\Roaming\Microsoft\Excel\", "\\tesla\sales\proposal\")
Next hLink

End Sub

Any suggestions on how to make this Macro work would be appreciated. 

• Proposed as answer by linamishan 1 hour 57 minutes ago

Hi Geekbstrd,

To your first question about running the MACRO, make sure the file is saved as *.xlsm format.  *.xlsm is the new format used by Excel 2010 (near as I can tell) to run macros within the worksheets.

Our spreadsheets contained specific path names to a myriad of different network locations all within the same worksheet.  Column "K" was pure hyperlinks and multiple columns were set up this way.  I have provided a small alternate set of code below for your macro.  This code uses the text contained within each cell to update the hyperlink path.

intStart = starting row of hyperlinks, intEnd = last row of hyperlinks, strCol = column containing hyperlinks

The "For Each" option listed by AdrianPL would be a better looping mechanism.  It would remove the need for intStart and intEnd.  The "i" below that identifies the row would then need to be modified to pull from the active cell row number.

Sub Macro1()

' Macro1 Macro

Dim intStart As Integer

Dim intEnd As Integer

Dim strCol As String

intStart = 2

intEnd = 880

strCol = "K"

'

    For i = intStart To intEnd

    If Range(strCol & i).Value <> "" Then

        Range(strCol & i).Select

        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=Range(strCol & i).Value, SubAddress:= _

        "", TextToDisplay:=Range(strCol & i).Value

    End If

    Next i

End Sub

Hope this helps,

• Edited by bezlbug76 Monday, March 11, 2013 6:28 PM

@djstanley et al...

I am experiencing the same issue with one of my clients. In 2013 the client upgraded from Office 2002 & 2003 to Office 2010 across the board.

I have created a new workbook in Excel 2010 which did not exist in a previous version of Excel (it is saved as an Excel workbook - .xlsx, not a 97-2003 .xls compatible workbook).

In the workbook I maintain logs of activities related to a number of IT projects that I have to handover. Within the logs I have many hyperlinks to documents and files on a number of different network drives - including the same network drive as the workbook itself.

During the handover of a project to a new project manager, he informed me that the hyperlinks were not working and I have now identified that EVERY hyperlink within the document has been changed to my local drive (as described in above posts). This is very annoying and I have not identified the cause or a means to remedy it (perhaps I will by following this thread).

However, I have made a point of including the full path in the text of the hyperlinks, so that I have a rudimentary workaround (although it is very unprofessional) by copying and pasting the url to IE.

I have tried correcting some hyperlinks and resaving the document, which works until I close and reopen the workbook, where they are reset to my local drive as follows:

C:\Users\<userid>\AppData\Local\Microsoft\Windows\Temporary Internet Files\<file path>\<document name>

The <file path>\<document name> matches the last two sub-folder names and document name of the original target hyperlink, however, the drive path and sub-folders are replaced with the path to my Temporary Internet Files.

I find this very strange and wondered if there is an Excel setting somewhere that is pointing to Temporary Internet Files, that could lead to the cause of the substitution.

The other anomaly is that my target locations for the hyperlinks are on SharePoint 2007 and I have not read a previous post on this subject that mentions SharePoint. I initially thought that the problem was related to SharePoint, but having found this (and other) forum threads, it appears to be generally related to Excel 2010.

One last thing that is worth mentioning is that Excel has crashed a few times while I am editing this document (considering I keep the document open and update it every day, this is not surprising), but the problem may be connected to document recovery, which other posts have alluded too.

I hope we can identify a cause and remedy it, as it is extremely frustrating. I will keep reading the threads in the meantime.

I have the same complaint, consistently, all the time.

Exactly as stated by Matt. Word for word.

Mark

Hi GSeevers,

I have a similar issue as you do, both on the Excel as on the why do companies buy MS (!?!)

However, I am not a programmer and it leaves me desperate. My IT department is not helpful. After all they purchased MS Office...

What are the chances that you might share the VB code for me to adapt to my spreadsheets?

Cheers,

Mark

I'm having almost the same exact problem. Difference for me is that the URL is not auto updating within excel but it happens when I copy and paste the cell into another excel file or Word doc. Additionally if I go into the edit hyperlink function the address is truncated omitting the entire first half of the URL and replacing it with "../" The excel file is on a sharepoint server.

Can anyone help me how to link 2 excel files on FTP environment?

I have copied 2 excel workbook on FTP server. Both excel files are not updating on FTP server. If open 1 excel workbook its gives error Workbook 2.xls is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the files resides.

I have tried to change the path from local pc to FTP server but I am unable to do it.

Can anyone help how to assign network path?

• Edited by File Server stops working. Error ID 80042505 Sunday, October 19, 2014 7:06 AM

geekbstrd and AdrianPL,

Alhamdulillah. Thank you guys! You save me mass of time fixing the issue.

Lina