Remove module from multiple xlsm workbooks

[navic]

I use Excel 2013. I have dozens of workbooks in the folder. The format of all files is * .xlsm. Each workbook contains several modules. I want at once to remove/delete 'Module2' from all workbooks.
In the Master workbook I have this VBa macro below. When I run it, nothing happens, there is no error, but there are no expected results.

These items I have active/enabled
- Trust access to the VBA project object model
- Microsoft Visual Basic for Applications Extensibility 5.3
- Microsoft Scripting Runtime

'Option Explicit

Sub loopAllSubFolderSelectStartDirectory4()

Dim FSOLibrary As FileSystemObject
Dim FSOFolder As Object
Dim folderName As String

'Set the folder name to a variable
folderName = "C:\Temp\aaa\" 'path

'Set the reference to the FSO Library
Set FSOLibrary = New FileSystemObject

'Another Macro must call LoopAllSubFolders Macro to start
LoopAllSubFolders FSOLibrary.GetFolder(folderName)

End Sub

Sub LoopAllSubFolders(FSOFolder As Object)

Dim FSOSubFolder As Object
Dim FSOFile As Object

    'For each subfolder call the macro
    For Each FSOSubFolder In FSOFolder.SubFolders
        LoopAllSubFolders FSOSubFolder
    Next
    
        'For each file, print the name
        For Each FSOFile In FSOFolder.Files

                    'Insert the actions to be performed on each file
                    'Call DeleteModuleFromFolder1
                    Call DeleteModuleFromFolder2
        Next
