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

rfs0123

New Member
Joined
Feb 6, 2010
Messages
30
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

edelsal

New Member
Joined
Jul 15, 2011
Messages
1
...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

New Member
Joined
Feb 6, 2010
Messages
30
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

Code:
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
 

Forum statistics

Threads
1,144,575
Messages
5,725,088
Members
422,590
Latest member
Mikeyyy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top