Relative hyperlinks help
 

Hi,

I'm using VBA to automatically create links to a number of files using
the following code:

For Each cell In Selection

....

path = "\" + docType + "\" + docFolder + "\" + docYear + "\" + docRef +
"." + extension

If Len(Dir(fullPath)) 0 Then
address = Replace(path, "\", "/")
ActiveSheet.Hyperlinks.Add Anchor:=cell, _
address:="Data/" & address, _
TextToDisplay:="View"

....

where the files are stored in various subdirectories (depending on the
document type) of a folder called 'Data'. The 'Data' folder is in the
same folder as the spreadsheet.

When I run the macro the links, although working, are returned as
absolute links beginning with "File:///C:\....\Data\..." and not the
relative links input within the code. When I move the files to a
different folder with the same structure the links stop working.

I eventually need to put the spreadsheet and files onto a CD and so
need the links to be relative to the spreadsheet's directory.

I've left the hyperlink base path empty in the spreadsheet properties.
How do I go about making the address be forced as being relative in
VBA.

Thanks for any help,
Chris