Results 1 to 5 of 5

How to save workbook without macro

This is a discussion on How to save workbook without macro within the Excel Questions forums, part of the Question Forums category; After populating my workbook using a macro I want to save workbook (with new name) but without macro.Copying the data ...

  1. #1
    New Member
    Join Date
    Dec 2002
    Posts
    1

    Default

    After populating my workbook using a macro I want to save workbook (with new name) but without macro.Copying the data to another workbook loose my sheet formating.

  2. #2
    dk
    dk is offline
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,932

    Default

    Hello,

    You can do this with a macro. Copy this code into a standard module.

    Please note the bit about setting the reference to the extensibility library.

    Code:
    Sub SaveWithoutMacros()
    
    'Purpose : To save a copy of the active workbook without macros
    
    'PLEASE NOTE : You must choose Tools, References and select the
    'Microsoft Visual Basic for Applications Extensibility library
    
    Dim vFilename As Variant
    Dim wbActiveBook As Workbook
    Dim VBComp As VBIDE.VBComponent
    Dim VBComps As VBIDE.VBComponents
    
    
    On Error GoTo CodeError
    
    
    'Get a filename to save as
    vFilename = Application.GetSaveAsFilename(filefilter:="Microsoft Excel Workbooks,*.xls", _
                                            Title:="Save Copy Without Macros")
                                            
    If vFilename = False Then Exit Sub  'User chose Cancel
    
    ActiveWorkbook.SaveCopyAs vFilename
    Set wbActiveBook = Workbooks.Open(vFilename)
    
    
    'Now strip all VBA, modules, userforms from the copy
    'This code is from Chip Pearson's website http://www.cpearson.com
    
    Set VBComps = wbActiveBook.VBProject.VBComponents
    
    For Each VBComp In VBComps
       Select Case VBComp.Type
          Case vbext_ct_StdModule, vbext_ct_MSForm, _
                vbext_ct_ClassModule
             VBComps.Remove VBComp
          Case Else
             With VBComp.CodeModule
                .DeleteLines 1, .CountOfLines
             End With
       End Select
    Next VBComp
    
    wbActiveBook.Save
    
    Exit Sub
    
    CodeError:
    MsgBox Err.Description, vbExclamation, "An Error Occurred"
    
    End Sub

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339

    Default

    If you have no code in worksheet modules, (or even if you have and want to keep it), the following will copy all your sheets into a new workbook without copying standard modules and should preserve the formatting-

    Public Sub CopyToNewWorkbook()
    Dim sh As Object

    ThisWorkbook.Activate
    For Each sh In ThisWorkbook.Sheets
    sh.Select False
    Next sh

    ThisWorkbook.Windows(1).SelectedSheets.Copy

    End Sub

  4. #4
    New Member
    Join Date
    Nov 2010
    Posts
    1

    Default Re: How to save workbook without macro

    I realize that this is an old thread, but I came across it while looking to perform this action (save without macros). I copied the code above, added a browse to file and asked the user "Are you sure?" before performing the save, then closed the file at the end. I have not altered the body of the code at all, but I am having the following problem:

    I need to run the code twice on a file to get all the macros stripped out. The first time clears out all code in the Sheets (where I have buttons to call subroutines in the Modules), but leaves the UserForms and Modules intact. The second time clears these out. Any ideas what the problem is here?

  5. #5
    New Member
    Join Date
    Nov 2010
    Posts
    4

    Post Re: How to save workbook without macro

    I ain't an expert with VBA but more of a record macro, google it and copy and paste guy.

    Here is code that I use to archive without the macro.. it's basic copy and paste for a single sheet but if effective for my purposes. Hope this helps someone else.

    --------------------------------------------------------------------------

    Sub Archive()

    'Use MsgBox to ask user for confirmation
    Dim iSave As Integer

    iSave = MsgBox(prompt:="Archive this report?", _
    Buttons:=vbYesNo, Title:=" Archive Report")

    Application.ScreenUpdating = False

    If iSave = vbYes Then

    'Code to save data in the offer sheet to a new workbook and save to predetermined location
    Sheets("Offer").Visible = True 'show sheet
    Sheets("Offer").Select

    Cells.Select 'select all cells
    Selection.Copy

    Workbooks.Add 'create new workbook

    ActiveSheet.Paste

    'formatting
    Rows("1:1").EntireRow.AutoFit
    Range("B2").Select
    ActiveWindow.FreezePanes = True
    Range("A1").Select

    Reportdate = Format(Now, "YYYYMMDD HHMM") 'Reportdate is a date and time of now

    'filesavename predetermined
    filesavename = ("Report created by ") & (Application.UserName) & (" on ") & (Reportdate) & (".xls")

    ' MsgBox "Save as " & FileSaveName
    'If vbOK Then

    'savelocation predetermined
    ActiveWorkbook.SaveAs Filename:="\\SERVER\Archive\" & (" ") & (Reportdate)

    'hide offer sheet and bring back userform1
    UserForm1.hide
    Workbooks("Processing.xls").Activate 'Current main file
    Range("D5").Select

    Sheets("Offer").Visible = False 'hide sheet
    Sheets("Info").Select
    UserForm1.show

    Application.ScreenUpdating = True

    ElseIf iSave = vbNo Then 'if no, stop
    Exit Sub

    End If

    End Sub

    --------------------------------------------------------------------------

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com