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.
Download Practice Workbook
You can download the practice workbook from here.
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.
5 Suitable Ways to Save a Copy of Excel File as XLSX Using VBA
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.
- 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.
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.
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.
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.
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.
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. Lastly, to learn more excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.