Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Change ALL Hyperlink Addresses in a Column?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Change ALL Hyperlink Addresses in a Column?
On Aug 15, 2:53*pm, (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 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Change ALL Hyperlink Addresses in a Column?
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 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Change ALL Hyperlink Addresses in a Column?
Check your other post.
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 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Change ALL Hyperlink Addresses in a Column?
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Change ALL Hyperlink Addresses in a Column?
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Change ALL Hyperlink Addresses in a Column?
On Mon, 17 Aug 2009 08:30:41 -0700 (PDT), Matthew Herbert
wrote: =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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Change ALL Hyperlink Addresses in a Column?
On Mon, 17 Aug 2009 08:30:41 -0700 (PDT), Matthew Herbert
wrote: = =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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Change ALL Hyperlink Addresses in a Column?
On Aug 17, 2:23*pm, (Terry) wrote:
On Mon, 17 Aug 2009 08:30:41 -0700 (PDT), Matthew Herbert wrote: = =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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How Can I Globally Change Hyperlink Addresses? | Excel Discussion (Misc queries) | |||
How do I change column addresses from numeric to alpha? | Setting up and Configuration of Excel | |||
How do I make a global change of Hyperlink addresses? | Excel Discussion (Misc queries) | |||
how to hyperlink a column of fileserver addresses? | Excel Worksheet Functions | |||
copy/convert column email addresses Hyperlink "mailto:" excel97 | Excel Discussion (Misc queries) |