Mass edit Hyperlinks

Mass edit Hyperlinks

I have a spread sheet with over 270 hyperlinks, have had to move the 
spreadsheet and associated links to a new location but the hyperlinks are 
still looking in the old location!  Does anyone know a way to edit the 
hyperlinks 'on mass' as opposed to me editing each link individually??
Thanks

date: Mon, 23 Jun 2008 03:28:00 -0700 author: Philip Drury

Re: Mass edit Hyperlinks

Try this:

Sub ReplaceHyperlinksInActiveWorkbook()
  Dim oSheet As Object
  Dim H As Hyperlink
  Dim stFind As String
  Dim stReplace As String
  stFind = InputBox("What is the initial path to replace?", , "\\Old\")
  If stFind = "" Then Exit Sub
  stReplace = InputBox("What should the path become?", , "\\New\")
  If stReplace = "" Then Exit Sub
  For Each oSheet In ActiveWorkbook.Sheets
    For Each H In oSheet.Hyperlinks
      If InStr(H.Address, stFind) = 1 Then
        H.Address = stReplace & Mid(H.Address, Len(stFind) + 1)
      End If
    Next
  Next
End Sub


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

date: Mon, 23 Jun 2008 11:43:38 GMT author: Bill Manville

Re: Mass edit Hyperlinks

I created a VBA routine with this, Bill, and then assigned it to a button. 
It is not working for me.  the routine runs correctly, but the hyperlinks 
aren't updating.  The files are on our business network, so I am wondering 
if that has anything to do with it??


"Bill Manville"  wrote in message 
news:VA.000014ad.054951e3@msn.com...
> Try this:
>
> Sub ReplaceHyperlinksInActiveWorkbook()
>  Dim oSheet As Object
>  Dim H As Hyperlink
>  Dim stFind As String
>  Dim stReplace As String
>  stFind = InputBox("What is the initial path to replace?", , "\\Old\")
>  If stFind = "" Then Exit Sub
>  stReplace = InputBox("What should the path become?", , "\\New\")
>  If stReplace = "" Then Exit Sub
>  For Each oSheet In ActiveWorkbook.Sheets
>    For Each H In oSheet.Hyperlinks
>      If InStr(H.Address, stFind) = 1 Then
>        H.Address = stReplace & Mid(H.Address, Len(stFind) + 1)
>      End If
>    Next
>  Next
> End Sub
>
>
> Bill Manville
> MVP - Microsoft Excel, Oxford, England
> No email replies please - respond to newsgroup
>

date: Thu, 21 Aug 2008 14:44:13 -0400 author: TR Young

Re: Mass edit Hyperlinks

TR Young wrote:
> the routine runs correctly, but the hyperlinks 
> aren't updating
>
Presumably the stFind you specified is not matching the start of the 
hyperlink addresses you wanted to change.  Could be a case-sensitivity 
issue.  Try:
      If InStr(LCase(H.Address), LCase(stFind)) = 1 Then

If that still doesn't do it, get it to tell you what the Address is 
that it is finding:

    For Each H In oSheet.Hyperlinks
      Debug.Print H.Address 
      If InStr(LCase(H.Address), LCase(stFind)) = 1 Then
        H.Address = stReplace & Mid(H.Address, Len(stFind) + 1)
      End If
    Next

I assume you are remembering to save the modified workbook having made 
the changes<g>.
Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

date: Fri, 22 Aug 2008 08:10:28 GMT author: Bill Manville

Re: Mass edit Hyperlinks

Where do I learn this secret language? (Dim, etc)?

"Bill Manville" wrote:

> Try this:
> 
> Sub ReplaceHyperlinksInActiveWorkbook()
>   Dim oSheet As Object
>   Dim H As Hyperlink
>   Dim stFind As String
>   Dim stReplace As String
>   stFind = InputBox("What is the initial path to replace?", , "\\Old\")
>   If stFind = "" Then Exit Sub
>   stReplace = InputBox("What should the path become?", , "\\New\")
>   If stReplace = "" Then Exit Sub
>   For Each oSheet In ActiveWorkbook.Sheets
>     For Each H In oSheet.Hyperlinks
>       If InStr(H.Address, stFind) = 1 Then
>         H.Address = stReplace & Mid(H.Address, Len(stFind) + 1)
>       End If
>     Next
>   Next
> End Sub
> 
> 
> Bill Manville
> MVP - Microsoft Excel, Oxford, England
> No email replies please - respond to newsgroup
> 
>

date: Fri, 3 Oct 2008 14:20:01 -0700 author: rebecca sage

Re: Mass edit Hyperlinks

Rebecca sage wrote:
> Where do I learn this secret language? (Dim, etc)?
>
No great secret.
The language is Visual Basic for Applications (VBA).

A good book to try for starters would be Excel NNNN Visual Basic for 
Applications Step by Step.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

date: Sun, 05 Oct 2008 08:34:16 GMT author: Bill Manville

Re: Mass edit Hyperlinks

Thanks, Bill. Unfortunately I need to resolve this issue sooner than later. I 
was wondering if I could get some help not using VBA? 
I need to edit hyperlinks en masse as well. Can I do that using windows 
dialogue boxes in Excel?
thanks!
Rebecca

"Bill Manville" wrote:

> TR Young wrote:
> > the routine runs correctly, but the hyperlinks 
> > aren't updating
> >
> Presumably the stFind you specified is not matching the start of the 
> hyperlink addresses you wanted to change.  Could be a case-sensitivity 
> issue.  Try:
>       If InStr(LCase(H.Address), LCase(stFind)) = 1 Then
> 
> If that still doesn't do it, get it to tell you what the Address is 
> that it is finding:
> 
>     For Each H In oSheet.Hyperlinks
>       Debug.Print H.Address 
>       If InStr(LCase(H.Address), LCase(stFind)) = 1 Then
>         H.Address = stReplace & Mid(H.Address, Len(stFind) + 1)
>       End If
>     Next
> 
> I assume you are remembering to save the modified workbook having made 
> the changes<g>.
> Bill Manville
> MVP - Microsoft Excel, Oxford, England
> No email replies please - respond to newsgroup
> 
>

date: Mon, 6 Oct 2008 11:15:02 -0700 author: Rebecca

Re: Mass edit Hyperlinks

Rebecca wrote:
> I 
> was wondering if I could get some help not using VBA? 
> I need to edit hyperlinks en masse as well. Can I do that using windows 
> dialogue boxes in Excel?
>
Excel does not provide help for mass edits of hyperlink addresses.
You will either have to use a macro similar to the one I posted on each 
workbook whose hyperlinks you need to change, or you could email me at 
Bill underscore Manville at Compuserve dot com for a free copy of 
LinkManager, a utility which will do this and more.

To run the macro is quite simple;
Start Excel
File > New
Alt+F11 to the visual basic editor
Insert > Module
Paste the code into the big white space that appears
Alt+F11 back to Excel
Open the workbook containing the links
Tools > Macro > Macros > (select the only macro) > Run

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

date: Tue, 07 Oct 2008 21:03:10 GMT author: Bill Manville