On Aug 17, 6:26*am, (Terry) wrote:
On Sat, 15 Aug 2009 15:15:01 -0700, ?B?QmFyYiBSZWluaGFyZHQ=?=

wrote:

=Are there already hyperlinks in the cells? * If so, changing the
displayed
=value doesn't necessarily change the hyperlinks. * Matthew's way
would work. *
=If you want a VBA solution, come back.
=="Terry" wrote:

=
=
= Excel 2003, XP Pro
=
= I have a single-sheet workbook with 600 and climbing entries in
col A,
= each with a hyperlink. The files in each link are now located on
an
= external drive so that I can work on building the sheet from
different
= computers. Eventually, all the files will be moved to a more
permanent
= location on a different computer, exact address unknown.
=
= My question is: how does one change the addresses of each
hyperlink in
= a global fashion? I really don't want to stroke in new addresses!
=
= Present Address: *file:///E:\FOLDER E1\TEMP QSL DATA\Data Filename
=
= Tentative Final Address: * *file:///C:\FINAL QSL DATA\Data
Filename
=

I'm back!

Answer to your question above, very few have hyperlinks now, for I
quit when I saw a problem (in my mind) of manually re-entering all
hyperlinks using the right-click/hyperlink method.

I wonder if the "Hyperlink Base" function could be used in conjunction
with concatenating the text in the cell, which is the filename, and
then adding ".jpg"?

Something like: Base Hyperlink + filename in cell + .jpg

For example, I am now working from a portable external drive E:\
entering the filename in col A as, say, B6TYY. The actual filename to
which I would like to link (presently while I'm working on the
project) is "file:///E:\folder1\folder2\B6TYY.jpg".

The path above will ultimately have to be changed to something on a
C:\ drive when I'm finished. Could this be done by changing the base
hyperlink?

I don't know how to do this in VB, if it could be done. Could you
help?

TIA--
Terry--WB4FXD
Edenton, NC

Terry,

This can be done in VBA, but you don't need VBA because Excel formulas
will be faster. Create an anchor cell to hold the Base Hyperlink,
create another anchor cell to hold the file extension, and create a
cell to hold the filename. Of course, this is assuming you have a
spreadsheet with all of your file names in it. (If you are wanting to
get the file name list from, say, a folder, then this is a different
issue). Build the string and then embed the string in the HYPERLINK
function. For example, see below.

A1: Base Hyperlink Text
B1: file extension
A2: start of the file names

A1: E:\folder1\folder2\
B1: .jpg
A2: B6TYY
A3: next file name
A4: next file name
A5: etc.

B2: =$A$1&A2&$B$1
B3:B(end): copy the B2 formula down

C2: =HYPERLINK(B2)
C3:C(end): copy the C2 formula down

If A1 needs the preceeding "///" then add it into the cell. When you
are ready to switch from E: to C:, simply change cell A1, calculate
the cells, and you're done.

If everythink looks good and is working, then you can combine both the
HYPERLINK and concatenated formulas into one.

B2: =HYPERLINK($A$1&A2&$B$1)
B3:B(end): copy the B2 formula down

Best,

Matt