Force *relative* path in Insert Hyperlink dialog

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

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.

--
**** Kusleika
Excel MVP
Daily Dose of Excel
www.*****-blog.com

Mark Tangard wrote:
> 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

> 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

**** Kusleika wrote:
> 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.
>
-- **** Kusleika Excel MVP Daily Dose of Excel www.*****-blog.com Mark Tangard
wrote:

>> 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

Mark Tangard wrote:
>> 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?

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.


--
**** Kusleika
Excel MVP
Daily Dose of Excel
www.*****-blog.com