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
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|