VBA Save File – 20 Easy Examples
The VBA Save command saves an Excel file similarly to clicking the Save icon or using the Save Shortcut (CTRL + S).
Save a Specified Workbook
To save a workbook, reference the workbook object and use the Save command.
Save the Active Workbook
Note: This is the current active workbook from with in the VBA code, which is different from ThisWorkbook which contains the running code.
VBA Coding Made Easy
Stop searching for VBA code online. Learn more about AutoMacro – A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!
Save the Workbook Where the Code is Stored
Save all Open Workbooks
This will loop through all open workbooks, saving each one.
Save all open workbooks that were not opened ReadOnly
Note: opening a workbook in ReadOnly mode prevents the file from being saved.
To save the file you will need to use Save As and save the file with a different name.
Save a workbook defined by a variable
This will save a workbook that was assigned to a workbook object variable.
Save a workbook defined by a string variable
This will save a workbook that’s name was saved to a string variable.
Save a workbook defined by the order it was opened.
Note: The first workbook opened would have 1, the second 2, etc.
Save a workbook based on a cell value
This will save a workbook that’s name is found in a cell value.
Save As – VBA
The VBA Save As command saves an Excel file as a new file, similar to clicking the Save As icon or using the Save As Shortcut (Alt > F > A).
Above, we identified all the ways to specify which workbook to save. You can use those exact same methods to identify workbooks when using Save As.
Save As behaves similarly to Save, except you also need to specify the name of the new file.
In fact, Save As has many potential variables to define:
SaveAs Syntax:
A full description of all of the SaveAs arguments is included below. For now we will focus on the most common examples.
Note: These arguments can be entered as string with parenthesis or as defined variables.
VBA Save Workbook (Excel File)
To save an Excel workbook using VBA, you need to use the SAVE method to write a macro. And in that macro, you need to specify the workbook that you want to save and then use the SAVE method. When you run this code, it works like the keyboard shortcut (Control + S).
- Specify the workbook hat you want to save.
- Type a dot to get the list of all the properties and methods.
- Select the “Save” method out of those or type “Save”
- In the end, run the code to save the workbook.
In this tutorial, we will look at different ways that we can use to save a workbook. So make sure to open the VBA editor from the developer tab to use the code you have in this tutorial.
Save the ActiveWorkbook
If you want to save the active workbook in that case you can use a code like the following code, instead of specifying the workbook by its name.
When you use the ActiveWorkbook as the workbook, VBA always refers to the workbook which is active despite in which file you are writing the code.
Save the Workbook where you are Writing Code
If you want to save the file where you are writing the code you need to use “ThisWorkbook” instead of the workbook name.
Save All the Open Workbooks
Here we can use a loop to loop through all the workbooks that are open and save them one by one. Look at the below code.
The above code uses the FOR EACH loop in each workbook it uses the SAVE method to each file one by one.
Note: If you are trying to save a workbook with the SAVE method that is not saved already, Excel will show a dialog box to ask for your permission to save that file, and then you need to choose if you want to save that file on the default location in the default format.
Now here’s the point: As you are using a macro to save the workbook, that file should be saved in the macro-enabled format and the best way to deal with this situation is to use the SAVE AS method (we’ll see in the next section of this tutorial).
Save As an Excel File
To SAVE a file that is not saved yet, using VBA, you need to use the SAVE AS method. In this method, you can define the file name and the path where you want to save the file, and apart from that, there are ten more arguments that you can define.
In the following code, you don’t have any argument with the “SAVE AS” method.
When you run this code, it asks you a few things, like, which format you want to use to save the file, do you want to replace the existing file that is already saved with the same name. So it’s better to define the use of some of the arguments.
Save As File on the Current Location
By default, VBA uses the current location to save the file. When you write code with the SAVE AS method and just specify the name that file straight goes to the current folder. You can see in the following code where you have the which saves the active workbook.
Save As File on a Specific Location
The filename argument also allows you to use the location path in case you want to use a different location to save the file.
In the above code, you have the path in the FileName argument and VBA uses that path to the file.
Note: You can also use this method to check if a workbook exists in a folder or not before you use the SAVE AS method to save it on a particular location and you can learn more about SAVE AS method from here.
VBA Сохранить как | Как использовать метод «Сохранить как» в VBA Excel?
Метод VBA Save As используется для сохранения файла Excel в определенном месте. Чтобы сохранить книгу с использованием кода vba, мы используем объект Workbook с функцией SaveAs.
После всей тяжелой работы, которую мы проделали в рабочей тетради, мы сохраняем ее, не так ли? Больно терять данные, над которыми мы работали. У нас есть два типа сохранений в Excel или в любом другом файле: один — «Сохранить», а другой — «Сохранить как». Ctrl + S — это популярное сочетание клавиш, как Ctrl + C и Ctrl + V во всем мире. Но это не совсем привычная концепция «Сохранить как» . В обычном рабочем листе горячая клавиша для сохранения файла — это клавиша F12 . В VBA мы также можем сохранить файл как « Сохранить как ».
Что делает функция «Сохранить как» в VBA?
Это общая задача экономии работы в процессе автоматизации. После всей работы мы хотим сохранить файл.
Этот код читает книгу с именем «Sales 2019.xlsx» для сохранения.
Точно так же можно создать копию книги, над которой мы работаем, с помощью метода «Сохранить как».
- Имя файла: каково имя файла, который вы хотите передать. Это должно быть объединено с путем к папке с файлом.
- Формат файла: каким должен быть формат сохраняемого файла.
- Пароль: Вы хотите указать пароль для сохраняемого файла.
- Write Res Password: укажите зарезервированный пароль для книги.
Думаю, этих параметров достаточно для понимания метода «Сохранить как».
Как использовать функцию «Сохранить как»?
Пример # 1
Мы сохраняем книгу правильно, поэтому важно указать имя книги и ее расширение, чтобы использовать метод «Сохранить как». Так что упомяните книгу, которую вы сохраняете.
Код:
Теперь воспользуйтесь методом «Сохранить как».
Код:
Теперь определите, где вы хотите сохранить.
Код:
Теперь поставьте обратную косую черту и введите имя файла по вашему желанию с расширением файла.
Код:
Теперь укажите формат файла как «xlWorkbok».
Код:
Хорошо, мы закончили, файл будет сохранен на диске D> Имя папки (статьи)> Имя подпапки (2019).
Пример # 2
Сохранить все открытые книги
Предположим, вы работаете с 10 книгами на своем компьютере. Вы хотите создать резервную копию этих книг, сохранив их на компьютере как одну из копий. Если вы хотите работать с более чем одной книгой, необходимо использовать циклы.
Код ниже поможет вам сохранить все книги как копии.
Код:
Если вы хотите выбрать свой собственный путь к папке, вы можете использовать этот код.
Код:
Как только вы запустите этот код с помощью клавиши F5 или вручную, он попросит вас выбрать путь к папке назначения, выбрать и нажать ОК, он сохранит файл.
VBA Save As
By 
Dheeraj Vaidya
Excel VBA Save As
If you are a frequent user of Microsoft Excel, you must have used Save As function under it, which allows you to save the currently opened workbook with a different name or different format (Excel Macro-enabled, CSV, PDF, etc.). You can also save the file in a different folder using this method. However, is it possible to use the same function under VBA? The answer is an absolute Yes! We are having Save As function under VBA as well which helps us to do all these above-mentioned tasks along with some additional benefits (obviously automating things is one of the benefits). In this article, we are going to have a look into different examples for VBA SAVE AS function.
Formula for Save As function in Excel VBA
Let us look below the formula for Save As function in VBA.
Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.
- FileName – Name of the workbook to be saved.
- FileFormat – File format in which the file needs to be saved (Ex. Pdf, CSV, etc.)
- Password – Password to protect the workbook (The workbook can’t be accessible without a password)
- WriteResPassword – Write reservation password for the workbook.
- ReadOnlyRecommended – Recognizes whether the workbook is saved in Read-Only format or not.
- CreateBackup – Determines whether a backup file for the workbook is created or not.
- AccessMode – Recognizes the access mode for the workbook.
- ConflictResolution – Recognizes the conflicts that pop-up when the workbook is shared and is used by more than one user.
- AddToMru – Checks if the workbook is added under recently used file or not.
- Local – Checks if the workbook is saved with the laws of Excel (local language) or with VBA laws (US – English).
Hush! Lots of arguments right? But what if I tell you, all these arguments are optional and can be skipped while using VBA SAVE AS function. However, it is true that these are the arguments that make VBA SaveAs more flexible function to use. “Expression” at the start of the syntax is nothing but an expression against which this function can be used. Like Workbook is the expression against which SaveAs can be used.
Examples to Save Excel File using VBA Save As Function
Below are the different examples to save excel file using VBA Save As function.
Example #1 – How to Save a Copy of the Workbook with a Different Name?
Let’s see how we can save the current workbook with a different name.
Follow the below steps to use Save As Function in Excel VBA:
Step 1: Add a new module under Visual Basic Editor (VBE). Go to Insert and then select Module.
Step 2: Define a new sub-procedure which can store a macro.
Code:
Step 3: Define a new variable which can hold the name by which the file to be saved as.
Code:
Step 4: Now use the assignment operator to assign a name to this variable using which current file can be saved as.
Code:
Step 5: Now, use SaveAs function with FileName argument in order to save the file as with name “Example1”.
Code:
Step 6: This is it, now run this code by hitting F5 or manually using the Run button and see the output.
You can see that a file with the name “Example1” is being saved on Documents.
If you could have noted down, the file is being saved as Macro-Enabled File, because the original file which I have used SaveAs function on is a file with Macro-Enabled. It means that this function in VBA automatically checks the file format of the previous file and saves it in the same format. Also, by default, the file will be saved in Documents under This PC. This default location can be provided explicitly at the time of defining sheet name.
Example #2 – Saving Workbook with User Provided Name
Instead of defining name initially, is it possible to write a code which allows a user to save the worksheet by the name of his choice same as Excel Save As function?
Follow the below steps to use Save As Function in Excel VBA.
Step 1: Define a new sub-procedure under newly inserted module which can store the macro.
Code:
Step 2: Define a new variable which can hold the value of the user-defined name.
Code:
The reason for this variable being defined as Variant is, this data type makes Naming conventions versatile. For Example, a user may add some extra special character (which are allowed in naming conventions) or can add dates as well under the file name.
Step 3: Now, with the help of an assignment operator and function combination called application.GetSaveAsFilename, make a statement that allows the system to take a user-defined name. See how it has been achieved in the screenshot below.
Code:
Step 4: Use conditional IF to make sure the name user enters is valid as per the naming conventions.
Code:
This condition checks if the name given by the user to save the worksheet is properly satisfying the naming conventions set for naming a file or not.
Step 5: Write down a statement which gets evaluated for the given IF condition.
Code:
This piece of code gets evaluated once the IF condition is true. If so, the active workbook will get saved under the name define in variable Spreadsheet_Name (Which will be user-defined)
Step 6: End the IF-loop and run this code to see the output.
Code:
Step 7: As soon as you run this code, you’ll get Save As dialogue box which will allow you to type in the name of your choice and save the file.
Example #3 – How to Save as a File into PDF using Excel VBA SaveAs function?
Suppose you have a data as given below in your excel sheet and you need to convert it into PDF.
Follow the below steps to convert this file into a PDF Using VBA Save As function:
Step 1: Define a new sub-procedure to store a macro.
Code:
Step 2: Now, use the following code to save this file as a PDF file.
Code:
Step 3: Run this code and you’ll see a pdf file generated under This PC > Documents.
In this code, ActiveSheet.SaveAs allows the file to be saved with the same name. As we have added the extension as .pdf at the end of the file, it gets exported into PDF file. You can see the image above for your reference.
Things to Remember
- The by default save location for the file used under VBA SaveAs will be This PC > Documents. However, you can specify the directory manually at the time of defining file name.
- By default, the file saved using VBA SaveAs will be saved under the format same as that of the original file. However, it can also be defined as per user requirement at the time you define the variable.
Recommended Articles
This is a guide to VBA Save As. Here we discuss how to save the file using Excel VBA Save As function along with an example and downloadable excel template. Below are some useful excel articles related to VBA –