how to find/replace hyperlinks

372 views
Skip to first unread message

vic

unread,
Apr 29, 2003, 12:39:44 AM4/29/03
to
a network drive letter has changed, and i need to do a
find/replace on about 800 hyperlinks on a single sheet
which point to the old drive letter.
i've messed around with some VBA code to automate this,
but can't get it to work.
any suggestions please?
tia.

Bill Manville

unread,
Apr 29, 2003, 3:07:46 AM4/29/03
to
Vic wrote:
> a network drive letter has changed, and i need to do a
> find/replace on about 800 hyperlinks on a single sheet
> which point to the old drive letter.
>

I would try

Sub ChangeHyperDrive()
Dim H As Hyperlink
For Each H In ActiveSheet.HyperLinks
Debug.Print H.Address
If Left(H.Address,2)="D:" Then
H.Address = "E:" & Mid(H.Address,2)
End If
Next
End Sub

If it doesn't work, look in the VB editor immediate window to see what
the addresses are actually like and, if you can't see how to change the
macro to cope, post back here a sample address.

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

vic

unread,
Apr 29, 2003, 7:16:05 PM4/29/03
to
fabulous. worked first time. can't thank u enough for
making the effort to post this.
thanks Bill.
Reply all
Reply to author
Forward
0 new messages