Delete VBA using VBA

[rmscho02]

I'm having problems deleting a VBA module in a workbook. I'm using the code from CPearson and for some reason it's not able to delete Module1. It's able to remove the text from ThisWorkbook, but the only bas is Module1 and that's not deleting.

Ultimately I'm looking for a way to save a workbook without VBA the distributed file doesn't contain any code for the recipient to worry about (and so they aren't prompted about macros when opening the file), so any other solutions are welcome. Thanks!


Sub DeleteAllVBACode()
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule

Set VBProj = ActiveWorkbook.VBProject

For Each VBComp In VBProj.VBComponents
If VBComp.Type = vbext_ct_Document Then
Set CodeMod = VBComp.CodeModule
With CodeMod
.DeleteLines 1, .CountOfLines
End With
Else
VBProj.VBComponents.Remove VBComp
End If
Next VBComp
End Sub
</pre>

 

[VoG]

Look at Deleting a module from a project http://www.cpearson.com/excel/VBE.aspx

 

[rmscho02]

Thanks VOG, but I tried that solution as well. Same result--the Module1 piece is the only thing that won't delete.

I'm running the code in Workbook A to delete the code in workbook B, so all I did was change the reference from ActiveWorkbook to Workbook B.