How to delte macros in another workbook
 

Hi,

Here I got following VBA code to delete macros from another opened workbook.

Public Sub DeleteAllVBA(wbk As Workbook)
On Error Resume Next
Dim wbkTemp As Workbook
Dim VBComps
Dim VBComp

Set wbkTemp = Workbooks(wbk.Name)
Set VBComps = wbkTemp.VBProject.VBComponents

For Each VBComp In VBComps
Debug.Print VBComp.Name
Select Case VBComp.Type
Case vbext_ct_StdModule, vbext_ct_MSForm, vbext_ct_ClassModule
VBComps.Remove VBComp
Case Else
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp

End Sub

Actually my requirement is, I'm having some macros (sheet, this workbook
evevents and standard modules) in Book1.xls including above code. When I
click a button on sheet1 it creates another .xls file

Button event starts here ...
It creates a copy of 'Book1.xls' with 'Book1_*.xls' - (*-last modified date
& time) using File system object. Once it is created I'm opening the newly
created file ('Book1_*.xls') and calling 'DeleteAllVBA' function to delete
all macros. But it is deleting only the code in sheets, thisworkbook and some
of the standard moudles i.e., not all modules.

later closing newly created file ('Book1_*.xls').
Button event ends upto here ...

Then opened 'Book1_*.xls', I found that some of the modules are not deleted,
while this work book is opend in this situation I executed 'DeleteAllVBA' by
passing file name at this time its removed all the modules. but its not done
in button event.

I executed the same code (button event code) in debug mode, its executing
'VBComps.Remove VBComp' but it is not deleting the modules


Can any one help me out, what is root cause in not deleting the modules.

Regards
Vinod