End Sub
'''''''''''''''''''''''''''''''''''
Sub DeleteModuleFromFolder1()
    'Nothing happens. No modules were deleted. The code ran thru all the lines with no errors?
        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
    
        Set VBComps = ThisWorkbook.VBProject.VBComponents 'ActiveWorkbook?
        
        For Each VBComp In VBComps
            Select Case VBComp.Type
                Case vbext_ct_StdModule
                
                    Select Case modName
                        Case "Module1", "Module2" ' all the modules to delete get listed here
            Case "Module2" ' delete Module2
                            VBComps.Remove VBComp
                        
                    End Select
            End Select
        Next VBComp
End Sub
''''''''''''''''''''''''''''''''''''
    Sub DeleteModuleFromFolder2()
    'Nothing happens. No modules were deleted. The code ran thru all the lines with no errors?
        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
    
        Set VBComps = ActiveWorkbook.VBProject.VBComponents 'ThisWorkbook?
        
        For Each VBComp In VBComps
            Select Case VBComp.Type
                Case vbext_ct_StdModule
                    modName = VBComp.Name
                'If modName = "Module1" Then 'defined module
                '    VBComps.Remove VBComp
                'End If
                If modName = "Module2" Then
                    VBComps.Remove VBComp
                End If
            End Select
        Next VBComp
    End Sub

Can anyone tell me why this VBA is above, not work?

 

[Norie]

Have you stepped through the code with F8?

You might also try setting a few breakpoints (F9) at points in the code where you would expect something to happen, for example here.

Call DeleteModuleFromFolder2

If you put a breakpoint there and the code is never interrupted then you'll know that DeleteModuleFormFolder2 isn't even being called.

 

[navic]

Have you stepped through the code with F8?

Hi @Norie
Thank you for your advice. I tried and I do not see a problem (note, I'm a beginner in VBA)
I have some other VBA macro (from here: exceloffthegrid.com/vba-code-loop-files-folder-sub-folders/) that loop through files in a folder, also does not work. I suspect routines 'DeleteModuleFromFolder1' and 'DeleteModuleFromFolder2' (And that's the most important thing to me).
With respect.

Does anyone else have any help?

 

[Wanderer_geologist]

I lost my previous reply.

So first you may have to open the file in the first place, i don t see any such action in the first script. What the script is doing is listing the files in the folder.

The select case used after is not working as you assign a text value when it s designed to work with an integer. Use a simple if modulename=‘target’ to trigger your actions.

Some the conditions are run on variable which are not defined and assigned.

 

[navic]

Some the conditions are run on variable which are not defined and assigned.....Use a simple....

Hi @Wanderer_geologist
Thank you for the answer. I do not have enough knowledge in VBA to do it. I used VBA codes from the internet. I have tried several different combinations.
Can you modify my VBA code, if you have free time? (I want to remove Module2 only, from multiple xlsm workbooks in folder).
Thank you in advance.

 

[Wanderer_geologist]

I m really short on time currently. I pointed aspect of the code which where dysfunctional.
On the last part, I had a quick look and test. I m not familliar in handling those objects through code though I'm pretty certains that the module command is not working.

 

[navic]

I m really short on time currently.

OK, thank you for answering time.
Greeting

 

[Domenic]

First, you'll need to amend DeleteModuleFromFolder2 as follows...

1) It should include a parameter that accepts a FileSystemObject File.

2) It needs to open the workbook referred to by File.

3) Then it needs to loop through each VBComponent within the opened workbook, and delete Module2, if it exists.

4) Then it needs to save and close the opened workbook.

Then you'll need to pass the FileSystemObject File to DeleteModuleFromFolder2 when it's called from LoopAllSubFolders. By the way, you should always declare all your variables. And, since you're already using early binding, you might as well make sure that all variables are declared as specific objects, rather than generic ones. Also, you'll probably want to set ScreenUpdating to False to prevent the screen from updating and to speed up your code.

With all this in mind, your code can be amended as follows...

Option Explicit

Sub loopAllSubFolderSelectStartDirectory4()


    Dim FSOLibrary As Scripting.FileSystemObject
    Dim folderName As String
    
    Application.ScreenUpdating = False
    
    'Set the folder name to a variable
    folderName = "C:\Temp\aaa\" 'path
    
    'Set the reference to the FSO Library
    Set FSOLibrary = New FileSystemObject
    
    'Another Macro must call LoopAllSubFolders Macro to start
    LoopAllSubFolders FSOLibrary.GetFolder(folderName)
    
    Application.ScreenUpdating = True


End Sub


Sub LoopAllSubFolders(FSOFolder As Scripting.Folder)


    Dim FSOSubFolder As Scripting.Folder
    Dim FSOFile As Scripting.file


    'For each subfolder call the macro
    For Each FSOSubFolder In FSOFolder.SubFolders
        LoopAllSubFolders FSOSubFolder
    Next
    
    'For each file, print the name
    For Each FSOFile In FSOFolder.Files
        'Insert the actions to be performed on each file
        'Call DeleteModuleFromFolder1
        Call DeleteModuleFromFolder2(FSOFile)
    Next
        
End Sub
    
Sub DeleteModuleFromFolder2(FSOFile As Scripting.file)
    
    Dim VBProj As VBIDE.VBProject
    Dim VBComps As VBIDE.VBComponents
    Dim VBComp As VBIDE.VBComponent
    Dim wb As Workbook
    Dim modName As String
    
    Set wb = Workbooks.Open(FSOFile.Path)


    Set VBComps = wb.VBProject.VBComponents
    
    For Each VBComp In VBComps
        Select Case VBComp.Type
            Case vbext_ct_StdModule
                modName = VBComp.Name
            'If modName = "Module1" Then 'defined module
            '    VBComps.Remove VBComp
            'End If
            If modName = "Module2" Then
                VBComps.Remove VBComp
                'Exit For  'uncomment this line if you're only searching for Module2 so that you can exit the for/next loop
            End If
        End Select
    Next VBComp
    
    wb.Close SaveChanges:=True
    
End Sub

Hope this helps!

 

[navic]

Hi @Domenic
Thank you very much for your help and your free time.
Best regards

 

[navic]

Problem solved