Hyperlink Relative Reference

[TKS_Mark]

I'm using about 100 3D pdf instruction files that are automatically linked in
a spreadsheet. The spreadsheet is small, but the instructions are large.
And the spreadsheet gets copied from project to project (folder to folder).
I don't want to clutter the server with these instructions plus when I'm
updating the instructions, I only want to do it once so everyone has access
to them instantly.

Is there any way to make the hyperlink function relative? In other words,
when I click on it, it will go to the root directory of the drive where the
current file is (p:\) and the up to a set location (P:\Instructions)? It
shouldn't matter how many folders deep the current file is. It just needs to
go up to the top and then down from there. And it can't matter what
different users name the drive, plus there are multiple servers in different
offices, so the root directory name will be variable.

Maybe this is too much for this site to handle, but I thought I would try.

Thanks!

 

[Tom Hutchins]

This user-defined function (UDF) returns the drive where ThisWorkbook (the
workbook with the code) is saved. It can return a letter (D if the drive is
mapped that way, or the server path (\\rwkflp06.il.mycompany.com\thutch). It
should work either way. If you create your hyperlinks using the HYPERLINK
function, you can call the UDF like this:

=HYPERLINK(GetDrive() & "\Instructions\Help1.pdf","Display this text")

Here is the UDF, which needs to be pasted into a VBA module in your workbook:

Public Function GetDrive() As Variant
Dim fs, f, s
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(ThisWorkbook.FullName)
GetDrive = f.Drive
End Function

If you are new to user-defined functions (macros), this link to Jon
Peltier's site may be helpful:
http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-elses-macro/

Hope this helps,

Hutch

 

[TKS_Mark]

Tom,

I only just now reprogrammed our template using your suggestion. This works
great. Thanks!

Mark.