ExcelBanter - Need to scan xls files and identify if they use macros...wanna use

I agree that Jim Hicks is probably going to have to open each workbook found
and determine if it has code in it or not on an individual basis. Even
scanning them with a pure VB application still requires opening them, so
might as well do it with Excel.

I found a coded function over at John Walkenbach's site that also determines
if there is any code in a workbook. I modified that a little here and
included a Sub to call it to show how it works:
Sub DoIHaveCode()
Dim HasCodeResult As Boolean

HasCodeResult = WorkbookHasVBACode(ThisWorkbook)
Worksheets("Sheet1").Activate
If HasCodeResult = True Then
ActiveCell = "Yes, Has Code"
Else
ActiveCell = "No - slipped by me"
End If

End Sub

Function WorkbookHasVBACode(wb As Workbook) As Boolean
'adapted from
'http://j-walk.com/ss/excel/tips/tip70.htm
'by J. Walkenbach
'
WorkbookHasVBACode = False ' default
If wb.VBProject.VBComponents(wb.CodeName). _
CodeModule.CountOfLines 0 Then
WorkbookHasVBACode = True
End If

End Function

Essentially you:
Open a workbook, pass the workbook to the WorkbookHasVBACode() function and
evaluate the result. Possibly some code that pflugs found could be
substituted. In the calling routine, you could even output the path and
filenames of the files with code in them to an Excel worksheet instead of the
"Yes..." output I used in the sample so you have a record. Only thing I
would consider is the possibility that you have more than 65536 .xls files
with code in them.

The comments that pflugs made about setting Macro security are definitely on
point - and are required to use the function I provided above.

The general format of the command to open an Excel workbook in VBA is
Workbooks.Open Filename:= _
"C:\Documents and Settings\user\My Documents\TestInputBoxOnOpen.xls"
that is - you provide it with the full path/filename to the file. It will
become the active window when opened. You can then save the file's path/name
and determine if it has code or not and then close it:
ActiveWorkbook.Close
and write saved information into the Excel workbook you're working from.

Repeat as required through all Dirs/Subdirs...

"Pflugs" wrote:

Jim,

Sounds like you're going to have to combine a few methods. While I don't
have a specific idea, I suggest you start with Chip Pearson's Website on
Programming to the Visual Basic Editor (VBE) at this url:

http://www.cpearson.com/excel/vbe.htm

You will have to open each workbook on your network and check its VBE
objects for "vbext_ct_ClassModule" and "vbext_ct_StdModule". Don't forget to
add a reference to "Microsoft Visual Basic For Applications Extensibility" in
Tools- References, and you will need to go to the Tools menu, choose Macros,
then Security, click the "Trusted Sources" tab, and put a check next to the
"Trust access to Visual Basic Project", as Mr. Pearson describes in the above
site.

Good luck.
Pflugs

"Jim Hicks" wrote:

I was given a project to scan all of the Excel files on our network and
generate a report listing the
files and wether they use Macros. I was thinking of using VB6 to scann all
dirs/subdirs...easy part.
Question: -- How do I quickly scan an excel file and determine if it
has/uses Macros using VB6.
I appreciate you taking the time to read this and any help anybody can
provide. Thanks.