If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How Can I Globally Change Hyperlinks?
I seem to know just enough about Excel to be dangerous, and therein lies my question. I have a spreadsheet (1 sheet) that has some 600 (and climbing) rows, one column of each having a hyperlink to a file on an external drive. Eventually, all the linked files will be moved to another disk and the links changed to that new drive's location. Is there a way to globally change the links to the other disk when the project is completed. I have stopped adding links until I discover a way, for I really don't want to do the changes manually one at a time! Thanks for any advice. Cheers--Terry Terry--WB4FXD Edenton, NC |
#2
|
|||
|
|||
How Can I Globally Change Hyperlinks?
My bad! It's Excel 2003 I'm using. Sribouthat! Cheers-- Terry--WB4FXD Edenton, NC |
#3
|
|||
|
|||
How Can I Globally Change Hyperlinks?
I like to use the =hyperlink() worksheet function for this.
I'll put the path in a hidden cell (A1 in my example). (Include the trailing backslash, too!) Then put the filenames in A2:Axxx Then use the =hyperlink() formula in B2:Bxx. =hyperlink("File:////"&$a$1&a2,"Click me") and drag down Then when I need to change the folder, I can change it one location (A1). I could embed the path directly in the formula: =hyperlink("File:////c:\my documents\excel\"& a2,"Click me") and use Edit|Replace, but that seems like more work to me. ======== If you used Insert|Hyperlink, then you've noticed that edit|replace won't touch those hyperlink addresses. Saved from a previous post: If you used Insert|hyperlink (xl2003 menus), you'll have more work to do. But the good news is David McRitchie has done most of it for you: http://www.mvps.org/dmcritchie/excel/buildtoc.htm look for: Fix Hyperlinks (#FixHyperlinks) If you're new to macros: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) Terry wrote: I seem to know just enough about Excel to be dangerous, and therein lies my question. I have a spreadsheet (1 sheet) that has some 600 (and climbing) rows, one column of each having a hyperlink to a file on an external drive. Eventually, all the linked files will be moved to another disk and the links changed to that new drive's location. Is there a way to globally change the links to the other disk when the project is completed. I have stopped adding links until I discover a way, for I really don't want to do the changes manually one at a time! Thanks for any advice. Cheers--Terry Terry--WB4FXD Edenton, NC -- Dave Peterson |
#4
|
|||
|
|||
How Can I Globally Change Hyperlinks?
On Tue, 11 Aug 2009 14:08:05 -0500, Dave Peterson
wrote: =I like to use the =hyperlink() worksheet function for this. Wow! Thanks, Dave, this looks like the mother lode of linking information. I've printed your post and will start looking at sources. Company at the house now and I dast not desert them for fear of incurring the wrath of She Who Must be Obeyed...!! Many thanks again. Cheers-- Terry--WB4FXD Edenton, NC |
#5
|
|||
|
|||
How Can I Globally Change Hyperlinks?
|
#6
|
|||
|
|||
How Can I Globally Change Hyperlinks?
So you used the insert|hyperlink version of hyperlinks, right?
This is from David McRitchie's site: Sub Fix192Hyperlinks() Dim OldStr As String, NewStr As String OldStr = "http://192.168.15.5/" NewStr = "http://hank.home.on.ca/" Dim hyp As Hyperlink For Each hyp In ActiveSheet.Hyperlinks hyp.Address = Replace(hyp.Address, OldStr, NewStr) Next hyp End Sub Try something like: Option Explicit Sub Fix192Hyperlinks() Dim OldStr As String, NewStr As String OldStr = "E:\Cards\All Cards-For Linking\" NewStr = "C:\Cards\SpecialBatch\ALL CARDS-FOR LINKING\" Dim hyp As Hyperlink For Each hyp In ActiveSheet.Hyperlinks hyp.Address = Replace(expression:=hyp.Address, _ Find:=OldStr, _ Replace:=NewStr, _ compa=vbTextCompare) Next hyp End Sub I added the keywords in the replace line and the compare parm. If you're not familiar with VBA, take a look at those sites in the earlier post. Terry wrote: On Wed, 12 Aug 2009 17:35:47 GMT, (Terry) wrote: =On Tue, 11 Aug 2009 14:08:05 -0500, Dave Peterson wrote: = ==I like to use the =hyperlink() worksheet function for this. = =Wow! Thanks, Dave, this looks like the mother lode of linking =information.... Dave:-- My ignorance of Excel and VB is obvious! I tried everything in your post and nothing worked. Gloom... Here's the present situation: Excel 2003 Sheet with filenames in A6:A619, each referring to file by that name. Hyperlink now for a file is: file:\\\E:\Cards\All Cards-For Linking\filename.jpg New desired link is: file:\\\C:\Cards\SpecialBatch\ALL CARDS-FOR LINKING\filename.jpg If I manually change one for the other, all is well, but there must be an easier way. Find/replace works OK when the link info is displayed as text, but not if just the filename is displayed. If you have the time, could you take pity on me and show me what to do? TIA-- Terry--WB4FXD Edenton, NC -- Dave Peterson |
#7
|
|||
|
|||
How Can I Globally Change Hyperlinks?
On Thu, 13 Aug 2009 12:00:09 -0500, Dave Peterson
wrote: =So you used the insert|hyperlink version of hyperlinks, right? Yes =This is from David McRitchie's site: = =Sub Fix192Hyperlinks() = Dim OldStr As String, NewStr As String = OldStr = "http://192.168.15.5/" = NewStr = "http://hank.home.on.ca/" = Dim hyp As Hyperlink = For Each hyp In ActiveSheet.Hyperlinks = hyp.Address = Replace(hyp.Address, OldStr, NewStr) = Next hyp =End Sub I tried this--no joy =Try something like: =Option Explicit =Sub Fix192Hyperlinks() = Dim OldStr As String, NewStr As String = OldStr = "E:\Cards\All Cards-For Linking\" = NewStr = "C:\Cards\SpecialBatch\ALL CARDS-FOR LINKING\" = Dim hyp As Hyperlink = For Each hyp In ActiveSheet.Hyperlinks = hyp.Address = Replace(expression:=hyp.Address, _ = Find:=OldStr, _ = Replace:=NewStr, _ = compa=vbTextCompare) = Next hyp =End Sub And this, too. Still no joy. I entered a file name, added a hyperlink through the Edit function, and made sure the link was correct. I changed the link to the new link and checked it, too. OK. Then changed the link back to the old link. Next, I put the code in the VB edit window by right-clicking "Sheet 1", selecting "View Code", and typing in the code. I returned to the sheet through "File"/"Close...and Return..." Old link still there. Back to the code. F5 to run. Some crunching somewhere I think... Back to the sheet. No joy. Still the old adress. Was this the way to do it? Groan again + Help! Be patient--I'm old! Cheers-- Terry--WB4FXD Edenton, NC |
#8
|
|||
|
|||
How Can I Globally Change Hyperlinks?
The code should be placed in a General module (not behind the worksheet).
Inside the VBE Insert|Module And move the code there. Then double check to make sure you have the old link path correct in the code (as well as the new link path). Then select the worksheet with the links (the code uses the ActiveSheet). Then hit alt-F8 and run the macro. ps. Make sure your slashes match what you used in the hyperlinks. Hyperlinks can be pretty forgiving, but string comparisons aren't! Terry wrote: On Thu, 13 Aug 2009 12:00:09 -0500, Dave Peterson wrote: =So you used the insert|hyperlink version of hyperlinks, right? Yes =This is from David McRitchie's site: = =Sub Fix192Hyperlinks() = Dim OldStr As String, NewStr As String = OldStr = "http://192.168.15.5/" = NewStr = "http://hank.home.on.ca/" = Dim hyp As Hyperlink = For Each hyp In ActiveSheet.Hyperlinks = hyp.Address = Replace(hyp.Address, OldStr, NewStr) = Next hyp =End Sub I tried this--no joy =Try something like: =Option Explicit =Sub Fix192Hyperlinks() = Dim OldStr As String, NewStr As String = OldStr = "E:\Cards\All Cards-For Linking\" = NewStr = "C:\Cards\SpecialBatch\ALL CARDS-FOR LINKING\" = Dim hyp As Hyperlink = For Each hyp In ActiveSheet.Hyperlinks = hyp.Address = Replace(expression:=hyp.Address, _ = Find:=OldStr, _ = Replace:=NewStr, _ = compa=vbTextCompare) = Next hyp =End Sub And this, too. Still no joy. I entered a file name, added a hyperlink through the Edit function, and made sure the link was correct. I changed the link to the new link and checked it, too. OK. Then changed the link back to the old link. Next, I put the code in the VB edit window by right-clicking "Sheet 1", selecting "View Code", and typing in the code. I returned to the sheet through "File"/"Close...and Return..." Old link still there. Back to the code. F5 to run. Some crunching somewhere I think... Back to the sheet. No joy. Still the old adress. Was this the way to do it? Groan again + Help! Be patient--I'm old! Cheers-- Terry--WB4FXD Edenton, NC -- Dave Peterson |
#9
|
|||
|
|||
How Can I Globally Change Hyperlinks?
On Thu, 13 Aug 2009 16:41:57 -0500, Dave Peterson
wrote: =The code should be placed in a General module (not behind the worksheet). = =Inside the VBE =Insert|Module = =And move the code there. This the code I am using: Option Explicit Sub CardHyperlinks() Dim OldStr As String, NewStr As String OldStr = "file:///E:\QSL CARDS\ALL CARDS-FOR LINKING\" NewStr = "file:///C:\ALL CARDS-FOR LINKING\" Dim hyp As Hyperlink For Each hyp In ActiveSheet.Hyperlinks hyp.Address = Replace(expression:=hyp.Address, _ Find:=OldStr, _ Replace:=NewStr, _ Compa=vbTextCompare) Next hyp End Sub =Then double check to make sure you have the old link path correct in the code (as well as the new link path). They match the present and future locations. =Then select the worksheet with the links (the code uses the ActiveSheet). Only one sheet in this project. =Then hit alt-F8 and run the macro. No Joy. : () ( Still the old (present) link. I stepped through the code with F8 and no unusual action was seen. =ps. Make sure your slashes match what you used in the hyperlinks. Several times, but they are exactly what the link actually is, all slashes especially! I must be doing something wrong. But what? Cheers-- Terry--WB4FXD Edenton, NC |
#10
|
|||
|
|||
How Can I Globally Change Hyperlinks?
How about copy|pasting a few of the old (untouched) links into your next
message. Or run some tests against a copy of the worksheet. Oldstr = "ALL" newstr = "xxx" just to see if that's working. Terry wrote: On Thu, 13 Aug 2009 16:41:57 -0500, Dave Peterson wrote: =The code should be placed in a General module (not behind the worksheet). = =Inside the VBE =Insert|Module = =And move the code there. This the code I am using: Option Explicit Sub CardHyperlinks() Dim OldStr As String, NewStr As String OldStr = "file:///E:\QSL CARDS\ALL CARDS-FOR LINKING\" NewStr = "file:///C:\ALL CARDS-FOR LINKING\" Dim hyp As Hyperlink For Each hyp In ActiveSheet.Hyperlinks hyp.Address = Replace(expression:=hyp.Address, _ Find:=OldStr, _ Replace:=NewStr, _ Compa=vbTextCompare) Next hyp End Sub =Then double check to make sure you have the old link path correct in the code (as well as the new link path). They match the present and future locations. =Then select the worksheet with the links (the code uses the ActiveSheet). Only one sheet in this project. =Then hit alt-F8 and run the macro. No Joy. : () ( Still the old (present) link. I stepped through the code with F8 and no unusual action was seen. =ps. Make sure your slashes match what you used in the hyperlinks. Several times, but they are exactly what the link actually is, all slashes especially! I must be doing something wrong. But what? Cheers-- Terry--WB4FXD Edenton, NC -- Dave Peterson |
Thread Tools | |
Display Modes | |
|
|