How to recognize if a *.xls contains macros

Hi

Do you know a tool or command which lists all *.xls containing macros (vba code) ?

thx
guenter
haslingerAsked:
Who is Participating?
 
ProfessorJimJamConnect With a Mentor Commented:
open a excel worksheet, insert a module and then change the path to the path where your files are and then run it. it will show in msgbox all the files containing macros.

Sub FindMacros()
    Dim sPath As String
    Dim sFile As String
    Dim sFoundFiles As String

    'specify directory to use - must end in "\"
    sPath = "C:\"

    sFile = Dir(sPath)
    Do While sFile <> ""
        If InStr(sFile, ".xls") > 0 Then
            Workbooks.Open (sPath & sFile)
            If Workbooks(sFile).HasVBProject Then
                sFoundFiles = sFoundFiles & sFile & vbCrLf
            End If
            Workbooks(sFile).Close (False)
        End If
        sFile = Dir     ' Get next filename
    Loop
    If Len(sFoundFiles) = 0 Then
        MsgBox "No workbooks found that contain macros"
    Else
        sFoundFiles = "The following workbooks contain macros:" & _
          vbCrLf & vbCrLf & sFoundFiles
        MsgBox sFoundFiles
    End If
End Sub

Open in new window

0
All Courses

From novice to tech pro — start learning today.