Sub to check files in folder and remove Module1

M

Max

I've got a bunch of .xls in say, D:\Maxh\Excel

Is there a sub I can use to run through that bunch of .xls
and remove Module1 from each .xls, if the Module1 exists?

Thanks
 
C

Chip Pearson

Try some code like the following:

Sub AAA()
Dim FName As String
Dim WB As Workbook
Dim DirName As String

DirName = "C:\Test" '<< CHANGE DIRECTORY
ChDrive DirName
ChDir DirName

FName = Dir(DirName & "\*.xls")
Do Until FName = vbNullString
Set WB = Workbooks.Open(FName)
On Error Resume Next
With WB.VBProject.VBComponents
.Remove .Item("Module1")
End With
On Error GoTo 0
WB.Close savechanges:=True
FName = Dir()
Loop
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
Top