Updating hyperlinks

Hi all,

I have an excel spreadsheet that references numerous other
spreadsheets via hyperlinks. The problem is the xls was
designed with the supporting spreadsheets in a defined
structure, i.e. c:\work\2003\stuff\stuff.xls

Is there any way that I can update all of the hyperlinks
at the same time to reflect a year change?? For example,
copy the entire structure to c:\work\2004 etc .... and
then update all of the hyperlinks to be 2004, not 2003.

Thanks in advance

Andy Wilkinson

Andy Wilkinson wrote:
Is there any way that I can update all of the hyperlinks
at the same time to reflect a year change?? For example,
copy the entire structure to c:\work\2004 etc .... and
then update all of the hyperlinks to be 2004, not 2003.


Sub UpdateHyperlinks()
Dim H As Hyperlink
For Each H In ActiveSheet.Hyperlinks
H.Address = Replace(H.Address, "2003", "2004")
Next
End Sub

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