Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 23rd 08, 11:28 AM posted to microsoft.public.excel.links
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 32
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   Report Post  
Old June 23rd 08, 12:43 PM posted to microsoft.public.excel.links
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 473
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   Report Post  
Old August 21st 08, 07:44 PM posted to microsoft.public.excel.links
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 21
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   Report Post  
Old August 22nd 08, 09:10 AM posted to microsoft.public.excel.links
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 473
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 changes<g.
Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

  #5   Report Post  
Old October 3rd 08, 10:20 PM posted to microsoft.public.excel.links
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2008
Posts: 3
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   Report Post  
Old October 5th 08, 09:34 AM posted to microsoft.public.excel.links
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 473
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   Report Post  
Old October 6th 08, 07:15 PM posted to microsoft.public.excel.links
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 106
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 changes<g.
Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup


  #8   Report Post  
Old October 7th 08, 10:03 PM posted to microsoft.public.excel.links
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 473
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



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
Can't edit hyperlinks Karla V Excel Discussion (Misc queries) 1 January 19th 07 08:41 PM
Can't edit hyperlinks galimi Excel Discussion (Misc queries) 3 January 19th 07 08:24 PM
Can't edit hyperlinks scott Excel Discussion (Misc queries) 0 January 19th 07 08:15 PM
Edit Hyperlinks SLKoelker Excel Discussion (Misc queries) 5 December 7th 06 01:28 PM
Edit Hyperlinks yazz22 Excel Worksheet Functions 2 January 15th 05 02:29 PM


All times are GMT +1. The time now is 02:18 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017