Find/Replace a statement in XL VBA code

[Sreez]

Hi All,

I have set of around 25 Excel sheets (Version 2003), each with multiple VBA modules. These Excel sheets use a hard coded path to an Access database to connect to a database and do some SQL transactions.

I was wondering if there is a way to search these hard coded statements across these multiple excel sheets and replace them with another statement.

I tried the Windows search (through windows explorer and desktop search) to find workbooks containing the hard coded statement in the VBA code . But this does not return any results.

Any ideas.

Thanks
Sree

 

[jbeaucaire]

I don't know of a way to search through workbooks and make changes in the VBA modules of those sheets, but I do have a suggestion to permanently simplify/solve this problem.

Store the "hardcoded" path in another workbook in a specific cell as a text string, then have all your other sheets draw in that value, either by VBA or by having a cell in each sheet that does that job via external link. Then the VBA in each sheet can refer to the text string it looked up rather than hard-coding, so if you change it in that single external location, all workbooks using it are automatically updated, too.

 

[Sreez]

thanks JB, that is precisely what I was planning to do but was wondering if there is a simpler way of making those changes in all those VBA modules.