How to Save a Copy as XLSX Using Excel VBA (5 Suitable Ways)

In this tutorial, I am going to share with you 5 suitable ways to save a copy of an Excel file in XLSX format using VBA. You can use these methods in any type of workbook whether they contain large or small data. Also, as we will be using VBA, it will take virtually no time to accomplish the task.

What Is an XLSX File?

The XLSX file is an MS Excel Open XML Format Spreadsheet that stores data in cells that are contained inside worksheets. The cells are arranged in a row-column structure. In MS Excel 2007 and earlier, this spreadsheet file was of the type XLS.

How to Save a Copy of Excel File as XLSX Using VBA: 5 Suitable Ways

For this tutorial, our main purpose is to show you how to save a current workbook in an XLSX format. So we have taken a simple and concise dataset that has 3 columns and 6 records of students marks. But you have the option to use your own datasets.

1. Using SaveCopyAs Method

The SaveCopyAs method in Excel VBA takes a workbook object and can save a new copy of this workbook in an XLSX format without modifying the data. Let us see how to use this method inside our code.

Steps:

• First, go to the Developer tab and select Visual Basic.

• Next, in the Visual Basic window, click on Insert and select Module.

• Now, in the new module window on the right, type in the following formula:

Sub SaveCopyAs_Method()
Application.DisplayAlerts = False
ActiveWorkbook.SaveCopyAs "C:\Users\USER\Desktop\5700\SaveCopyAs method.xlsx"
End Sub

• Then, close the VBA window and navigate to the Developer tab again.
• Here, select Macros.

• Now, in the Macro window, you should see the macro code we inserted.
• Next, click on Run.

• Finally, open the folder where you saved the file and it should be available in the XLSX format as we wanted.

Read More: Create New Workbook and Save Using VBA in Excel

2. Specifying Filename

We can save a copy of an Excel file in XLSX format by specifying the file name in the VBA code. When setting the file name, we will also add the file extension which will convert the file to our desired format. To proceed with this method, insert the below code in the VBA module window.

Sub Specify_file_name()
Dim location As String
location = "C:\Users\USER\Desktop\5700\Specify file name.xlsx"
ActiveWorkbook.SaveAs Filename:=location
End Sub

After typing the code, simply run it from the Macros option as we showed previously. Now, go to the save folder and the file with the XLSX format should be there now.

Read More: Excel VBA to Save Workbook in Specific Folder with Date

3. Entering File Format Number

The file format numbers are unique numbers that denote a specific file type while saving. For this tutorial, our goal is to save an Excel copy as an XLSX file using VBA. So, we will use the format number 51, which denotes the XLSX file type. So, type the following code in the VBA module:

Sub file_format_number()
Dim location As String
Application.DisplayAlerts = False
location = "C:\Users\USER\Desktop\5700\File format number"
ActiveWorkbook.SaveAs Filename:=location, FileFormat:=51
End Sub

Now, if you run this code, excel will immediately save the workbook in an XLSX format. You can confirm that by checking in the destination folder.

Read More: Excel VBA Save as File Format

4. Saving with Password

In many cases, it is very important to save a copy of an Excel workbook in XLSX format with an additional password. This is especially true for workbooks that have high-security concerns. You can achieve this task very easily using VBA and setting a custom password alongside saving your document. For that, enter the below VBA code in the Module window:

Sub Save_With_Password()
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs _
Filename:="C:\Users\USER\Desktop\5700\Save with password.xlsx", Password:="one"
End Sub

Finally, as we saw before, you just have to run this code from the Macros options. Now, if you go to your saved folder, you should find the file with the name that you gave and the XLSX extension at the end.

5. Save by Recommending Read Only

A less strict way of protecting a document is to make it a read-only file. If you save an Excel file copy in the XLSX format, then you can set the read-only condition using VBA. Now, to do this, insert the following code in the VBA module:

Sub recommend_read_only()
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs _
Filename:="C:\Users\USER\Desktop\5700\Recommend read only.xlsx", _
ReadOnlyRecommended:=True
End Sub

Then, run this code from the Macros option under the Developer tab. This should save an XLSX copy of the current workbook as shown below.

Read More: Excel VBA to Save File with Variable Name

Things to Remember

• Make sure to change the saving path of the XLSX file in the VBA. It should match a folder path inside your computer.
• Double-check to see that you are spelling all the built-in VBA functions exactly as I have done.
• Remember that, in some cases, the VBA code may not be available anymore in the VBA window after running the code.

Download Practice Workbook

You can download the practice workbook from here.

Conclusion

I hope that the methods that I showed to save an Excel file copy in XLSX format using VBA were helpful to you. If you get stuck in any of the steps or a code doesn’t work, then I would suggest checking the codes I have provided a few times. Also, try changing the code to some extent to understand what the code is doing. If you have any queries, please let me know in the comments.

Related Articles

• Excel VBA: Save Workbook Without a Prompt
• How to Save Excel Macro Files as Filename from Cell Value
• How to Use Macro to Save Excel File with New Name
• Excel VBA: Save Workbook as New File in Same Folder
• Excel VBA to Save as File Using Path from Cell
• Excel VBA: Save Workbook in Specific Folder