Excel 2007 changes external link drive formula references if file is loaded from the network

[rfs0123]

I have a large worksheet with approximately 30 or so worksheets that we use for estimating large HVAC projects. I have a couple of external files that I use to easily update varying labor rates and to customize the large worksheet to each user's range of local or network printers (standard, color. ledger, etc.) These external files are all saved on the users' "C:" drive and in an identically named folder. For the most part this works out great if the file is loaded from the users hard drive, but if the file is copied to a network drive and loaded, excel changes the external reference formula(s) from something like this ='C:\ESTTEMP\Projections\Local 537\[One.XLS]A'!$K$50 to ='M:\ESTTEMP\Projections\Local 537\[One.XLS]A'!$K$50. This of course fails since there is no such folder on the M drive and you get the failed link popup.

I thought that it might be as a result of the cells and sheet being 'protected', so I unprotected, but it still occurs. I also listed the folder used on the user's C: drive in the trusted location section.

Any ideas? If there is no apparent solution, I suppose I could have all references to the external file drive/folder go to one internal cell, then replace all of the formula with that one range name reference. At least I'd only have to change the info one location.

Thank you,

Bob

 

[edelsal]

...For the most part this works out great if the file is loaded from the users hard drive, but if the file is copied to a network drive and loaded, excel changes the external reference formula(s) from something like this ='C:\ESTTEMP\Projections\Local 537\[One.XLS]A'!$K$50 to ='M:\ESTTEMP\Projections\Local 537\[One.XLS]A'!$K$50. This of course fails since there is no such folder on the M drive and you get the failed link popup.

Thank you,

Bob

Wow, no answer to this post in a long time... I am havingthis same problem now, and cannot figure out how to make this work properly so that the drive letter does not change when the file is copied to a different drive (in our company all the network drives are named the same for everyone, all he network drives are mapped in each machine the same way)

Thanks,

Edelsal

 

[rfs0123]

Yeah, I was surprised that no one else had this problem. Since I had over 200 cells where the formula referred to two other files on the user's 'C' drive, I decided to hard code the formula's using VBA in conjunction with a changeable LinkDrive and LinkFolder cell in the worksheet. The LinkDrive cell ONLY has a single letter in it (ie 'C'), so hopefully Excel wouldn't be dumb enough to think that it was a drive reference and change it.

This exercise was surprisingly simple using Notepad++, then pasting it into the VBE.

I've attached a sample of the code I used, which was activated when called from my AutoOpen code. One.XLS is one of eight files with a bunch of different labor rates, etc.

Works great, plus I placed a button on the Labor spreadsheet that runs this routine when labor rates change without having to close the workbook and reopen.

I also use similar code to set everyone's different network printer selections (standard, color and ledger) so that the various printouts in the workbook printout correctly.


Bob

Sub SetupFormulaLinks()

With Sheets("Labor Rates")
    .Unprotect
    .Range("c6").Formula = "='" & [linkdrive] & ":" & [LinkFolder] & "[One.XLS]A'!$D$4"
    .Range("c8").Formula = "='" & [linkdrive] & ":" & [LinkFolder] & "[One.XLS]A'!$K$50"
    .Range("c12").Formula = "='" & [linkdrive] & ":" & [LinkFolder] & "[One.XLS]A'!$K$103"
    .Range("c16").Formula = "='" & [linkdrive] & ":" & [LinkFolder] & "[One.XLS]A'!$K$156"

    .Protect
    
End With
    
End Sub

 

[shg]

Much discussion on this topic at https://social.technet.microsoft.co...m-an-unc-path-a-bug-in-excel-2010?forum=excel. See also Description of link management and storage in Excel