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.
'published by www.ic-ims.com
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
'Message Box when tasks are completed
MsgBox "Task Completed!"
Application.ScreenUpdating = True
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
'For each file, print the name
For Each FSOFile In FSOFolder.Files
'Insert the actions to be performed on each file
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
modName = VBComp.Name
'delete a specific vba module
If modName = "Module1" Then
'Exit For 'uncomment this line if you're only searching for Module2 so that you can exit the for/next loop
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
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
- Creating Top Folders and Sub Folders in One Step
- How to copy a workbook 100 times and create multiple workbooks from the list in the 'A' column
- Rename the first in the order of the worksheet, in multiple workbooks in the same folder
- How to remove the last Worksheet in multiple Workbooks in the same folder
- How to remove a particular VBA module in multiple workbooks in the same folder
- 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