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
Can anyone tell me why this VBA is above, not work?
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
Code:
'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?