goblin
Active Member
- Joined
- Apr 16, 2003
- Messages
- 469
I'm trying to delete all macros from an open workbook and for some reason it doesn't work. I got the code from Chip Pearson's site and it worked fine in XL2000, but now doesn't work in XL 2002.
I have enabled the 'Trust access to VB project' under options->security.
I need to use 'current file' as a template file for a lot of other files, except the other files should not have the macros that the original file has. It goes something like:
DeleteAllVBA is like this:
This code clears all code from all modules, but it doesn't remove modules itself. Thus when the template file is used, it will always display the macro warning. How do I remove that code module?????!!!!
I have enabled the 'Trust access to VB project' under options->security.
I need to use 'current file' as a template file for a lot of other files, except the other files should not have the macros that the original file has. It goes something like:
Code:
Function PrepareStrippedTemplate() As Workbook
Dim wb As Workbook
ThisWorkbook.SaveCopyAs Application.DefaultFilePath & "\BudgetTmpl.xls"
Set wb = Workbooks.Open(Application.DefaultFilePath & "\BudgetTmpl.xls", False, False)
wb.Sheets("tables").Range("A2", .Range("M2").End(xlDown)).Clear
DeleteAllVBA wb
Set PrepareStrippedTemplate = wb
End Function
DeleteAllVBA is like this:
Code:
Sub DeleteAllVBA(wb As Workbook)
Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents
Set VBComps = wb.VBProject.VBComponents
For Each VBComp In VBComps
Select Case VBComp.Type
Case vbext_ct_StdModule, vbext_ct_MSForm, vbext_ct_ClassModule
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
VBComps.Remove VBComp
Case Else
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp
End Sub
This code clears all code from all modules, but it doesn't remove modules itself. Thus when the template file is used, it will always display the macro warning. How do I remove that code module?????!!!!