VBA save as Workbook Excel Macro code helps Save file to a specific Folder, its is a common task in automation process. Once you are done with actual calculations or task, at end of the procedure we generally call a procedure to export or Save the Output File to a Specific Folder or common drive. Or in other case you may not have the permissions to Save the File in a location, so that you can use SaveAs Option to store the revised or updated file.
VBA save as Workbook – Solution(s):
You can use SaveAs method to Save the File to a specific location. You can Save with the same File Name and Location. Or you can use different File Name and Location to Save the File. You can also set to an object and Save the File.
In other method, you use Save Dialog Box. So that user can choose a specific folder to save the Excel File.
Save Workbook to Specific Folder – Example Cases:
- Save Workbook to Specific Folder
- Set to an Object and Save it
- Save Workbook to Specific Folder using Save Dialog Box
- Save Workbook in the same location of the Macro (this) Workbook
- Save the Workbook
- Download: Example Macro Workbook
Save a Workbook to a Specific Folder
The following example show you how to save an Excel Workbook in Specific folder using SaveAs method:
Sub ExampleToSaveWorkbook() Workbooks.Add 'Saving the Workbook ActiveWorkbook.SaveAs "C:\WorkbookName.xls" 'OR 'ActiveWorkbook.SaveAs Filename:="C:\WorkbookName1.xls" End Sub
Set to an Object and Save it
Set to an Object and Save it, so that it is easy to refer to your workbook to do further tasks. If you are dealing with more than one workbook, you will need this method to access a specific Excel Workbook.
Sub ExampleToSaveWorkbookSet() Dim wkb As Workbook 'Adding New Workbook Set wkb = Workbooks.Add 'Saving the Workbook wkb.SaveAs "C:\WorkbookName.xls" 'OR 'wkb.SaveAs Filename:="C:\WorkbookName1.xls" End Sub
Save Workbook to Specific Folder using Save Dialog Box
You can Save the Workbook to Specific Folder by showing the Save Dialog Box to user. So that user can choose desired location to save the file.
Sub sbSaveExcelDialog() Dim IntialName As String Dim sFileSaveName As Variant IntialName = "Sample Output" sFileSaveName = Application.GetSaveAsFilename(InitialFileName:=InitialName, fileFilter:="Excel Files (*.xlsm), *.xlsm") If sFileSaveName <> False Then ActiveWorkbook.SaveAs sFileSaveName End If End Sub
Save Workbook in the same location of the Macro (this) Workbook
You can save the workbook in the same directory of the macro workbook using ThisWorkbook.Path property.
Sub ExampleToSaveWithSamePathDifferentName() Dim sFilename As String sFilename = "WorkbookName.xls" 'You can give a nem to save Workbooks.Add 'Saving the Workbook ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & sFilename End Sub
Save the Workbook
You can simply save the file without changing its file name or path name using Save method.
Sub ExampleToSaveWithSameNameandPath() 'Saving the Workbook ActiveWorkbook.Save End Sub
You can download the example file and explore it.
ANALYSISTABS – Save Workbook
Overwrite an Existing Workbook using VBA
While Saving the existing workbook or a new excel file with existing name, Excel will prompt a warning message. It will interrupt the procedure and ask user to press yes or no for overwriting a file.
Overwrite an Existing Workbook using VBA – Solution:
You can avoid this by disabling the alerts temporarily and save the workbook with the same name by setting the Application.DisplayAlerts=False property. Once you are done with the task, you should enable the application alerts by setting the property TRUE.
Overwrite an Existing Workbook using VBA – An Example
The following example will show you, how to overwrite a file by disabling the application alerts.
sub procedure to over write an excel file Sub ExampleToOverWriteExistingWorkbook() 'Declaration: Declaring the variable Dim wkb As Workbook 'Adding New Workbook using Workbook.Add method and setting to wkb Object Set wkb = Workbooks.Add 'Saving the Workbook 'Desable the application alerts before svaing the file Application.DisplayAlerts = False 'Now save the file wkb.SaveAs "C:\WorkbookName.xls" ' change to existng file name 'OR 'wkb.SaveAs Filename:="C:\WorkbookName1.xls"</span> 'Eanbling the Application Aletrts after saving the file Application.DisplayAlerts = True End Sub
- Open an excel workbook
- Press Alt+F11 to open VBA Editor
- Insert a Module for Insert Menu
- Copy the above code and Paste in the code window
- Save the file as macro enabled workbook
- Press F5 to execute itit