Save Excel Files As Xlsx With Vba

Saving Excel files as XLSX using VBA is a common requirement for automating tasks, especially when working with Excel macros. XLSX is the default file format for Excel 2007 and later versions, which offers several benefits over the older XLS format, including better compression and support for newer Excel features. Here’s how you can use VBA to save Excel files as XLSX.

Why Save as XLSX?

Before diving into the VBA code, it’s worth understanding the benefits of saving Excel files as XLSX:

1. Compatibility: XLSX files are compatible with Excel 2007 and later versions, making them a good choice for sharing files across different versions of Excel.
2. Compression: XLSX files use ZIP compression, which reduces the file size compared to XLS files.
3. Security: XLSX files support advanced security features, such as encryption and password protection.

VBA Code to Save as XLSX

You can use the following VBA code to save an Excel file as XLSX:

Sub SaveAsXLSX()
    Dim filePath As String
    Dim fileName As String
    
    ' Define the file path and name
    filePath = "C:\Example\" ' Change to your desired path
    fileName = "ExampleFile" ' Change to your desired file name
    
    ' Save the active workbook as XLSX
    ActiveWorkbook.SaveAs fileName:=filePath & fileName & ".xlsx", _
                          FileFormat:=xlOpenXMLWorkbook
End Sub

In this code:

• ActiveWorkbook refers to the currently active workbook.
• SaveAs is the method used to save the workbook with a new name or location.
• fileName:= specifies the path and file name for the saved file. You should change "C:\Example\" to your desired file path and "ExampleFile" to your desired file name.
• FileFormat:=xlOpenXMLWorkbook specifies that the file should be saved in the XLSX format (xlOpenXMLWorkbook is the file format constant for XLSX).

How to Use the Code

1. Open Excel and press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
2. In the VBA editor, click Insert > Module to insert a new module.
3. Paste the VBA code into the module window.
4. Update the filePath and fileName variables with your desired file path and name.
5. Click Run > Run Sub/UserForm (or press F5) to execute the code.
6. The active workbook will be saved as an XLSX file in the specified location.

Tips and Variations

• Save a Specific Workbook: If you want to save a specific workbook instead of the active one, replace ActiveWorkbook with Workbooks("YourWorkbookName.xlsx").
• Prompt for File Path and Name: You can use the Application.GetSaveAsFilename method to prompt the user for the file path and name.
• Overwrite Existing Files: Be cautious when using SaveAs because it will overwrite files with the same name without warning. Use Application.DisplayAlerts = False before SaveAs to suppress the overwrite prompt, but be sure to set it back to True afterwards.

Saving Excel files as XLSX using VBA is a straightforward process that can be automated to enhance your workflow efficiency. By adapting the provided code to your specific needs, you can easily integrate this functionality into your Excel macros.

Gallery of Save Excel Files As Xlsx With Vba