Looping through excel files in a folder to find workbooks with locked VBA project modules

[dougmarkham]

Hi Folks,

I am trying to build an excel model which will loop through all the workbooks in a folder and tell me which of them has password protected VBE modules.

The reason for this requirement is that I'm trying to use another excel VBA model to find workbooks that are challenging our server database via VBA. My module string-search code searches modules, but not password protected modules. Therefore, I wish to find workbooks with password protected VBE modules and remove them to another folder.

I have VBA code that I can add to a module of any workbook, and list the modules that are password protected.
I have tested this code below and it works very well.

Sub Run_Macros()
     Call EnableReferences
     Call Prj_ProtectedNew
End Sub


Sub EnableReferences()
'Adds a reference to MS Visual Basic for Applications Extensibility 5.3
Dim Ref As Object, lRow As Long
If Not CheckRefEnabled("VBIDE") Then _
    ThisWorkbook.VBProject.References.AddFromGuid "{0002E157-0000-0000-C000-000000000046}", 5, 3
If Not CheckRefEnabled("VBScript_RegExp_55") Then _
    ThisWorkbook.VBProject.References.AddFromGuid "{3F4DACA7-160D-11D2-A8E9-00104B365C9F}", 5, 5
End Sub

Function CheckRefEnabled(sRefName As String) As Boolean
Dim Ref As Object
For Each Ref In ThisWorkbook.VBProject.References
    If Ref.Name = sRefName Then
        CheckRefEnabled = True
        Exit Function
    End If
Next Ref
End Function

Sub Prj_ProtectedNew()
'Macro that lists modules with password protection

'set a reference to MS Visual Basic for Applications Extensibility 5.3
On Error Resume Next
ThisWorkbook.VBProject.References.AddFromGuid _
"{0002E157-0000-0000-C000-000000000046}", 5, 3

Dim vbPrj As VBProject
Dim vbCom As VBComponent

Set vbPrj = Application.VBE.VBProject
Set vbCom = Application.VBE.VBProject.VBComponents

Dim x As Integer

x = 1

On Error Resume Next

For Each vbPrj In Application.VBE.VBProjects
    If vbPrj.Protection = vbext_pp_locked Then
        For Each vbCom In vbPrj.VBComponents
            Worksheets("Sheet1").Cells(x, 1) = vbPrj.Filename & ":" & vbPrj.Name & "." & vbCom.Name
            x = x + 1
        Next
    End If
Next

End Sub

My next objective was to build a procedure which will loop through workbooks and essentially run the above code for each workbook in a folder, and this is where I am having difficulties.

Here is my current code build for this procedure...

Sub CheckWbooksModProtection()

'Note: Set a reference to MS Visual Basic for Applications Extensibility 5.3

Dim x           As Integer
Dim Value       As String
Dim MasterWb   As Workbook
Dim vbPrj       As VBIDE.VBProject
Dim vbCom       As VBIDE.VBComponent

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

Set MasterWb = Workbooks("MasterWb.xlsm")
MasterWb.Application.EnableEvents = True

previousSecurity = Application.AutomationSecurity
Application.AutomationSecurity = msoAutomationSecurityForceDisable

On Error Resume Next
ThisWorkbook.VBProject.References _
    .AddFromGuid "{0002E157-0000-0000-C000-000000000046}", Major:=5, Minor:=0

myfolder = "C:\Users\DougM\Downloads\Test folder\"

MasterWb.Worksheets("Output").Range("C4") = myfolder
MasterWb.Worksheets("Output").Range("B7:D" & Rows.Count) = ""

x = 7
Value = Dir(myfolder)
Do Until Value = ""
    If Value = "." Or Value = ".." Then
    Else
        If Right(Value, 3) = "xls" Or Right(Value, 3) = "xlt" Or Right(Value, 4) = "xlsx" Or Right(Value, 4) = "xlsm" Or Right(Value, 4) = "xltm" Or Right(Value, 4) = "xlsb" Or Right(Value, 4) = "xlam" Then
            On Error Resume Next
            Workbooks.Open Filename:=myfolder & Value, ReadOnly:=True, IgnoreReadOnlyRecommended:=True, Password:="zzzzzzzzzzzz"
            
            Set vbPrj = Application.VBE.VBProject
            Set vbCom = Application.VBE.VBProject.VBComponents
            
            On Error Resume Next

            For Each vbPrj In Application.VBE.VBProjects
                If vbPrj.Protection = vbext_pp_locked Then
                    For Each vbCom In vbPrj.VBComponents
                       MasterWb.Worksheets("Output").Cells(x, 2) = vbPrj.Filename & ":" & vbPrj.Name & "." & vbCom.Name
                        x = x + 1
                    Next
                End If
            Next

            Workbooks(Value).Close False
            On Error GoTo 0
        End If
    End If
    Value = Dir
Loop

Application.AutomationSecurity = previousSecurity
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

This code simply doesn't add the filename/project name/module name to the output worksheet like the first macro does successfully.
I don't get any errors in the code; however, using F8 to go step by step, the code isn't detecting if a workbook has locked modules, and isn't cycling through modules either.

Would anyone please help me understand what I've done wrong?

Kind regards,

Doug.

 

[RoryA]

You can only password protect a project not a module. If it's protected for viewing, you shouldn't be able to view it or iterate it in code so that would raise an error, but your code is suppressing all errors.

Also, I'm not sure what this line is supposed to do:

Set vbPrj = Application.VBE.VBProject

as it makes no sense to me. There is no VBE.VBProject property.

 

[dougmarkham]

You can only password protect a project not a module. If it's protected for viewing, you shouldn't be able to view it or iterate it in code so that would raise an error, but your code is suppressing all errors.

Also, I'm not sure what this line is supposed to do:

Set vbPrj = Application.VBE.VBProject

as it makes no sense to me. There is no VBE.VBProject property.

Hi Rory,

Thanks for your reply.

I found the code in question here: how do i know if vba project is protected?
I'm not sure what that line does either, and couldn't find any mention of it here: Programming In The VBA Editor

However, it seems to work when I'm just using it in one workbook and not trying to loop through lots of workbooks.
For instance, when I run the code on workbook with a protected project, I get the below output:

It is listing everything in that project.

I noticed the cpearson source link uses:

Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents("Module1")

So I had presumed that the below was a generic reference to the VBProject/VB Components, or maybe old syntax.

Set vbPrj = Application.VBE.VBProject
Set vbCom = Application.VBE.VBProject.VBComponents

I'm wondering if the reason I can't use looping through workbooks is because when the workbooks.open event triggers, the resulting workbook doesn't have a reference to MS Visual Basic for Applications Extensibility 5.3 set. Not sure how I would add that reference via code to a workbook I've just opened! Would that even be possible?

 

[RoryA]

If a project is locked for viewing, your code should not be able to list its components. That would kind of defeat the purpose of locking it.