Hi everyone.

I'm trying to create a macro that allows my spreadsheet to update
every year for Excel '97. Basically, the spreadsheet contains
hyperlinks to other spreadsheets with dates in the title. For example
='C:\Kris\Stats 2003\[Stats0703.xls]stats'!$D$12

Now that 0703 represents July 2003. What I want the macro to do is go
through all the tabs in the spreadsheet and change that 0703 or 0603
or whatever to 04, 05 etc. Also, I would want it to change the
subfolder titled "Stats 2003" to "Stats 2004". There are LOTS of
hyperlinks in this spreadsheet (100+).

Is this possible or am I just dreaming?!?!

Please let me know!

Thanks,

Kris Taylor

Hi Kris,

Are they true hyperlinks, or are they just external links? If they are just
links to cells in an external Excel file, you can do a search/replace to
update the year. Just select all the sheet tabs (click first sheet, hold
down Shift key, click last sheet), then hit Ctrl+h. In the find box, enter
"/Stats 2003/", and enter "/Stats 2004/" in the replace box. Make sure the
find/replace is looking in formulas. If you click Replace All, it should
replace all references as expected. You can do the same thing and replace
"[Stats0703.xls]" with "[Stats0704.xls]" or similar.

If you want to automate this, you can just record a macro before you go
through those steps.

--
Regards,

Jake Marx
www.longhead.com


Kris Taylor wrote:
Hi everyone.

I'm trying to create a macro that allows my spreadsheet to update
every year for Excel '97. Basically, the spreadsheet contains
hyperlinks to other spreadsheets with dates in the title. For example
='C:\Kris\Stats 2003\[Stats0703.xls]stats'!$D$12

Now that 0703 represents July 2003. What I want the macro to do is go
through all the tabs in the spreadsheet and change that 0703 or 0603
or whatever to 04, 05 etc. Also, I would want it to change the
subfolder titled "Stats 2003" to "Stats 2004". There are LOTS of
hyperlinks in this spreadsheet (100+).

Is this possible or am I just dreaming?!?!

Please let me know!

Thanks,

Kris Taylor

Kris,

First try a manual Replace to see if it works. Than record a macro to
reproduce what you just did.

But be cautious - the replace function works on all matches so be sure to
make them distinct enough to prevent changing anything else.

instead of 2003, use Stats 2003; also use Stats0703 instead of 0703.

You can also build a loop to go from 0103 to 1203 to help streamline
your code. And you can use an outer loop to go through all the worksheets:
Dim x as Long, y as Long, strng as String
For x = 1 to Activeworkbook.Worksheets.Count
Sheets(x).Select
For y = 1 to 12
*replace code for Stats0x03*
Next
*replace code for Stats 2003*
Next

See if this can get you started...
--
sb
"Kris Taylor" wrote in message
om...
Hi everyone.

I'm trying to create a macro that allows my spreadsheet to update
every year for Excel '97. Basically, the spreadsheet contains
hyperlinks to other spreadsheets with dates in the title. For example
='C:\Kris\Stats 2003\[Stats0703.xls]stats'!$D$12

Now that 0703 represents July 2003. What I want the macro to do is go
through all the tabs in the spreadsheet and change that 0703 or 0603
or whatever to 04, 05 etc. Also, I would want it to change the
subfolder titled "Stats 2003" to "Stats 2004". There are LOTS of
hyperlinks in this spreadsheet (100+).

Is this possible or am I just dreaming?!?!

Please let me know!

Thanks,

Kris Taylor