Using Relative Hyperlinks

C

Chaim

How would one set up relative hyperlinks in Excel? For example, if the links
refer to a file system directory, so that the whole hyperlink would be <base
directory>/<variable part of filename>, how could one set things up so that
if the <base directory> were to change, one could either change one value or
change all of the hyperlinks at one shot?

Let's say that <base directory> = "c:\my files\html base\" and <variable
....> = "html_file1.htm", "html_file2.htm", etc. How would I change the
"c:\my files\html base\" to "c:\my files\other html base\" with one edit?
And how do I set up relative hyperlinks in the spreadsheet?

I have very minimal Excel knowledge/experience. So please keep things real
simple for me, if possible.
 
D

Dave Peterson

I'm not sure if this option is available in all versions of excel. It's there
in xl2002+, though.

Look at file|properties|summary tab
Do you see "hyperlink Base" at the bottom of that dialog?

If you do, then you can search excel's help for "hyperlink base" to see how it
works.

And if you need to change that base, you could use code like:

Thisworkbook.BuiltinDocumentProperties("Hyperlink Base") _
= "whateveryouwanthere"

or

ThisWorkbook.BuiltinDocumentProperties("Hyperlink Base") = ThisWorkbook.Path

If you put it in a general module and name the procedure Auto_open(), it'll run
each time you open the file.

Option Explicit
sub Auto_open()
Thisworkbook.BuiltinDocumentProperties("Hyperlink Base") = ThisWorkbook.Path
end sub

I don't use this kind of hyperlink very often.

There is an =hyperlink() worksheet function that may work for you.

You can put parts of the string in other cells and build up the link:

For example, you could put:
file:////c:\my files\
in A1

Then put the variable names in A2:Axxx.

Then in B2, put:
=hyperlink($a$1&a2)
and drag down the column.

Changing $a$1 would adjust all the =hyperlink() formulas.
 
C

Chaim

Dave,

Thanks for the reply.

The office I'm in has Excel 2000 and it has the Hyperlink Base property.
However, it appears that this is used with the Hyperlink Editor. I'd rather
not go with an option that requires code; I'd do it, but the other users are
not near capable/competent.

The hyperlink() function idea worked great. One thing that I couldn't
understand though, was your comment about "and drag down the column." I put
the "=hyperlink(etc.)" in just one cell? And then what does it mean to 'drag
down the column'? I know you mean a mouse drag, but what should I see? Will
this copy the formula to each cell? Or will this cause it to be applied to
each cell?

Sorry. As I said, I have minimal Excel knowledge/experience.
 
D

Dave Peterson

When you select a cell, do you see a little box in the bottom right corner?

If you don't, you can toggle a setting:
Tools|Options|edit tab|allow cell drag and drop

Then you can click on this little autofill box and drag down the column to copy
what's in that cell (formula or constant).

Debra Dalgleish describes it with pictures at:
http://contextures.com/xlDataEntry01.html#Mouse


Dave,

Thanks for the reply.

The office I'm in has Excel 2000 and it has the Hyperlink Base property.
However, it appears that this is used with the Hyperlink Editor. I'd rather
not go with an option that requires code; I'd do it, but the other users are
not near capable/competent.

The hyperlink() function idea worked great. One thing that I couldn't
understand though, was your comment about "and drag down the column." I put
the "=hyperlink(etc.)" in just one cell? And then what does it mean to 'drag
down the column'? I know you mean a mouse drag, but what should I see? Will
this copy the formula to each cell? Or will this cause it to be applied to
each cell?

Sorry. As I said, I have minimal Excel knowledge/experience.
Click to expand...
 
C

Chaim

Thanks much. That was very useful.

And thanks for following this. Since it dates back to last week, I thought
it likely that this would have dropped off everyone's radar. I'm pleasantly-
and gratefully- surprised.
 
Top