Excel tutorials

7. Delete a specific VBA module in multiple workbooks within one folder

Continue tutorials from the previous web site. In this part of the tutorial, the fifth in the order will show how we can suddenly remove the VBA modules from each workbook that is in the same folder.

In our case (if you have been following the tutorial from the beginning) I have inserted Module1 in a certain step of the organization. In this module I set up a VBA macro that was used to duplicate the template.xlsm Workbook or to duplicate 100 copies of that workbook.

We no longer need this 'Module1' and it is located in each Workbook within the same folder. So I have 100 Excel files and in each i need to delete Module1.

Copy this VBA macro below into a newly-opened workbook and run it.

Option Explicit
'published by www.ic-ims.com
Sub loopAllSubFolderSelectStartDirectory()
'https://exceloffthegrid.com/vba-code-loop-files-folder-sub-folders/
Dim FSOLibrary As Scripting.FileSystemObject
Dim folderName As String

Application.ScreenUpdating = False

'Set the folder name to a variable
folderName = "C:\Temp\ccc\" 'defined path - change to suit

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

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

'Message Box when tasks are completed
MsgBox "Task Completed!"

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 DeleteModuleFromFolder(FSOFile)
Next

End Sub

Sub DeleteModuleFromFolder(FSOFile As Scripting.File)
'Domenic corrected error
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
'delete a specific vba module
If modName = "Module1" 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

To make this VBA macros work properly, you need to set the following

- Trust access to the VBA project object model (After finishing the work, turn this option OFF for your safety! If you leave it turned ON, that is a security risk for you!)
- Microsoft Visual Basic for Applications Extensibility 5.3
- Microsoft Scripting Runtime

Trust access to the VBA project object model

 

Microsoft Visual Basic for Applications Extensibility 5.3

 

After you start VBA macros, wait for a while. I applied this VBA macro to 100 Excel files and the duration was about 4 minutes. In this way I removed the VBA modules from each workbook that is in the same folder

This concludes another step that is part of an organization that I have imagined at the beginning of this long tutorial that runs on multiple web pages.

And at the end of this tutorial section, you can Download  this to your computer's ZIP file, which contains all created folders, sub-folders and several books in the first month of 2019, where I did this tutorial.

List all topics related to complete tutorials

  1. Creating Top Folders and Sub Folders in One Step
  2. How to copy a workbook 100 times and create multiple workbooks from the list in the 'A' column
  3. Rename the first in the order of the worksheet, in multiple workbooks in the same folder
  4. How to remove the last Worksheet in multiple Workbooks in the same folder
  5. How to remove a particular VBA module in multiple workbooks in the same folder
  6. How to save all workbooks as values without formulas in all sub-folders

Click to continue tutorials: ------ >>>>>> The next part of the tutorial Item 6 from list above