Tricky one: Change of references within Macros

[HolgerP]

I have an application which pulls information from multiple spreadsheets into an MS Access db. Access provides the web-reporting online. Macros and Excel generate additional reporting. The entire app is hosted on a server, therefore all Macros Webpages reference to this server. Now I was told that I have to move my app of to a new server, which means I have to re-point my reference to the new location. This is very painful as I have a lot of references within the 60 Macros I created. Is there a tool availabel which allows me a mass-change or do I really have to go manually through all Macros? Any help is greatly appreciated. Mr. Excel rocks !

 

[blueshark]

I don't know if it will help but there is an application called Speed Ferret that does something similar to that.

http://www.SpeedFerret.com

Good Luck.

 

[mdmilner]

My personal recommendation, however brutal, is for you to manually change all 60 macros. But, you need to upgrade them to make it easier in the future.

What many people do, myself included, is to put critical information that may need to be changed into a table - and then in the code refer to that data within the table. Net result? Make a change once in a single field in a table (that you always know where it is) and you've just changed an unlimited number of references in every bit of code you use.

My question is - are you using macros or are you using VBA code modules?
One benefit of VBA code is - you remember that little old Find & Replace option in Excel? It works in VBA code modules. You could mass change every exact text string match with a couple keystrokes. I'm *not sure* how hard it is to do the same thing in a macro.

*If* you're using macros. Try this - convert them all to code (built in utility) and then try editing them. Might take awhile to do 60, and then you should probably manually cut n paste them into one or more (group logically however you wish) code modules....and then do a find/replace on that.

Mike