search and replace code in Excel-projects?

[Rolf Barbakken]

We have a case where we need to modify about 100 Excel-files to use a
different method of data-access. So we need to open the VBA-project, add a
reference, and change and add code.

Is it possible to do this (some or all) through some kind of search and
replace? Or do we have to open each and every file to fix it?

Any ideas appreciated.

 

[Patrick Smith [MSFT]]

This is a difficult thing to do, but can be done using the Microsoft Visual
Basic for Application Extensibility reference. Adding this as a reference
gives you the ability to program against the VBA OM so that you can access
the project directly.

Other than doing it this way, you'd have to do it manually by opening the
file and making the changes you need to make. Also, in order to do this,
you'll have to turn on the "Trust Access to VBA project" setting in the
applications options.

Hope this helps,

 

[Rolf Barbakken]

Thanks, Patrick.

The shortest route to a solution turned out to be opening each and every
file (about 150) and doing the same change. Crude, but didn't take too long.

For the future, I'm trying to convince the customer to let me place some of
the code on a server (a WS, for instance) to avoid a similar problem
occurring again.