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").Cod eModule
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" wrote in message
...
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?

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

should give you a start.

--
Regards,
Tom Ogilvy

"ScottG" wrote in message
...
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?

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").Cod eModule
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" wrote in message
...
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?

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").Cod eModule
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" wrote in message
...
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,

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").Cod eModule
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" wrote in message
...
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?