Spreadsheet with hundred hyperlinks that need to change
 

I have a spreadsheet that has a hundred or so links (as hyperlinks) to pdf
files on a server share. The original links were pointing to
\\ServerA\share\file.pdf

The new location is \\ServerB\Share\file.pdf However I have to do this to a
hundred links! Is there a way to bulk change this. A find and replace doesn't
seem to work.

Thanks,

TKE402

Spreadsheet with hundred hyperlinks that need to change
 

I found this on a web:
http://www.office-addins.com/-excel-...d-replace.html

You can take also code from the example
http://www.ozgrid.com/forum/showthread.php?t=72798 below, but you should
change it first to fit your needs :

Sub remove_chars()
Dim x As Integer
Dim y As Integer
Dim strAddress As String
For y = 1 To 10 ' starting and ending columns
For x = 1 To 1000 ' starting and ending rows
strAddress = GetAddress( Cells(x, y))
If InStr(strAddress, "C:\Documents_And_Settings\") 0 Then
Cells(x, y).Hyperlinks(1).Address = Right(strAddress,
Len(strAddress) - InStr(strAddress, "C:\Documents_And_Settings\") - 25) '
change 25 to the number of chars in the string to remove minus 1
End If
Next x
Next y
End Sub

Function GetAddress(HyperlinkCell As Range)
If HyperlinkCell.Hyperlinks.Count 0 Then
GetAddress = HyperlinkCell.Hyperlinks(1).Address
End If
End Function

Ivan

"tke402" wrote in message
...
I have a spreadsheet that has a hundred or so links (as hyperlinks) to pdf
files on a server share. The original links were pointing to
\\ServerA\share\file.pdf

The new location is \\ServerB\Share\file.pdf However I have to do this to
a
hundred links! Is there a way to bulk change this. A find and replace
doesn't
seem to work.

Thanks,

TKE402

Spreadsheet with hundred hyperlinks that need to change
 

Thanks Ivan the add-ins worked great!

"Ivan" wrote:

I found this on a web:
http://www.office-addins.com/-excel-...d-replace.html

You can take also code from the example
http://www.ozgrid.com/forum/showthread.php?t=72798 below, but you should
change it first to fit your needs :

Sub remove_chars()
Dim x As Integer
Dim y As Integer
Dim strAddress As String
For y = 1 To 10 ' starting and ending columns
For x = 1 To 1000 ' starting and ending rows
strAddress = GetAddress( Cells(x, y))
If InStr(strAddress, "C:\Documents_And_Settings\") 0 Then
Cells(x, y).Hyperlinks(1).Address = Right(strAddress,
Len(strAddress) - InStr(strAddress, "C:\Documents_And_Settings\") - 25) '
change 25 to the number of chars in the string to remove minus 1
End If
Next x
Next y
End Sub

Function GetAddress(HyperlinkCell As Range)
If HyperlinkCell.Hyperlinks.Count 0 Then
GetAddress = HyperlinkCell.Hyperlinks(1).Address
End If
End Function

Ivan

"tke402" wrote in message
...
I have a spreadsheet that has a hundred or so links (as hyperlinks) to pdf
files on a server share. The original links were pointing to
\\ServerA\share\file.pdf

The new location is \\ServerB\Share\file.pdf However I have to do this to
a
hundred links! Is there a way to bulk change this. A find and replace
doesn't
seem to work.

Thanks,

TKE402