For a variety of reasons I need to share an Excel spreadsheet and a large number of photos between two computers and unfortunately the photos must be on different drives on the two machines (disk space reasons).
I though I could solve this problem by using either a system or user environment variable on each machine to specify the initial part of the path name for the photos (this is the part of the path which varies on the two machines). However it does not seem to work.
The code I have used is as follows
Environment variable AGScan value of variable E:\Data\Cardall Collection\AG Scans Backup
Hyperlink function value =HYPERLINK("%AGScan%\photos\"&B1251&".tif","photos\"&B1251&".tif")
The path of the photo is E:\Data\Cardall Collection\AG Scans Backup\photos\*.tif
This code works fine as =HYPERLINK("E:\Data\Cardall Collection\AG Scans Backup\photos\"&B1251&".tif","photos\"&B1251&".tif") so there is no basic problem with the parameters.
When I discovered this did not seem to work I though maybe I could solve the problem by setting the base hyperlink value in the worksheet properties based on an environment variable - however I could find no way of setting the base hyperlink value dynamically using an environment variable.
Does anyone have any suggestions as to how I could make either of these two approaches work, or perhaps an alternative approach.
Thanks for your help
Bernard