Home
Search
 
What's New
Index
Books
Links
Q & A
Newsletter
Banners
 
Feedback
Tip Jar
 
C# Helper...
 
XML RSS Feed
Follow VBHelper on Twitter
 
 
 
MSDN Visual Basic Community
 
 
 
 
 
TitleUse VBA code to save an Excel workbook while removing macro code
DescriptionThis example shows how to use VBA code to save an Excel workbook while removing macro code.
KeywordsVBA, Excel, macro, remove macros
CategoriesOffice, Files and Directories
 
First use Tools\References to set a reference to "Microsoft Visual Basic for Applications Extensibility 5.3" (or whatever version you have).

Use the Application object's GetSaveAsFilename function to get a file name.

Next loop through the Visual Basic IDE components. Remove any code modules, UserForms, and class modules. For other objects (including Worksheets and the Workbook), remove the lines of code from the object. Finally save the modified file with the new file name.

 
' Use Tools\References to set a reference to
' "Microsoft Visual Basic for Applications
' Extensibility 5.3" (or whatever version you have).
Private Sub cmdSaveAs_Click()
Dim file_name As Variant
Dim comps As VBIDE.VBComponents
Dim comp As VBIDE.VBComponent

    ' Get the file name.
    file_name = Application.GetSaveAsFilename( _
        FileFilter:="Excel Files,*.xls,All Files,*.*", _
        Title:="Save As File Name")

    ' See if the user canceled.
    If file_name = False Then Exit Sub

    ' Save the file with the new name.
    If LCase$(Right$(file_name, 4)) <> ".xls" Then
        file_name = file_name & ".xls"
    End If

    ' Remove modules, forms, and classes from the copy.
    ' See
    ' http://www.cpearson.com/excel/vbe.htm#DeleteAllVBA.
    Set comps = ActiveWorkbook.VBProject.VBComponents
    For Each comp In comps
        Select Case comp.Type
            Case vbext_ct_StdModule, _
                 vbext_ct_MSForm, _
                 vbext_ct_ClassModule
                    comps.Remove comp
            Case Else ' Worksheet, Workbook, etc.
                comp.CodeModule.DeleteLines 1, _
                    comp.CodeModule.CountOfLines
        End Select
    Next comp

    ' Save a new copy.
    ActiveWorkbook.SaveAs Filename:=file_name
End Sub
 
 
Copyright © 1997-2006 Rocky Mountain Computer Consulting, Inc.   All rights reserved.
  Updated