Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 23
Default 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   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 2
Default 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




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


Similar Threads
Thread Thread Starter Forum Replies Last Post
change a Figures into text in my excel exp. 200-two hundred Kems Excel Worksheet Functions 2 May 17th 08 02:51 PM
how to change word 500 to text five hundred inayat khan Excel Worksheet Functions 1 November 28th 07 12:09 PM
Hyperlinks: Hyperlinks change on copy/paste? Rick S. Excel Worksheet Functions 0 November 13th 07 09:19 PM
Why would hyperlinks in a spreadsheet change to an invalid path? LisaW Excel Discussion (Misc queries) 1 September 19th 06 05:38 PM
change a number (100) to word (i.e Hundred) CHA Salam Excel Discussion (Misc queries) 2 January 29th 05 03:15 PM


All times are GMT +1. The time now is 02:01 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"