(Msg. 1) Posted: Mon Jun 23, 2008 3:28 am
Post subject: Mass edit Hyperlinks Archived from groups: microsoft>public>excel>links (more info?)
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
(Msg. 2) Posted: Mon Jun 23, 2008 11:43 am
Post subject: Re: Mass edit Hyperlinks [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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
(Msg. 3) Posted: Thu Aug 21, 2008 2:44 pm
Post subject: Re: Mass edit Hyperlinks [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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" <Bill-Manville.RemoveThis@msn.com> 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
>
(Msg. 4) Posted: Fri Aug 22, 2008 8:10 am
Post subject: Re: Mass edit Hyperlinks [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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
(Msg. 5) Posted: Fri Oct 03, 2008 2:20 pm
Post subject: Re: Mass edit Hyperlinks [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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
>
>
(Msg. 6) Posted: Sun Oct 05, 2008 8:34 am
Post subject: Re: Mass edit Hyperlinks [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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
(Msg. 7) Posted: Mon Oct 06, 2008 11:15 am
Post subject: Re: Mass edit Hyperlinks [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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
>
>
(Msg. 8) Posted: Tue Oct 07, 2008 9:03 pm
Post subject: Re: Mass edit Hyperlinks [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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
All times are: Eastern Time (US & Canada) (change)
Page 1 of 1
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Profile
Private Messages
Log in
Office