A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Links and Linking
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Mass edit Hyperlinks



 
 
Thread Tools Display Modes
  #1  
Old June 23rd, 2008, 11:28 AM posted to microsoft.public.excel.links
Philip Drury
external usenet poster
 
Posts: 34
Default 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
  #2  
Old June 23rd, 2008, 12:43 PM posted to microsoft.public.excel.links
Bill Manville
external usenet poster
 
Posts: 258
Default 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

  #3  
Old August 21st, 2008, 07:44 PM posted to microsoft.public.excel.links
TR Young
external usenet poster
 
Posts: 25
Default 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
...
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



  #4  
Old August 22nd, 2008, 09:10 AM posted to microsoft.public.excel.links
Bill Manville
external usenet poster
 
Posts: 258
Default 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 changesg.
Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

  #5  
Old October 3rd, 2008, 10:20 PM posted to microsoft.public.excel.links
rebecca sage[_2_]
external usenet poster
 
Posts: 2
Default 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


  #6  
Old October 5th, 2008, 09:34 AM posted to microsoft.public.excel.links
Bill Manville
external usenet poster
 
Posts: 258
Default 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

  #7  
Old October 6th, 2008, 07:15 PM posted to microsoft.public.excel.links
Rebecca
external usenet poster
 
Posts: 284
Default 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 changesg.
Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup


  #8  
Old October 7th, 2008, 10:03 PM posted to microsoft.public.excel.links
Bill Manville
external usenet poster
 
Posts: 258
Default 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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:12 PM.


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