Macro that will remove all macros from a project in Excel 2003 and/or 2007

I need a code that will remove all macros from a project in 2003 and/or 2007. I have the code below, but it requires "Microsoft Visual Basic for Applications Extensibility library" and i can't find it in the exhaustive References list. So unless there's a simpler code that works, exactly how far down the Ecel 2003 References list is the extensibility library reference?

Thanlks,
John
LVL 1
gabrielPennybackReliability Business Tools Analyst IIAsked:
Who is Participating?
 
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
The routine below, PurgeCodeInWorkbook, removes all source code from a workbook. The routine will not work on the workbook in which it is located and running. The following sample code illustrates how to save a copy of a workbook and use the routine to purge all VBA code from it.

[Begin Code Segment]

   Const FileName = "C:\Full\Path\To\Workbook Copy.xls"
   ThisWorkbook.SaveCopyAs FileName
   Workbooks.Open FileName, False
   PurgeCodeInWorkbook ActiveWorkook
   ActiveWorkbook.Save
   ActiveWorkbook.Close False

[End Code Segment]

The routine for purging the VBA source code follows below.

[Begin Code Segment]

Public Sub PurgeCodeInWorkbook( _
      ByVal TargetWorkbook As Workbook _
   )

' Purge all VBA code in the specified workbook. The specified workbook must not
' be in the workbook in which this code is running.
   
   Dim VBComponent As Variant
   Dim VBCodeModule As CodeModule
   
   If TargetWorkbook Is ThisWorkbook Then Stop ' Can't delete code in the same workbook
   
   For Each VBComponent In TargetWorkbook.VBProject.VBComponents
      Set VBCodeModule = VBComponent.CodeModule
      If VBCodeModule.CountOfLines > 0 Then
         If VBComponent.Type = 1 Then
            TargetWorkbook.VBProject.VBComponents.Remove VBComponent
         Else
            VBCodeModule.DeleteLines 1, VBCodeModule.CountOfLines
         End If
      End If
   Next VBComponent

End Sub

[End Code Segment]

Kevin
0
 
dlmilleConnect With a Mentor Commented:
Of course, a very simple way to purge code from a workbook is to save it as a macro-free workbook.

File SaveAs ExcelWorkbook or Exce 97-2003 Workbook.

Its that simple.  I had never done this before so I took a sample excel file with lots of macros in several modules and sheet codepages, saved as macro free and presto - the job was done.

Dave
0
 
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
>exactly how far down the Ecel 2003 References list is the extensibility library reference?

The list is in alphabetical order. Scroll down to "Microsoft V..." and it will be there.

Kevin
0
 
gabrielPennybackReliability Business Tools Analyst IIAuthor Commented:
Thanks!

John
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.