How To Delete an Excel Macro by Using Automation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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)
 
Thanks, that worked perfectly.

Scott

Bob Phillips said:
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)
Click to expand...
 
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 :
 
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
 
Back
Top