On Aug 17, 6:26*am, katamaso...@hotmail.com (Terry) wrote:
> On Sat, 15 Aug 2009 15:15:01 -0700, =?Utf-8?B?QmFyYiBSZWluaGFyZHQ=?=
>
> <BarbReinha...@discussions.microsoft.com> 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
|
Similar Threads
