How to Change ALL Hyperlink Addresses in a Column?

[Terry]

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 am not a programmer and I know just enough about Excel to be
dangerous! I would truly appreciate any solutions, especially in the
hand-holding department!

TIA--
Terry--WB4FXD
Edenton, NC

 

[Matthew Herbert]

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 am not a programmer and I know just enough about Excel to be
dangerous! I would truly appreciate any solutions, especially in the
hand-holding department!

TIA--
Terry--WB4FXD
Edenton, NC

Terry,

Why not create a "prefix" string in one cell which contains the folder
path? You can then concatenate this prefix and the file name for your
hyperlink. So, if A1 were the prefix and A2 were the file name then
you could create a function in B2 that looks something like the
following: =$A$1&A2. Once the file path looks the way you want you
can embed it in the HYPERLINK function. You can then copy this
formula down for all of your files. This way, all you need to do is
change the "prefix" in A1 and calculate the cells (F9 if calculation
is manual).

Best,

Matthew Herbert

 

[Barb Reinhardt]

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.

 

[Dave Peterson]

Check your other post.

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 am not a programmer and I know just enough about Excel to be
dangerous! I would truly appreciate any solutions, especially in the
hand-holding department!

TIA--
Terry--WB4FXD
Edenton, NC

 

[Terry]

On Sat, 15 Aug 2009 15:15:01 -0700, =?Utf-8?B?QmFyYiBSZWluaGFyZHQ=?=

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

 

[Matthew Herbert]

On Sat, 15 Aug 2009 15:15:01 -0700, =?Utf-8?B?QmFyYiBSZWluaGFyZHQ=?=


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

 

[Terry]

On Mon, 17 Aug 2009 08:30:41 -0700 (PDT), Matthew Herbert

=>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: =3D$A$1&A2&$B$1
=>B3:B(end): copy the B2 formula down
=>
=>C2: =3DHYPERLINK(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: =3DHYPERLINK($A$1&A2&$B$1)
=>B3:B(end): copy the B2 formula down
=>
=>Best,
=>
=>Matt

OK--I think I see what the syntax is and I'll give'er a try. Thanks so
much for taking the time to educate an old man!! I'll get back to
you.

Cheers--

Terry--WB4FXD
Edenton, NC

 

[Terry]

On Mon, 17 Aug 2009 08:30:41 -0700 (PDT), Matthew Herbert

=>
=>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: =3D$A$1&A2&$B$1
=>B3:B(end): copy the B2 formula down
=>
=>C2: =3DHYPERLINK(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: =3DHYPERLINK($A$1&A2&$B$1)
=>B3:B(end): copy the B2 formula down
=>
=>Best,
=>
=>Matt

That did it, Matt. Thanks a bunch. I had to delete "3D" fom the
expresions to get it running. (B2: =3DHYPERLINK($A$1&A2&$B$1)) I have
a feeling it was inserted by Free Agent, my news reader.

This is just what I was looking for, and you have saved me untold
hours, and errors, of typing!! I think I'll devote some time to
learning the syntax required! Might save us all a lot of time...

Thanks again!

Cheers--



Terry--WB4FXD
Edenton, NC

 

[Matthew Herbert]

On Mon, 17 Aug 2009 08:30:41 -0700 (PDT), Matthew Herbert


=>
=>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: =3D$A$1&A2&$B$1
=>B3:B(end): copy the B2 formula down
=>
=>C2: =3DHYPERLINK(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: =3DHYPERLINK($A$1&A2&$B$1)
=>B3:B(end): copy the B2 formula down
=>
=>Best,
=>
=>Matt

That did it, Matt. Thanks a bunch. I had to delete "3D" fom the
expresions to get it running. (B2: =3DHYPERLINK($A$1&A2&$B$1)) I have
a feeling it was inserted by Free Agent, my news reader.

This is just what I was looking for, and you have saved me untold
hours, and errors, of typing!! I think I'll devote some time to
learning the syntax required! Might save us all a lot of time...

Thanks again!

Cheers--

Terry--WB4FXD
Edenton, NC

Terry,

Glad to be of help.

Matt