VBA Save as

In order to save a workbook in VBA, use Alt + F11 and enter the following code.

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.

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.

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.

Posted in vba