Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
doug
 
Posts: n/a
Default 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   Report Post  
Ron de Bruin
 
Posts: n/a
Default

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   Report Post  
doug
 
Posts: n/a
Default

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   Report Post  
Ron de Bruin
 
Posts: n/a
Default

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   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 05:03 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"