Force *relative* path in Insert Hyperlink dialog

[Mark Tangard]

Googling this question turned up a lot of people with the *opposite*
problem, so I must be overlooking something obvious. The issue arises
on 2 different PCs, running Excel 2000 and 2003, both with WinXP.

I have several hundred files to be hyperlinked from a spreadsheet. When
done they're all destined for a CD. So I figure we need all the
hyperlink paths to be relative, that is, the address field for each link
should be in the form \path\stuff.txt and not D:\path\stuff.txt (nor any
other letter plus :\stuff.txt).

But no matter what I enter in 'Hyperlink Base' in File-> Properties->
Summary, when I pick a cell in the sheet, click Insert-> Hyperlink, and
BROWSE to a file to be linked, that file's full ABSOLUTE path gets inserted.

Is there a way to disable that? I see tons of posts saying you can
*type* the file's relative path, but oddly enough, nobody mentions how
convenient it would be if you could make the Browse button automatically
OMIT the drive prefix, i.e., automatically insert said relative path.
That's why I'm convinced something obvious has escaped me. Is there no
way to make it do that?

I know I can de-absolute all the links at once afterward with a little
macro that cycles through them & applies the VBA Replace function, but
that seems like sort of a backward way to do it. Am I crazy? (Shh!)

If the answer is bonehead simple, I promise to slink quietly back into
my cave.

TIA!!
Mark Tangard
"Life is nothing if you're not obsessed." --John Waters

 

[Dick Kusleika]

Mark

If you leave the hyperlink base blank, then all hyperlinks will be relative
to the workbook that contains them - with a few exceptions. If the file is
on a different drive or network share, the path will be absolute because
there's no way show a relative path across drives.

Also, note that if the workbook to which you are inserting hyperlinks is
unsaved, the hyperlink path will be absolute until you save it. That's
because the workbook doesn't have a path to which to be relatve. Once you
save the workbook, however, the path will become relative. The
TextToDisplay will NOT become relative in that situation, though, so be sure
you're looking at the Address property of the hyperlink and not just what is
shows in the cell.

If that doesn't answer your question, can you give me more details about
where your files are and what kind of results you're getting. I'll try to
reproduce it.

 

[Mark Tangard]

If you leave the hyperlink base blank, then all hyperlinks will be relative

to the workbook that contains them - with a few exceptions.

Gawd. Didn't realize it'd be *that* simple. Thanks so much for recognizing my
blind spot. But, what are the exceptions?

Mark Tangard

Mark

If you leave the hyperlink base blank, then all hyperlinks will be relative
to the workbook that contains them - with a few exceptions. If the file is
on a different drive or network share, the path will be absolute because
there's no way show a relative path across drives.

Also, note that if the workbook to which you are inserting hyperlinks is
unsaved, the hyperlink path will be absolute until you save it. That's
because the workbook doesn't have a path to which to be relatve. Once you
save the workbook, however, the path will become relative. The
TextToDisplay will NOT become relative in that situation, though, so be sure
you're looking at the Address property of the hyperlink and not just what is
shows in the cell.

If that doesn't answer your question, can you give me more details about
where your files are and what kind of results you're getting. I'll try to
reproduce it.

-- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com Mark Tangard
wrote:

 

[Dick Kusleika]

Gawd. Didn't realize it'd be *that* simple. Thanks so much for
recognizing my blind spot. But, what are the exceptions?

The ones I mentioned: Unsaved workbook results in absolute links until you
save it; and links on a different drive or path. There may be more
exceptions, but those are the two I know.