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

"Winston Lee" wrote in message
...
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

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.

--
HTH. Best wishes Harald
Followup to newsgroup only please.

"Winston Lee" wrote in message
...
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

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?



"Winston Lee" wrote in message
...
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

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


"Rob van Gelder" wrote in message
...
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?



"Winston Lee" wrote in message
...
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

Hi Winston Lee;
You need:
http://download.microsoft.com/downlo...US/Dsofile.exe
and after, you can use the HasMacros property.
MP

"Winston Lee" a écrit dans le message de
...
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