Remove VBA code from all books in folder
I am trying to create code to remotely loop through a folder of .xls files and remove the macros contained within. So far I have the individual components worked, but I am having difficulty activating various books and then the software "Microsoft Visual Basic for Application Extensibility 5.3" is mentioned in each file.
Thank!
Sub LoopFiles()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
strPath = ' enter path here
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.DisplayAlerts = False
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFso.GetFolder(strPath)
For Each objfile In objFolder.Files
If objFso.GetExtensionName(objfile.Path) = "xls" Then
Set Objworkbook = objExcel.Workbooks.Open(objfile.Path)
' Include your code to work with the Excel object here
Objworkbook.Activate
AddReference (objfile)
Objworkbook.Close True 'Save changes
End If
Next
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub
Sub AddReference(FileRequired)
FileRequired.Activate
'MsgBox "Sheet: " & ActiveWorkbook.Name
ActiveWorkbook.VBProject.References.AddFromGuid _
GUID:="{0002E157-0000-0000-C000-000000000046}", _
Major:=5, Minor:=3
End Sub
Sub DeleteAllVBACode()
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Set VBProj = ActiveWorkbook.VBProject
For Each VBComp In VBProj.VBComponents
If VBComp.Type = vbext_ct_Document Then
Set CodeMod = VBComp.CodeModule
With CodeMod
.DeleteLines 1, .CountOfLines
End With
Else
VBProj.VBComponents.Remove VBComp
End If
Next VBComp
End Sub
source to share
As I mentioned in my comments, you don't need to add a link to Microsoft Visual Basic for Application Extensibility 5.3
to remove code from files. Let's take a look at this little exercise.
1 ). Create an Excel file
2 ). Paste this code into the module
Sub Sample1()
MsgBox "A"
End Sub
3 ). Save the above file asC:\Sample.xls
4 ). Close the file
5 ). Open a new excel file and paste this code into the module
Option Explicit
'~~> Trust Access To Visual Basics Project must be enabled.
Sub Sample2()
Dim wb As Workbook
Dim i As Long
'~~> Replace this with the relevant file
'~~> We can open the files in a loop as well
Set wb = Workbooks.Open("C:\Sample.xls")
On Error Resume Next
With wb.VBProject
'~~> Remove the components
For i = .VBComponents.Count To 1 Step -1
.VBComponents.Remove .VBComponents(i)
Next i
'~~> Remove the code lines
For i = .VBComponents.Count To 1 Step -1
.VBComponents(i).CodeModule.DeleteLines _
1, .VBComponents(i).CodeModule.CountOfLines
Next i
End With
On Error GoTo 0
End Sub
6 ) Make sure "Trust access to Visual Basic Project" is enabled
7 ) RunSample2()
You will see that the code Sample.xls
has been removed and we haven't even set a link to Microsoft Visual Basic for Application Extensibility 5.3
.
source to share