Home |
Search |
Today's Posts |
#1
|
|||
|
|||
edit hyperlink code cont from 12/8/04
I tried running this without success. Can anybody tell me why?
Sub Fix192Hyperlinks() ' Dim OldStr As String, NewStr As String OldStr = "c:\My Templates\Profile Database\" NewStr = "\\Pinoak\Data\Mfr\Grinding Room\" Dim hyp As Hyperlink For Each hyp In ActiveSheet.Hyperlinks hyp.Address = Replace(hyp.Address, OldStr, NewStr) Next hyp End Sub Thank you "Ron de Bruin" wrote: I copy this from David McRitchie his site http://www.mvps.org/dmcritchie/excel/buildtoc.htm#hyp 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 Regards Ron de Bruin http://www.rondebruin.nl I am moving an Excel spreadsheet with about 3000 hyperlinks from C: drive to U: drive (servor) and need to revise the hyperlinks accordingly. While I'm at it, I can take an unneccessary folder (....../folder/....) out of the address. I tried writing VBA to do this without success. Suggestions? Thank you in advance. Doug |
#2
|
|||
|
|||
Do you use Excel 97 Doug ?
Try hyp.Address = Application.WorksheetFunction.Substitute(hyp.Addre ss, OldStr, NewStr) -- Regards Ron de Bruin http://www.rondebruin.nl "doug" wrote in message ... I tried running this without success. Can anybody tell me why? Sub Fix192Hyperlinks() ' Dim OldStr As String, NewStr As String OldStr = "c:\My Templates\Profile Database\" NewStr = "\\Pinoak\Data\Mfr\Grinding Room\" Dim hyp As Hyperlink For Each hyp In ActiveSheet.Hyperlinks hyp.Address = Replace(hyp.Address, OldStr, NewStr) Next hyp End Sub Thank you "Ron de Bruin" wrote: I copy this from David McRitchie his site http://www.mvps.org/dmcritchie/excel/buildtoc.htm#hyp 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 Regards Ron de Bruin http://www.rondebruin.nl I am moving an Excel spreadsheet with about 3000 hyperlinks from C: drive to U: drive (servor) and need to revise the hyperlinks accordingly. While I'm at it, I can take an unneccessary folder (....../folder/....) out of the address. I tried writing VBA to do this without success. Suggestions? Thank you in advance. Doug |
#3
|
|||
|
|||
excel 2003
"Ron de Bruin" wrote: Do you use Excel 97 Doug ? Try hyp.Address = Application.WorksheetFunction.Substitute(hyp.Addre ss, OldStr, NewStr) -- Regards Ron de Bruin http://www.rondebruin.nl "doug" wrote in message ... I tried running this without success. Can anybody tell me why? Sub Fix192Hyperlinks() ' Dim OldStr As String, NewStr As String OldStr = "c:\My Templates\Profile Database\" NewStr = "\\Pinoak\Data\Mfr\Grinding Room\" Dim hyp As Hyperlink For Each hyp In ActiveSheet.Hyperlinks hyp.Address = Replace(hyp.Address, OldStr, NewStr) Next hyp End Sub Thank you "Ron de Bruin" wrote: I copy this from David McRitchie his site http://www.mvps.org/dmcritchie/excel/buildtoc.htm#hyp 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 Regards Ron de Bruin http://www.rondebruin.nl I am moving an Excel spreadsheet with about 3000 hyperlinks from C: drive to U: drive (servor) and need to revise the hyperlinks accordingly. While I'm at it, I can take an unneccessary folder (....../folder/....) out of the address. I tried writing VBA to do this without success. Suggestions? Thank you in advance. Doug |
#4
|
|||
|
|||
Hi Doug
Send me a workbook with a few links (private)and i take a look at it -- Regards Ron de Bruin http://www.rondebruin.nl "doug" wrote in message ... excel 2003 "Ron de Bruin" wrote: Do you use Excel 97 Doug ? Try hyp.Address = Application.WorksheetFunction.Substitute(hyp.Addre ss, OldStr, NewStr) -- Regards Ron de Bruin http://www.rondebruin.nl "doug" wrote in message ... I tried running this without success. Can anybody tell me why? Sub Fix192Hyperlinks() ' Dim OldStr As String, NewStr As String OldStr = "c:\My Templates\Profile Database\" NewStr = "\\Pinoak\Data\Mfr\Grinding Room\" Dim hyp As Hyperlink For Each hyp In ActiveSheet.Hyperlinks hyp.Address = Replace(hyp.Address, OldStr, NewStr) Next hyp End Sub Thank you "Ron de Bruin" wrote: I copy this from David McRitchie his site http://www.mvps.org/dmcritchie/excel/buildtoc.htm#hyp 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 Regards Ron de Bruin http://www.rondebruin.nl I am moving an Excel spreadsheet with about 3000 hyperlinks from C: drive to U: drive (servor) and need to revise the hyperlinks accordingly. While I'm at it, I can take an unneccessary folder (....../folder/....) out of the address. I tried writing VBA to do this without success. Suggestions? Thank you in advance. Doug |
#5
|
|||
|
|||
Are you sure your activesheet had hyperlinks that matched your OldStr?
Maybe replacing this: hyp.Address = Replace(hyp.Address, OldStr, NewStr) with: hyp.Address = Replace(hyp.Address, OldStr, NewStr, 1, -1, vbTextCompare) will help. If you don't specify it (like vbTextCompare), replace is case sensitive. Check VBA's help for Replace for more info. doug wrote: I tried running this without success. Can anybody tell me why? Sub Fix192Hyperlinks() ' Dim OldStr As String, NewStr As String OldStr = "c:\My Templates\Profile Database\" NewStr = "\\Pinoak\Data\Mfr\Grinding Room\" Dim hyp As Hyperlink For Each hyp In ActiveSheet.Hyperlinks hyp.Address = Replace(hyp.Address, OldStr, NewStr) Next hyp End Sub Thank you "Ron de Bruin" wrote: I copy this from David McRitchie his site http://www.mvps.org/dmcritchie/excel/buildtoc.htm#hyp 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 Regards Ron de Bruin http://www.rondebruin.nl I am moving an Excel spreadsheet with about 3000 hyperlinks from C: drive to U: drive (servor) and need to revise the hyperlinks accordingly. While I'm at it, I can take an unneccessary folder (....../folder/....) out of the address. I tried writing VBA to do this without success. Suggestions? Thank you in advance. Doug -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|