EDN Admin
Well-known member
I have an Excel file ready to save but I need to strip out the macros before saving it so that the user doesnt have the inconvenience of deciding whether to save it as a macro free workbook or not (the macros can go as the final file just contains
reports). I have decided perhaps the best way to do this is simply to delete the macro module. I have searched and searched on the Net but apart from a C# solution (which still doesnt work for the following reason) I cannot seem to address parts of
the VBA code below (when transformed into VB .NET code):
Sub DeleteModule()<br/>
Dim VBProj As VBIDE.VBProject<br/>
Dim VBComp As VBIDE.VBComponent<br/>
Set VBProj = ActiveWorkbook.VBProject<br/>
Set VBComp = VBProj.VBComponents("Module1")<br/>
VBProj.VBComponents.Remove VBComp<br/>
End Sub
My workbook is called xlReportWorkbook. But when I go to address VBIDE and VBProject and VBComponent with VB .NET code I cannot work out the addressing hierarchy and intellisense is no help either. I have tried using xlReportApp (that is my "Application"
object) also but that doesnt get me anywhere either.
I try this and get nowhere:
Dim VBProj (as Excel.what?) = xlReportWorkbook .VBProject (but there is no VBProject addressable)<br/>
Dim VBComp (as Excel.what?) = VBProj.VBComponents("MacroModul") (but there is no VBComponent addressable) <br/>
VBProj.VBComponents.Remove VBComp
Can somebody help me on this please? I have spent quite a bit of time trying to find a solution by myself but now I need some help. Thanks in advance.
View the full article
reports). I have decided perhaps the best way to do this is simply to delete the macro module. I have searched and searched on the Net but apart from a C# solution (which still doesnt work for the following reason) I cannot seem to address parts of
the VBA code below (when transformed into VB .NET code):
Sub DeleteModule()<br/>
Dim VBProj As VBIDE.VBProject<br/>
Dim VBComp As VBIDE.VBComponent<br/>
Set VBProj = ActiveWorkbook.VBProject<br/>
Set VBComp = VBProj.VBComponents("Module1")<br/>
VBProj.VBComponents.Remove VBComp<br/>
End Sub
My workbook is called xlReportWorkbook. But when I go to address VBIDE and VBProject and VBComponent with VB .NET code I cannot work out the addressing hierarchy and intellisense is no help either. I have tried using xlReportApp (that is my "Application"
object) also but that doesnt get me anywhere either.
I try this and get nowhere:
Dim VBProj (as Excel.what?) = xlReportWorkbook .VBProject (but there is no VBProject addressable)<br/>
Dim VBComp (as Excel.what?) = VBProj.VBComponents("MacroModul") (but there is no VBComponent addressable) <br/>
VBProj.VBComponents.Remove VBComp
Can somebody help me on this please? I have spent quite a bit of time trying to find a solution by myself but now I need some help. Thanks in advance.
View the full article