Detect macros in an Excel file
Many thanks
"Dave Peterson" wrote:
Try changing one line (and remove the reference):
Dim m As Object
Gleam wrote:
Thank you for bringing this to my attention - less haste more speed on my part!
Still not a portable solution until "everyone" is on Excel 2007
"Dave Peterson" wrote:
' if Compile error:
' User-defined type not defined
' then VBE Tools - Reference,
' check Microsoft Visual Basic for Applications Extensibility
5.3
Gleam wrote:
Carl
I get a compile error at this line:
Dim m As VBComponent
"user-defined type not defined."
Do I need to activate a reference library? Some help on this would be
appreciated as I have just searced help with "reference libraries" and found
nothing helpful
"Carl Hartness" wrote:
Define a function
Function wkbkHasMacros(fName$) As Boolean
' if Compile error:
' User-defined type not defined
' then VBE Tools - Reference,
' check Microsoft Visual Basic for Applications Extensibility
5.3
Dim m As VBComponent
' if Run-time error '1004'
' Programmatic access to Visual Basic Project is not trusted.
' then Excel Tools - Macro - Security,
' Click to enable "Trust access to Visual Basic Project"
wkbkHasMacros = False
For Each m In Workbooks(fName$).VBProject.VBComponents
' m.Type, 1=module, 2=Class, 3=Form, 100=Excel object
If m.Type = 1 Then
wkbkHasMacros = True
Exit Function
End If
Next m
End Function
and call it with wkbkHasMacros(ActiveWorkbook.Name)
Carl.
On Mar 16, 4:26 pm, Gleam wrote:
I am using macros in one file to audit another file. The second file should
not have any macros. Is there a way I can detect this?
--
Dave Peterson
--
Dave Peterson
|