How to use VBA SaveAs without closing calling workbook?
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)
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
- user opens macro-enabled template file
- do manipulation
- save ActiveWorkbook's file path (template file)
- execute a SaveAs
- set ActiveWorkbook (now the saveas'd file) as a variable
- open template file path in step 3
- 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
.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�
- 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.
SaveCopyAsto 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.
For Each... ws.Deleteis 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 = 1saves you from having to delete an arbitrary number of sheets and have that nasty
On Error Resume Nextin there. You should save the value from
Application.SheetsInNewWorkbookand 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.