Searching a folder of Worksheets to locate specific VB code string

  • Thread starter Thread starter Randy Hardy
  • Start date Start date
R

Randy Hardy

I have mis-located a worksheet with an important VB macro
in it. I need to search several folders with hundreds of
worksheets to locate the correct worksheet. All I have
are a couple of text strings that I think are in the VB
code.

Is there any utility that can search VB code in multiple
EXCEL 2000 modules for a specific string?

I know how to do it by manually opening each workbook, but
it would trake days to do this. All the utilities I have
found only search data withing worksheet cells, not the VB
macros/modules.

Thanks, Randy
 
Maybe you'll get lucky with Windows|Find.

I put a couple of strings in some VBA module and it found them for me.

There's an option to search for files containing text on one of those dialogs.
(In Win98, it was on the name and location tab. (I used *.xl* as the name to
limit the search.)
 
Thanks, I thought this would not work, but it depends on
if Excel VB changes commands into pseudo code or leaves it
as text. For instance if you searched for the occurrence
of debug.print windows search would not find it. But if
you put a single quote in front if it ('debug.print) excel
would leave it as text and windows find will find it.

What I was looking for was a utility that could decode VBA
pseudo code on-the-fly and find anything I wanted.

Thanks again for the reply. I am probably asking for too
much as the utility would probably need a chunk of the VBA
engine to accomplish this.
 
I don't think I could translate the pcode back into English.

I built a test workbook with a line like:
debug.print "xxxxxx"

then I saved/closed that workbook and opened it up in my favorite text editor.
I searched for xxxxxx and found it.

But the stuff in front of xxxxx looked Greek to me. But I copied it and pasted
it into the Find dialog.

Win98's Find failed to retrieve it.

===
Chip Pearson has some code that can be used to read from a module and to write
to a module.

You may want to look at that:
http://www.cpearson.com/excel/vbe.htm
 
Back
Top