VBA to refresh data links / connected to another workbook via shared network drive

[Aton999]

Hi,

My workbook relies on values from other 5 workbooks saved on a network drive. The issue is that when someone tries to use the same workbook, they will need to go an update each link as the mapping drive letter varies between us. Therefore, I would like to add the ability to replace drive letters in the "Edit Links" dynamically.

I wonder if there is a VBA code to help please?

Many thanks, aton

 

[Anthony47]

I suggest that instead than Drive Letters you use the UNC, that typical looks like
"\\192.168.0.3\ShareName\Path\FileName.xlsx"
Example of using UNC in a formula:

=VLOOKUP(J2,'\\192.168.0.8\ShareName\Path\[MY-FILE_IN PROGRESS.xlsx]Sheet1'!$A$4:$J$17,6,0)

Your Network Administrator would know the resources UNC
Bye