Excel Hyperlink Macro

[Kris Taylor]

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

 

[Jake Marx]

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.

 

[steve]

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...