How do I use an environment variable within the Excel hyperlink function

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

Answer
Answer
There's no way to access environment variables from worksheet formulas. You'd need a user-defined function. Hold down an [Alt] key and press [F11] to display the Visual Basic Editor (VBE). Issue the VBE menu command Insert > Module, which should add a general VBA module to the workbook. Enter the following code in that module.

'-- begin ----
Function env(vn As String) As String
  env = Environ(vn)
End Function
'--  end  ----

Then use it in the Hyperlink formula as

=HYPERLINK(env("AGScan")&"\photos\"&B1251&".tif","photos\"&B1251&".tif")

13 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated November 8, 2023 Views 12,911 Applies to: