How To Delete an Excel Macro by Using Automation

[ScottG]

We automate Excel (v10) to generate a bunch a workbooks which populate and
format themselves with an imbedded macro. Is there any way to
programmatically delete the macro before distributing the documents?

[Bob Phillips]

Here is an example to delete a procedure


Const vbext_pk_Proc = 0


'----------------------------------------------------------------
Sub DeleteProcedure()
'----------------------------------------------------------------
Dim oCodeModule As Object
Dim iStart As Long
Dim cLines As Long

Set oCodeModule =
ThisWorkbook.VBProject.VBComponents("Module1").CodeModule
With oCodeModule
On Error GoTo dp_err:
iStart = .ProcStartLine("myProc", vbext_pk_Proc)
cLines = .ProcCountLines("myProc", vbext_pk_Proc)
.DeleteLines iStart, cLines
On Error GoTo 0
Exit Sub
End With

dp_err:
If Err.Number = 35 Then
MsgBox "Procedure does not exist"
End If
End Sub



--

HTH

RP
(remove nothere from the email address if mailing direct)


"ScottG" <[email protected]> wrote in message
news:[email protected]...
> We automate Excel (v10) to generate a bunch a workbooks which populate and
> format themselves with an imbedded macro. Is there any way to
> programmatically delete the macro before distributing the documents?

[Tom Ogilvy]

http://www.cpearson.com/excel/vbe.htm

should give you a start.

--
Regards,
Tom Ogilvy

"ScottG" <[email protected]> wrote in message
news:[email protected]...
> We automate Excel (v10) to generate a bunch a workbooks which populate and
> format themselves with an imbedded macro. Is there any way to
> programmatically delete the macro before distributing the documents?

[ScottG]

Thanks, that worked perfectly.

Scott

"Bob Phillips" wrote:

> Here is an example to delete a procedure
>
>
> Const vbext_pk_Proc = 0
>
>
> '----------------------------------------------------------------
> Sub DeleteProcedure()
> '----------------------------------------------------------------
> Dim oCodeModule As Object
> Dim iStart As Long
> Dim cLines As Long
>
> Set oCodeModule =
> ThisWorkbook.VBProject.VBComponents("Module1").CodeModule
> With oCodeModule
> On Error GoTo dp_err:
> iStart = .ProcStartLine("myProc", vbext_pk_Proc)
> cLines = .ProcCountLines("myProc", vbext_pk_Proc)
> .DeleteLines iStart, cLines
> On Error GoTo 0
> Exit Sub
> End With
>
> dp_err:
> If Err.Number = 35 Then
> MsgBox "Procedure does not exist"
> End If
> End Sub
>
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "ScottG" <[email protected]> wrote in message
> news:[email protected]...
> > We automate Excel (v10) to generate a bunch a workbooks which populate and
> > format themselves with an imbedded macro. Is there any way to
> > programmatically delete the macro before distributing the documents?
>
>
>

[keepITcool]

caveat:
when users run those macros they must have a security setting.
"Allow access to Visual Basic Project"

if not your code will fail.

in xl2002 it could be changed via Macro Options by user..
in xl2003 this setting can only be changed with admin priviliges
via registry... in HKLM


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


ScottG wrote :

> Thanks, that worked perfectly.
>
> Scott
>
> "Bob Phillips" wrote:
>
> > Here is an example to delete a procedure
> >
> >
> > Const vbext_pk_Proc = 0
> >
> >
> > '----------------------------------------------------------------
> > Sub DeleteProcedure()
> > '----------------------------------------------------------------
> > Dim oCodeModule As Object
> > Dim iStart As Long
> > Dim cLines As Long
> >
> > Set oCodeModule =
> > ThisWorkbook.VBProject.VBComponents("Module1").CodeModule
> > With oCodeModule
> > On Error GoTo dp_err:
> > iStart = .ProcStartLine("myProc", vbext_pk_Proc)
> > cLines = .ProcCountLines("myProc", vbext_pk_Proc)
> > .DeleteLines iStart, cLines
> > On Error GoTo 0
> > Exit Sub
> > End With
> >
> > dp_err:
> > If Err.Number = 35 Then
> > MsgBox "Procedure does not exist"
> > End If
> > End Sub
> >
> >
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "ScottG" <[email protected]> wrote in message
> > news:[email protected]...
> > > We automate Excel (v10) to generate a bunch a workbooks which
> > > populate and format themselves with an imbedded macro. Is there
> > > any way to programmatically delete the macro before distributing
> > > the documents?
> >
> >
> >

[Halray]

Bob,

I tied the code, but I must be doing soemthing wrong because I get the
following error:

Method 'VBProject' of object '_Workbook' failed.


Can you help me with this? Many Thanks!

Ray Collins


--
Ray


"Bob Phillips" wrote:

> Here is an example to delete a procedure
>
>
> Const vbext_pk_Proc = 0
>
>
> '----------------------------------------------------------------
> Sub DeleteProcedure()
> '----------------------------------------------------------------
> Dim oCodeModule As Object
> Dim iStart As Long
> Dim cLines As Long
>
> Set oCodeModule =
> ThisWorkbook.VBProject.VBComponents("Module1").CodeModule
> With oCodeModule
> On Error GoTo dp_err:
> iStart = .ProcStartLine("myProc", vbext_pk_Proc)
> cLines = .ProcCountLines("myProc", vbext_pk_Proc)
> .DeleteLines iStart, cLines
> On Error GoTo 0
> Exit Sub
> End With
>
> dp_err:
> If Err.Number = 35 Then
> MsgBox "Procedure does not exist"
> End If
> End Sub
>
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "ScottG" <[email protected]> wrote in message
> news:[email protected]...
> > We automate Excel (v10) to generate a bunch a workbooks which populate and
> > format themselves with an imbedded macro. Is there any way to
> > programmatically delete the macro before distributing the documents?
>
>
>