multiple Hyperlink address change

[Guest]

I'm trying to create a macro which will simply take a changed hyperlink
address (i.e. "C:\Documents and Settings\XXXX\yyy.doc") and populate an
existing hyperlink in excel. The hyperlink is in one column and the hyperlink
address is in the adjacent column. I tryed to make a macro which would copy
the address contents (CTRL+C) and then select the adjacent hyperlink, select
"edit hyperlink" and then paste (CTRL+V) into the edit hyperlink window.
I have 2 columns of about 50 or so hyperlinks and corresponding addresses.
The links currently reflect the corresponding addresses and I wanted to
cretae a macro so that if the drive or folder cahnges I could do it more
automatically to each hyperlink.
When I record a macro now it just uses the specific example on each link, I
want it to copy the address for the cell it is in and paste it into the
hyperlink address window independently. I tried using the "Relative cell"
button but that does not seem to make a difference.
The other solution that I wanted to try is to use the "find/replace"
function on the hyperlinks but I guess it wont look at the hyperlink address,
only the displayed hyperlink text. (this woul dbe the easiest method if
someone knew how to have the "find/replace" function work on hyperlinks.
Thanks,
Jim

 

[Dave Peterson]

If you have the filenames in one column, how about an alternative:

I'd put the drive and folder in an out of the way cell (or hide that
column/row):

Say your filename data is in A2:Axx
Put this in A1:
C:\documents and settings\xxxx\
and hide row 1.

Then in B2, put this:
=hyperlink("file:////" & $a$1 & a2,"Click Me")
and drag down the column.

When/if the drive or folder changes, just change $A$1 to point at the new
location.

 

[Guest]

Here is a trick, use the HYPERLINK() function:

if the address is in A1, then in B1 enter:
=HYPERLINK(A1) and copy down


clearly Find/Replace will work on the text in column A and column B will
follow suite.