Sub to check files in folder and remove Module1
 

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

Sub to check files in folder and remove Module1
 

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)




On Thu, 30 Oct 2008 22:09:18 +0800, "Max"
wrote:

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

Sub to check files in folder and remove Module1
 

Many thanks, Chip
It works well.