Remove all macros and objects in Excel, Save and Quit

[kanchianil]

Hi Everyone,
I have a code in excel that after processing saves to a new name, and
tries to remove all the code from the activeworkbook, save all the open

workbooks and quits Excel.

The problem is:


The Standard module and the form do not get deleted after the saved
workbook is opened. Though the code gets deleted from the objects.


Any ideas on why this is happening. Here's the code.


Sub main()
RemoveAllMacros Activeworkbook
End sub


Sub RemoveAllMacros(ByVal objdocument As Object)


' deletes all VBProject components from objDocument
' removes the code from built-in components that can't be deleted
' use like this: RemoveAllMacros ActiveWorkbook ' in Excel
' requires a reference to the
' Microsoft Visual Basic for Applications Extensibility library
'Dim objdocument As Workbook
Dim i, l As Long
Dim w As Workbook


If objdocument Is Nothing Then Exit Sub
i = 0
On Error Resume Next
i = objdocument.VBProject.VBComponents.count
On Error GoTo 0
If i < 1 Then ' no VBComponents or protected VBProject
MsgBox "The VBProject in " & objdocument.Name & _
" is protected or has no components!", _
vbInformation, "Remove All Macros"
Exit Sub
End If
With objdocument.VBProject
For i = .VBComponents.count To 1 Step -1
l = 1
On Error Resume Next
l = .VBComponents(i).CodeModule.CountOfLines
.VBComponents(i).CodeModule.DeleteLines 1, l
' clear lines
On Error GoTo 0
Next i
End With
With objdocument.VBProject
For i = .VBComponents.count To 1 Step -1
'On Error Resume Next
If (.VBComponents(i).Type = vbext_ct_StdModule Or _
.VBComponents(i).Type = vbext_ct_MSForm) Then
.VBComponents.remove .VBComponents(i)
End If
' delete the component
'On Error GoTo 0
Next i
End With


Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each w In Application.Workbooks
w.Save
Next w


For Each w In Application.Workbooks
w.Saved = True
Next w


Set objdocument = Nothing
Application.ScreenUpdating = True
'Application.DisplayAlerts = True
Application.Quit


End Sub