How to use VBA SaveAs without closing calling workbook?

savecopyas vba
vba close workbook without saving
excel vba save copy of workbook without opening
excel vba save workbook without prompt
excel vba save sheet as new workbook without opening
vba saveas vs savecopyas
excel vba saveas
vba how to save a copy of a workbook

I want to:

  • Do data manipulation using a Template workbook
  • Save a copy of this work book as .xlsx (SaveCopyAs doesn't let you change filetypes, otherwise this would be great)
  • Continue showing original template (not the "saved as" one)

Using SaveAs does exactly what is expected - it saves the workbook while removing the macros and presents me the view of the newly created SavedAs workbook.

This unfortunately means:

  • I no longer am viewing my macro enabled workbook unless I reopen it
  • Code execution stops at this point because
  • Any macro changes are discarded if I forget to save (note: for a production environment this is ok, but, for development, it's a huge pain)

Is there a way I can do this?

'current code
Application.DisplayAlerts = False
templateWb.SaveAs FileName:=savePath, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
templateWb.Activate
Application.DisplayAlerts = True

'I don't really want to make something like this work (this fails, anyways)
Dim myTempStr As String
myTempStr = ThisWorkbook.Path & "\" & ThisWorkbook.Name
ThisWorkbook.Save
templateWb.SaveAs FileName:=savePath, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWorkbook.Close
Workbooks.Open (myTempStr)

'I want to do something like:
templateWb.SaveCopyAs FileName:=savePath, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False 'SaveCopyAs only takes one argument, that being FileName

Also note while SaveCopyAs will let me save it as a different type (ie templateWb.SaveCopyAs FileName:="myXlsx.xlsx") this gives an error when opening it because it now has an invalid file format.

Here is a much faster method than using .SaveCopyAs to create a copy an then open that copy and do a save as...

As mentioned in my comments, this process takes approx 1 second to create an xlsx copy from a workbook which has 10 worksheets (Each with 100 rows * 20 Cols of data)

Sub Sample()
    Dim thisWb As Workbook, wbTemp As Workbook
    Dim ws As Worksheet

    On Error GoTo Whoa

    Application.DisplayAlerts = False

    Set thisWb = ThisWorkbook
    Set wbTemp = Workbooks.Add

    On Error Resume Next
    For Each ws In wbTemp.Worksheets
        ws.Delete
    Next
    On Error GoTo 0

    For Each ws In thisWb.Sheets
        ws.Copy After:=wbTemp.Sheets(1)
    Next

    wbTemp.Sheets(1).Delete
    wbTemp.SaveAs "C:\Blah Blah.xlsx", 51

LetsContinue:
    Application.DisplayAlerts = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

save as without closing original, By using 'Save As' you are effectively closing the original workbook. the way around this is to re-open the original workbook like this: Code:. Using below code, you can send your current workbook without any VBA code in it which already has VBA codes in it. This is one of the best usage of saving a workbook as Excel Macro FREE Workbook. Before sending your workbook as an attachment in email, you do not want to share the code along-with the workbook, then this piece of code will do the

I did something similar to what Siddharth suggested and wrote a function to do it as well as handle some of the annoyances and offer some more flexibility.

Sub saveExample()
    Application.ScreenUpdating = False

    mySaveCopyAs ThisWorkbook, "C:\Temp\testfile2", xlOpenXMLWorkbook

    Application.ScreenUpdating = True
End Sub

Private Function mySaveCopyAs(pWorkbookToBeSaved As Workbook, pNewFileName As String, pFileFormat As XlFileFormat) As Boolean

    'returns false on errors
    On Error GoTo errHandler



     If pFileFormat = xlOpenXMLWorkbookMacroEnabled Then
        'no macros can be saved on this
        mySaveCopyAs = False
        Exit Function
    End If

    'create new workbook
    Dim mSaveWorkbook As Workbook
    Set mSaveWorkbook = Workbooks.Add

    Dim initialSheets As Integer
    initialSheets = mSaveWorkbook.Sheets.Count


    'note: sheet names will be 'Sheet1 (2)' in copy otherwise if
    'they are not renamed
    Dim sheetNames() As String
    Dim activeSheetIndex As Integer
    activeSheetIndex = pWorkbookToBeSaved.ActiveSheet.Index

    Dim i As Integer
    'copy each sheet
    For i = 1 To pWorkbookToBeSaved.Sheets.Count
        pWorkbookToBeSaved.Sheets(i).Copy After:=mSaveWorkbook.Sheets(mSaveWorkbook.Sheets.Count)
        ReDim Preserve sheetNames(1 To i) As String
        sheetNames(i) = pWorkbookToBeSaved.Sheets(i).Name
    Next i

    'clear sheets from new workbook
    Application.DisplayAlerts = False
    For i = 1 To initialSheets
        mSaveWorkbook.Sheets(1).Delete
    Next i

    'rename stuff
    For i = 1 To UBound(sheetNames)
        mSaveWorkbook.Sheets(i).Name = sheetNames(i)
    Next i

    'reset view
    mSaveWorkbook.Sheets(activeSheetIndex).Activate

    'save and close
    mSaveWorkbook.SaveAs FileName:=pNewFileName, FileFormat:=pFileFormat, CreateBackup:=False
    mSaveWorkbook.Close
    mySaveCopyAs = True

    Application.DisplayAlerts = True
    Exit Function

errHandler:
    'whatever else you want to do with error handling
    mySaveCopyAs = False
    Exit Function


End Function

How to use VBA SaveAs without closing calling workbook?, I want to: Do data manipulation using a Template workbook Save a copy of this work book as .xlsx (SaveCopyAs doesn't let you change filetypes, otherwise this� 50 = xlExcel12 (Excel Binary Workbook in 2007-2016 with or without macro's, xlsb) 56 = xlExcel8 (97-2003 format in Excel 2007-2016, xls) Note : I always use the FileFormat numbers instead of the defined constants in my code so that it will compile OK when I copy the code into an Excel 97-2003 workbook (For example, Excel 97-2003 won't know what

There is nothing pretty or nice about this process in Excel VBA, but something like the below. This code doesn't handle errors very well, is ugly, but should work.

We copy the workbook, open and resave the copy, then delete the copy. The temporary copy is stored in your local temp directory, and deleted from there as well.

Option Explicit

Private Declare Function GetTempPath Lib "kernel32" _
         Alias "GetTempPathA" (ByVal nBufferLength As Long, _
         ByVal lpBuffer As String) As Long

Public Sub SaveCopyAs(TargetBook As Workbook, Filename, FileFormat, CreateBackup)
  Dim sTempPath As String * 512
  Dim lPathLength As Long
  Dim sFileName As String
  Dim TempBook As Workbook
  Dim bOldDisplayAlerts As Boolean
  bOldDisplayAlerts = Application.DisplayAlerts
  Application.DisplayAlerts = False

  lPathLength = GetTempPath(512, sTempPath)
  sFileName = Left$(sTempPath, lPathLength) & "tempDelete_" & TargetBook.Name

  TargetBook.SaveCopyAs sFileName

  Set TempBook = Application.Workbooks.Open(sFileName)
  TempBook.SaveAs Filename, FileFormat, CreateBackup:=CreateBackup
  TempBook.Close False

  Kill sFileName
  Application.DisplayAlerts = bOldDisplayAlerts
End Sub

Excel VBA Close Workbook: 8 Ready-To-Use Macro Code Examples, Learn how to quickly close Excel workbooks using VBA. At the end of that first section, I discuss the topic of closing Excel workbooks without prompts and introduce some of the most common SaveAs method prior to calling the Workbook. I'm attempting to save a worksheet as a new workbook but in doing so the custom properties of the initial workbook are not being inherited. ' Set the workbook properties ActiveWorkbook.CustomDocumentProperties.item("customProp1") = variableOne ActiveWorkbook.CustomDocumentProperties.item("customProp2") = variableTwo ' Save the worksheet as a new workbook Worksheets("Sheet1").SaveAs FileName

I have a similar process, here's the solution I use. It allows the user to open a template, perform manipulation, save the template somewhere, and then have the original template open

  1. user opens macro-enabled template file
  2. do manipulation
  3. save ActiveWorkbook's file path (template file)
  4. execute a SaveAs
  5. set ActiveWorkbook (now the saveas'd file) as a variable
  6. open template file path in step 3
  7. close the variable in step 5

the code looks something like this:

    'stores file path of activeworkbook BEFORE the SaveAs is executed
    getExprterFilePath = Application.ActiveWorkbook.FullName

    'executes a SaveAs
    ActiveWorkbook.SaveAs Filename:=filepathHere, _
    FileFormat:=51, _
    Password:="", _
    WriteResPassword:="", _
    ReadOnlyRecommended:=False, _
    CreateBackup:=False

    'reenables alerts
    Application.DisplayAlerts = True


    'announces completion to user
    MsgBox "Export Complete", vbOKOnly, "List Exporter"             


    'sets open file (newly created file) as variable
    Set wbBLE = ActiveWorkbook

    'opens original template file
    Workbooks.Open (getExprterFilePath)

    'turns screen updating, calculation, and events back on
    With Excel.Application
        .ScreenUpdating = True
        .Calculation = Excel.xlAutomatic
        .EnableEvents = True
    End With

    'closes saved export file
    wbBLE.Close

Save Copy Of Workbook Without Closing Original, Note that if you "save as" manually it renames the workbook… choose where to save it to) without closing or modifying the original. FileDialogs were exposed to VBA in xl2002, so it won't work if using a version below that. The Macros are not deleted until the workbook is closed after a SaveAs.xlsx so you can do two SaveAs in quick succession without closing the workbook. ActiveWorkbook.SaveAs FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False Application.DisplayAlerts = False ActiveWorkbook.SaveAs FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Another option (only tested on latest versions of excel).

The Macros are not deleted until the workbook is closed after a SaveAs .xlsx so you can do two SaveAs in quick succession without closing the workbook.

ActiveWorkbook.SaveAs FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False, ConflictResolution:=xlLocalSessionChanges
Application.DisplayAlerts = True

Note: you need to turn off the DisplayAlerts to avoid getting the warning that the workbook already exists on the second save.

"How to suppress Save Changes" prompt when you close a , a Microsoft Visual Basic for Applications (VBA) macro that suppresses the Save Changes You can use the reserved subroutine name Auto_Close to specify a macro that The following macro closes the workbook without saving changes:. Is there any way to use ActiveWorkbook.SaveAs without opening the destination file?. My main Workbook called TEC ALOC EXT has a code to when it's saved it's generate another file without macros in a different path (using FileFormat:=51) called ALOCACAO TECNICOS.

Using Workbook Object in Excel VBA (Open, Close, Save, Set), In this tutorial, I will cover the how to work with workbooks in Excel using VBA. Saving a Workbook; Saving All Open Workbooks; Saving and Closing All Workbooks If it hasn't been saved, then you can use the name without the file extension. to select the location to save the file, you can use call the Saveas dialog box. In order to ensure that the workbook is saved always under the desired filename, you can generally use the Workbook.SaveAs method prior to calling the Workbook.Close method. The following macro example (Close_Workbook_Save_Changes_Filename) closes the active workbook and saves changes under the specified filename .

The Complete Guide To The VBA Workbook, Learn how to Open, Close, Save, SaveAs, Copy, and Create a workbook and much more If you want to use VBA to Open a Workbook then check out Open Workbook Close ' Closes workbook Test2.xlsx and saves changes Workbooks( "Test2.xlsx"). Count ' Close the workbook without saving Workbooks("Book1. xlsm"). I'm using Excel 2010. I have an Excel macro-enabled template that has a data connection to a text file that is set to automatically refresh when a new document is created using this template.

VBA Save As, Pdf, CSV, etc.) Password – Password to protect the workbook (The workbook can' t be accessible without a password); WriteResPassword – Write reservation�

Comments
  • a lame workaround I can think of is to SaveCopyAs, open the copy, save it as your desired format, delete the copy. If you shove it into a sub then it won't clutter up your main procedures.
  • Use SaveCopyAs to create a copy then, open that copy and do a save as?
  • @Cor_Blimey: Sorry didn't see your comment
  • Or create a new workbook, copy all your sheets in that and then save it as an xlsx?
  • Both those options make me die a little on the inside (ok a lot). @Cor_Blimey I definitely thought of doing that as well initially but it just seems there should be a better way. I'm working with network drives so minimizing saving on them multiple times is ideal.
  • +1, this is pretty similar to what I ended up with here - I made it a bit more robust and into a function.
  • Also For Each... ws.Delete is such a hack ;)
  • It depends on what version of Excel you use. With Excel 2003 copying sheets is not a safe operation and can result in data loss.
  • Oh yeah, also: Application.SheetsInNewWorkbook = 1 saves you from having to delete an arbitrary number of sheets and have that nasty On Error Resume Next in there. You should save the value from Application.SheetsInNewWorkbook and restore it at the end of your Sub.
  • @SiddharthRout IIRC there are a few errors that can occur. For example, a quick Google search turns up the 255 character limit.
  • out of curiosity, is there a reason why you copy the sheets and not move them from the temp file? Moving closes the workbook automatically. Anyway, I can see this being quicker than saving a copy, opening etc, but it probably wouldn't be great if you intend to use this method with worksheets with tables, formulae, defined names etc (though as you are using this with a template you control, I suppose you know this isn't an issue).
  • @Cor_Blimey I want to keep the template intact and use this functionality basically like "SaveCopyAs" - if I move the sheets I lose them from the template workbook.
  • whoops - I lost sight of that objective. Thanks.