Changing hyperlinks for a range of cells

[cw]

Every month I have to update an Excel file which links to
another Excel file by hyperlinks.(for instance, the
cell "A1" in "monthly03.xls" are linked to the cell "A1"
in "Source-Jun03.xls", now I need to link "monthly03.xls"
to "Source-July03.xls"). Currently I need to go to every
single cell (more than 500 of them) of "monthly03.xls" and
change the hyperlinks so as the file is linked to "Source-
July03.xls". It is very time-consuming. I believe it can
be done by using Macro. However, I do not have any
background in Macro/VBA. Is there any other easier way
to do it? Please help.

Awaiting for your reply.

Thanks and best regards,
CW

 

[Andy]

cw

Have you tried using Find/Replace?

Andy

 

[Minh Phan]

No VBA involved...this is easier than you think!

Two methods:
1. Use the edit link functionality.
Edit -> Links -> Select "Source-Jun03.xls" -> Change
Source -> Select the new file "Source-July03.xls"

2. Use find and replace.
Open all 3 files.
Edit -> Replace -> do a search for the "Source-Jun03.xls"
and replace with "Source-July03.xls"

Hope that answers your question!

Minh