What is Excel VBA Save As?
VBA Save As is a function that allows you to save an Excel workbook with a different filename, file format, or at a different location. Whether you need to create a backup copy of a workbook, save a workbook in a different format, or save a copy to a different location, the Save As function in Excel VBA can help.
Let’s look at an example to understand Excel VBA Save As function. Suppose we have the following Excel dataset. We want to save this Excel data into PDF at any required location.
It can be accomplished using the following VBA Macro:
Once we execute the above macro, the required Excel data will be saved as PDF at the given file location. The resulting output looks like this in the PDF file.
Table of contents
- Excel VBA Save As is a function that allows users to save an Excel workbook with a different filename, file format, or location.
- The Save As function can be customized to meet specific needs. You can use variables to create dynamic filenames.
- Ensure that the specified folder and file name exist when using the Excel VBA Save As function.
- If you don’t specify the file format when saving, Excel will save it in its default format, which you may not want.
What Does Excel VBA Save As Function Do?
Excel VBA Save As is a feature in Excel that allows you to save a workbook at a different location or with a different file name.
When working on an Excel file, save a copy with a new name so you don’t overwrite the original file. The Save As feature lets you specify a new file name when saving. The original workbook remains unchanged when you use the Save As function in Excel VBA.
Save As also allows you to choose a different file format for the saved workbook. Excel offers various file formats like XLSX, XLSM, CSV, or PDF. Each format has its characteristics and purposes. For example, XLSX is the default format for Excel workbooks, while XLSM supports macros.
How to Use Excel VBA Save As Function?
To use the Save As function in Excel VBA, you need to follow these steps:
- Open the Visual Basic Editor (VBE).
To open the VBE, press the Alt + F11 keys in Excel or click on the Developer tab in the ribbon and then click the Visual Basic button.
- Create a new module.
In the VBE, click the Insert menu and select Module to create a new module.
- Add the Save As code.
In the new module, add the Save As code to a subroutine. The basic syntax of the Save As function is:
In this above code, “ActiveWorkbook.SaveAs” is a command that tells Excel to save the workbook with a different file name or in a different location and saves the current workbook with the filename “FileName.xlsx” in the specified folder.
- Customize the Save As code.
You can customize the Excel VBA Save As code to meet specific needs. For example, you can use variables to create dynamic filenames or save the workbook in a different file format or location.
Let us explore some examples here:
• Save a copy of the current workbook to a different location using an Excel VBA Save As Location function:
• Save the current workbook as a CSV file using an Excel VBA Save As CSV function:
• Save the current workbook as a PDF file in an Excel VBA Save As PDF function:
Here are two examples of using the Save As function in Excel VBA to Save a copy of the current workbook to a new location.
Suppose you have a workbook called “SalesData.xlsx” to save to a different folder using the “VBA Save As XLSX” function. Here’s how you can use the Save As function to save a copy of the workbook to a new location:
- Step 1: Open the Visual Basic Editor by following the abovementioned steps.
- Step 2: Start the subroutine named “SaveCopy” in the new module using the line.
- Step 3: Declare two variables, strFolder, and strFileName, using the Dim keyword.
- Step 4: Display a message to the user asking for the folder path where they want to save the copy of the workbook:
- Step 5: Display a message to the user asking for the file name for the copy of the workbook:
- Step 6: Save the copy of the workbook to the specified location using the XLSX file format:
- “Filename:=strFolder & “\” & strFileName” specifies the complete file path where the workbook should be saved. It combines the folder path stored in the “strFolder” variable, a backslash “\” as the path separator, and the file name stored in the “strFileName” variable.
- “FileFormat:=xlOpenXMLWorkbook” specifies the desired file format for the saved workbook. In this case, “xlOpenXMLWorkbook” represents the XLSX file format, which is the default format for Excel workbooks in newer versions.
- Step 7: Save the module and close the VBE. Now, press Alt + F8 to open the Macro list.
- Step 8: Now click on “SaveCopy” and then Run. You will see an Excel message box asking you to enter the folder path where you want to save the workbook copy.
Here, you can provide the path of the folder where you want to save the workbook and click OK:
Folder path example: C:\Path\To\File\
- Step 9: After you click ok in the previous step, you will see another pop-up message box asking you to provide the file name. Save the file with the name “SalesData” and click OK.
It will now save a new “XLSX” file in the path you have provided with the file name “SalesData.XLSX.”
If you get the following message box while saving, please click “Yes” to save the file as a macro-free workbook.
The above message indicates that when saving a workbook without macros in VBA (in a macro-free file format), certain features or components of the VB project, such as macros, user forms, customized excel ribbons, or ActiveX controls, cannot be preserved.
To retain these elements, save the workbook in a file format that supports macros, like XLSM.
Here is the full code:
Dim strFolder As String
Dim strFileName As String
‘Get the new folder path
strFolder = InputBox(“Enter the folder path where you want to save the copy of the workbook”)
‘Get the new file name
strFileName = InputBox(“Enter the file name for the copy of the workbook”)
‘Save the copy of the workbook to the new location
ActiveWorkbook.SaveAs Filename:=strFolder & “\” & strFileName, FileFormat:=xlOpenXMLWorkbook
Let us now look at another Excel VBA macro to save an workbook as a CSV file in Excel:
In the above code, we declare a variable name as “strFilePath” to store the chosen file path and name for the CSV file.
- The line “strFilePath = Application.GetSaveAsFilename(FileFilter:=”CSV Files (*.csv), *.csv”)” displays a Save As dialog box that allows the user to choose a file path and name for the CSV file.
- The file filter is set to display only CSV files in the dialog.
- The line “ActiveWorkbook.SaveAs Filename:=strFilePath, FileFormat:=xlCSV” saves the workbook as a CSV file.
- The SaveAs method is used with the specified file path stored in “strFilePath” and the “xlCSV” file format. It saves the workbook as a CSV file with the chosen file path and name.
The workbook can be saved as a CSV file with the chosen file path and name. After saving, a message box will show you that the CSV file has been saved successfully.
If you cancel, the save operation will be aborted.
Here is the full code:
Dim strFilePath As String
‘ Prompt the user to choose a file path and name for the CSV file
strFilePath = Application.GetSaveAsFilename(FileFilter:=”CSV Files (*.csv), *.csv”)
‘ Check if the user canceled the save operation
If strFilePath <> “False” Then
‘ Save the workbook as CSV
ActiveWorkbook.SaveAs Filename:=strFilePath, FileFormat:=xlCSV
MsgBox “CSV file saved successfully!”
MsgBox “Save operation canceled.”
Important Things to Note
Here are some important things to note about using the Save As function in Excel VBA.
- When you use the Save As function in Excel VBA, it is important to ensure that the specified folder and file name exists. Otherwise, you may get an error.
- Before using the SaveAs method, it’s important to check whether the file already exists in the specified location. If the file exists and you try to save the workbook with the same name, you will get a prompt asking whether you want to overwrite the existing file.
- When using the SaveAs method, specify the correct file format for the saved file.
Frequently Asked Questions (FAQs)
The Excel VBA Save As function saves a copy of the current workbook with a different filename, file format, or location. The original workbook remains unchanged. The Save Copy As function saves a copy of the current workbook with a different filename, file format, or location, and closes the original workbook.
To create a Save As button in Excel VBA, add a button to the worksheet and assign it a macro. The macro should contain the Save As code that saves the workbook with a different filename, file format, or location.
There are several reasons why Save As may not work in Excel VBA. For example, the specified folder or file name may not exist, the file may be open in another program, or the workbook may be protected or read only. To resolve the issue, ensure that the specified folder and file name exist, close the file in other programs, and remove any protection or read-only restrictions.
This article must be helpful to understand the Excel VBA Save As, with its usage and examples. You can download the template here to use it instantly.
This has been a guide to Excel VBA Save As. Here we explain how Save Function in VBA works, how to use it, with examples & downloadable excel template. You can learn more from the following articles –