In order to save a workbook in VBA, use Alt + F11 and enter the following code.
|
1 2 3 |
Sub SaveWorkbook() ActiveWorkbook.Save End Sub |
This procedure will save the file in the default location. If the file wasn’t saved yet, the new file is created, otherwise, it overwrites the old file.
Save the file in the specified location
You can specify the name of the file you want to write.
|
1 2 3 4 5 6 |
Sub SaveWorkbook() Dim myFile As String myFile = "ExcelFile" ActiveWorkbook.SaveAs Filename:=myFile, FileFormat:=xlOpenXMLWorkbook ‘or 51 End Sub |
In the following code, I used xlOpenXMLWorkbook to save a standard Open XML Workbook (xlsx).
The other popular formats are:
xlsm (Open XML Workbook Macro Enabled) – xlOpenXMLWorkbookMacroEnabled or 52
xlsb (Excel Binary Workbook) – xlExcel12 or 50
csv – (CSV) – xlCSV or 6
If you want to save your file using a different format, you can use one of the following parameters (https://docs.microsoft.com/en-us/office/vba/api/excel.xlfileformat).
Save with a dialog box
So far we were saving a file using a variable name. This code will ask you for a name for your file.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Sub SaveWorkbook() Dim myFile As Variant Dim fileTypes As String fileTypes = "Open XML Workbook (*.xlsx), *.xlsx, CSV (*.csv), *.csv" myDialogBox = Application.GetSaveAsFilename(fileFilter:=fileTypes) If myDialogBox <> False Then ActiveWorkbook.SaveAs Filename:=myDialogBox End If End Sub |
In the code, you can see a string called fileTypes. This is where you add file types. In this example, you can choose between xlsx and csv.
You can add multiple other formats.