How to determin a Excel file has Macro

  • Thread starter Thread starter Winston Lee
  • Start date Start date
W

Winston Lee

Hi All:
I have a lot of Excel files. I hope to demarcate
the Excel files that have macro. Is there any tools or how
to program ?


Thanks
Best Regards
 
Winston,

There is no way to determine whether a workbook has a macro
or not without attempting to open it.
If you set your security level to "Medium", you'll be prompted
to "Enable Macros" if one is found in the workbook.
Be aware though that getting the prompt doesn't necessarily
mean that there "are" macros in the workbook. An empty module
or some errant code in the workbook will also trigger the prompt.

John
 
Hi

I have never seen code like that. But try opening a few small Excel files in
Notepad -there are some very distinct ascii words in files that contain macros. So maybe
this can be done, and if "lot of files" is a big lot then maybe it's worth the effort.
 
This would be very difficult to get working accurate first cut.

I would inspect the code modules of each VBComponent inside the workbook.
Try doing a lookup on ProcOfLine. You could loop through each line checking
for at least one procedure. If one exists you could assume the workbook has
macros?
 
I thought I would put theory to the test.
You need to add a reference (From Tools menu, References) to "Microsoft
Visual Basic for Applications Extensibility"

Sub testit()
Dim wkb As Workbook, vbc As VBComponent, i As Long, blnFound As Boolean

For Each wkb In Workbooks
blnFound = False
For Each vbc In wkb.VBProject.VBComponents
With vbc.CodeModule
For i = 1 To .CountOfLines
If .ProcOfLine(i, vbext_pk_Get) <> "" Or _
.ProcOfLine(i, vbext_pk_Let) <> "" Or _
.ProcOfLine(i, vbext_pk_Proc) <> "" Or _
.ProcOfLine(i, vbext_pk_Set) <> "" Then
blnFound = True
Exit For
End If
Next
End With
If blnFound Then Exit For
Next
MsgBox wkb.Name & ": macros " & IIf(blnFound, "", "not ") & "found"
Next
End Sub
 
Back
Top