Search multiple workbooks for vba

[robertjtucker]

Is there a program or a way to search xls files for specific visual basic? Kind of like searching for a file in windows, where I could say that I am looking for the term, for example, "activeworkbook" through all excel files and it would tell me which xls files contain a script with "activeworkbook" in it? I wrote a function about 4 months ago and I can't find it and I have hundreds of xls files. i am looking for a quicker way than looking individually.
Thanks for the help as always,
Bob

[Tristan]

Hi

I'm quite proud of this one.

Stick the code below into a new book.

Stick all the files that you want to check into the same directory and alter the file path ni the code (highligted in BOLD appropriately.

Then press go.

Unfortunately you can't walk away whilst it searches because you might have to accept or decline prompts if there are links in your workbooks that might need updating. Also you'll need to make a manual note of any workbooks that have VBA in (though you could have the macro list these on a worksheet if you so desired).

Just a thought though. Your function wouldn't happen to be in your Personal.xls workbook would it? To check drill down through your c: drive to ... C:\Program Files\Microsoft Office\Office10\XLStart. In this folder there should be your Personal.xls workbook. Open it up and then you'll be able to check any code in it in the VBE.

Let me know how it turns out.
Tris

////////////////////////////////////////////////////////////
Sub FindCode()

Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer


With Application.FileSearch
.LookIn = "C:\Documents and Settings\My Documents"
.FileType = msoFileTypeExcelWorkbooks
.Execute

For i = 1 To .FoundFiles.Count

GetObject (.FoundFiles(i))

For j = 1 To Workbooks(Application.Workbooks.Count).VBProject.VBComponents.Count

If Workbooks(Application.Workbooks.Count).VBProject.VBComponents(j).CodeModule.CountOfLines > 0 Then

MsgBox "The following file" & .FoundFiles(i) & "has some VBA code in " & Workbooks(Application.Workbooks.Count).VBProject.VBComponents(j).Name

End If

Next j

Workbooks(Application.Workbooks.Count).Close savechanges:=False

Next i

MsgBox "Finished searching " & i & "files."

End With
/////////////////////////////////////////////////////////////////////////////////////