Hyperlink base

[Chris Beall]

Excel 2000

In one column of a spreadsheet I have hyperlinks to .jpg images. During
development of the sheet, these links simply contained file names, since
the images were in the same directory as the sheet on my system.

I then uploaded the sheet (.xls) to a web site, for viewing by others.
Although the same .jpg images were also updated, the links didn't work
for other users, since they were running the sheet on their own machines
and didn't have those images.

So I modified the sheet by placing the proper path information in File
-> Properties -> Summary -> Hyperlink base:. This solved the problem:
when another user loads the spreadsheet, the hyperlinks all point to the
web-site images.

BUT, I expected this to work like the HTML <Base> element, i.e. I
expected it to be a reversible process, with the actual links unchanged,
but the base hyperlink prepended to them at run time. This appears not
to be the case.

While I'm making changes to the sheet, I want all these links to again
point to the local versions of the files (for performance reasons), so I
erased the Hyperlink base: information, expecting the original, local
filename, links to be unmodified. Instead, erasing the Hyperlink base:
data has had NO EFFECT on the links, they still all contain full URLs.
(I saved and reloaded the spreadsheet after making the change).

Is the specification of a Hyperlink base: really a non-reversible
process? If no, how do I reverse it?

Chris Beall

 

[Gary''s Student]

You gain much better control by using the HYPERLINK() function:

=Hyperlink("http://www.cnn.com","news")

Because the link address is just a string constant, you are in complete
control.

 

[Chris Beall]

You gain much better control by using the HYPERLINK() function:

=Hyperlink("http://www.cnn.com","news")

Because the link address is just a string constant, you are in complete
control.

GS,

OK, but that leaves two problems:
- Lots more keystrokes to enter a link (actually, almost infinitely
more. If you Insert -> Hyperlink, then, while the popup window is open,
navigate to the image you want to point to, its filename is
automatically plugged in to both fields of the hyperlink dialog. No
typing required, unless you want the displayed text to be different, and
no chance for errors.)
- While possibly useful for the future, not a solution for an existing
sheet with 2100 rows.

Thanks for trying, though. See my other reply for how I 'solved' the
problem.

Chris Beall

 

[Chris Beall]

Excel 2000

In one column of a spreadsheet I have hyperlinks to .jpg images. During
development of the sheet, these links simply contained file names, since
the images were in the same directory as the sheet on my system.

I then uploaded the sheet (.xls) to a web site, for viewing by others.
Although the same .jpg images were also updated, the links didn't work
for other users, since they were running the sheet on their own machines
and didn't have those images.

So I modified the sheet by placing the proper path information in File
-> Properties -> Summary -> Hyperlink base:. This solved the problem:
when another user loads the spreadsheet, the hyperlinks all point to the
web-site images.

BUT, I expected this to work like the HTML <Base> element, i.e. I
expected it to be a reversible process, with the actual links unchanged,
but the base hyperlink prepended to them at run time. This appears not
to be the case.

While I'm making changes to the sheet, I want all these links to again
point to the local versions of the files (for performance reasons), so I
erased the Hyperlink base: information, expecting the original, local
filename, links to be unmodified. Instead, erasing the Hyperlink base:
data has had NO EFFECT on the links, they still all contain full URLs.
(I saved and reloaded the spreadsheet after making the change).

Is the specification of a Hyperlink base: really a non-reversible
process? If no, how do I reverse it?

Chris Beall

FUP:

Apparently, the answer is, Yes, specification of a hyperlink base is
non-reversible. In fact, it doesn't seem to be possible to later change
the specified base (it changes in the Properties, but not in existing
links). This is, IMHO, incredibly stupid.

To recover, I created a new spreadsheet with the same column widths as
the original (actually, I already had such a 'template' spreadsheet). I
then selected all of the data in the damaged sheet, did an Edit -> Copy,
selected cell A1 of the template, did an Edit -> Paste, then saved the
result.

The resulting spreadsheet contains the original relative links, i.e. the
Hyperlink base info was NOT copied to the new spreadsheet, nor was the
the 'absolute links' state. Thank goodness.

So now I will continue to develop my sheet using relative links. Once
it is ready to appear on the web, I'll make a copy, set the hyperlink
base approprately, and upload that version to the web, retaining the
version with relative links for future updates.

This is the sort of thing that keeps me from being a MS supporter.

Chris Beall

 

[Gary''s Student]

How about a hybrid approach to solve BOTH your problems. You are correct.
It is easier to create the links using Insert, but the HYPERLINK function
gives better control:

1. Enter your links using Insert
2. Enter and run the following small macro:

Sub Chris()
' gsnuxx
s = Chr(34)
s0 = "=hyperlink("
Dim h As Hyperlink
For Each h In ActiveSheet.Hyperlinks
s1 = s & h.Name & s & ")"
s2 = s & h.Address & s & ","
Set s3 = h.Parent
s3.Clear
s3.Formula = s0 & s2 & s1
Next
End Sub

This routine will convert all your inserted links into function calls.

It is now easy to change drive addresses, folder addresses or URL addresses
with just Find/Replace.

 

[Chris Beall]

How about a hybrid approach to solve BOTH your problems. You are correct.
It is easier to create the links using Insert, but the HYPERLINK function
gives better control:

1. Enter your links using Insert
2. Enter and run the following small macro:

Sub Chris()
' gsnuxx
s = Chr(34)
s0 = "=hyperlink("
Dim h As Hyperlink
For Each h In ActiveSheet.Hyperlinks
s1 = s & h.Name & s & ")"
s2 = s & h.Address & s & ","
Set s3 = h.Parent
s3.Clear
s3.Formula = s0 & s2 & s1
Next
End Sub

This routine will convert all your inserted links into function calls.

It is now easy to change drive addresses, folder addresses or URL addresses
with just Find/Replace.

GS,

OK, this looks interesting. I may stick with my current workaround for
this worksheet, but I'll keep this in mind as I may need it in the future.

BUT, I've never seen VB before, so let's see if I can understand your macro:

' Start a subroutine, no parameters needed. Name it Chris.

Sub Chris()

' Cryptic commentary to identify this macro.

' gsnuxx

' Define a variable containing the double-quote character.

s = Chr(34)

' Set a string to invoke the Excel hyperlink function.

s0 = "=hyperlink("

' Declare h as a variable of type hyperlink.
' The hyperlink type defines (among others) objects
' Address (string), Name (read-only string), and Parent (Object).

Dim h As Hyperlink

' Scan all the current hyperlinks

For Each h In ActiveSheet.Hyperlinks

' Set s1 to a string consisting of the Name within the hyperlink, inside
double quotes and with a trailing right parenthesis (which will
eventually close the Hyperlink function call).

s1 = s & h.Name & s & ")"

' Set s2 to a string consisting of the Address within the hyperlink,
inside double quotes and with a trailing comma to separate it from the
later Name.

s2 = s & h.Address & s & ","

' Set s3 to the parent object within the hyperlink.
' I'm not clear on this one. Based on what follows, it points s3 to the
cell that contains the current hyperlink, that cell thus being the
hyperlink's parent.

Set s3 = h.Parent

' Clear the cell of its current contents.

s3.Clear

' Set the cell contents to '=hyperlink("Address","Name")' (without the
single quotes).

s3.Formula = s0 & s2 & s1 ' Do them all.
Next ' Return to caller or user.
End Sub

Is my understanding correct?

Thanks,
Chris Beall

 

[Gary''s Student]

Your understanding is correct. Try it if you like on a trial worksheet with
some Inserted hyperlinks on it:


Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the macro from Excel:

1. ALT-F8
2. Select the macro
3. Touch RUN

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm