EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home VBA VBA Resources VBA Tips VBA Save As

VBA Save As

By Madhuri ThakurMadhuri Thakur

VBA Save As

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax of VBA Save As

Where,

  • 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.

You can download this VBA Save As Excel Template here – VBA Save As Excel Template

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.

VBA Save As Example 1-1

Step 2: Define a new sub-procedure which can store a macro.

Code:

Sub SaveAs_Ex1()

End Sub

VBA Save As Example 1-2

Step 3: Define a new variable which can hold the name by which the file to be saved as.

Code:

Sub SaveAs_Ex1()

Dim newName As String

End Sub

VBA Save As Example 1-3

Step 4: Now use the assignment operator to assign a name to this variable using which current file can be saved as.

Code:

Sub SaveAs_Ex1()

Dim newName As String
newName = "Example1"

End Sub

VBA Save As Example 1-4

Step 5: Now, use SaveAs function with FileName argument in order to save the file as with name “Example1”.

Code:

Sub SaveAs_Ex1()

Dim newName As String
newName = "Example1"
ActiveWorkbook.SaveAs Filename:=newName

End Sub

VBA Save As Example 1-5

Step 6: This is it, now run this code by hitting F5 or manually using the Run button and see the output.

VBA Save As Example 1-6

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:

Sub SaveAs_Ex2()

End Sub

VBA Save As Example 2-1

Step 2: Define a new variable which can hold the value of the user-defined name.

Code:

Sub SaveAs_Ex2()

Dim Spreadsheet_Name As Variant

End Sub

VBA Save As Example 2-2

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:

Sub SaveAs_Ex2()

Dim Spreadsheet_Name As Variant
Spreadsheet_Name = Application.GetSaveAsFilename

End Sub

VBA Save As Example 2-3

Step 4: Use conditional IF to make sure the name user enters is valid as per the naming conventions.

Code:

Sub SaveAs_Ex2()

Dim Spreadsheet_Name As Variant
Spreadsheet_Name = Application.GetSaveAsFilename
If Spreadsheet_Name <> False Then

End Sub

VBA Save As Example 2-4

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:

Sub SaveAs_Ex2()

Dim Spreadsheet_Name As Variant
Spreadsheet_Name = Application.GetSaveAsFilename
If Spreadsheet_Name <> False Then
  ActiveWorkbook.SaveAs Filename:=Spreadsheet_Name

End Sub

IF condition Example 2-5

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:

Sub SaveAs_Ex2()

Dim Spreadsheet_Name As Variant
Spreadsheet_Name = Application.GetSaveAsFilename
If Spreadsheet_Name <> False Then
  ActiveWorkbook.SaveAs Filename:=Spreadsheet_Name
End If

End Sub

End the IF-loop Example 2-6

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.

Write File Name Example 2-7

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.

Sample Data Example 3-1

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:

Sub SaveAs_PDF_Ex3()

End Sub

VBA Save As Example 3-2

Step 2: Now, use the following code to save this file as a PDF file.

Code:

Sub SaveAs_PDF_Ex3()

ActiveSheet.SaveAs Filename:="Vba Save as.pdf"

End Sub

VBA Save As Example 3-3

Step 3: Run this code and you’ll see a pdf file generated under This PC > Documents.

PDF file Example 3-4

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 –

  1. VBA Exit Sub
  2. VBA Object
  3. VBA Object Required
  4. VBA Get Cell Value
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
Financial Analyst Masters Training Program
1000+ Hours of HD Videos
43 Learning Paths
250+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Data Science Bundle
1500+ Hour of HD Videos
80 Learning Paths
360+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Software Development Bundle
3000+ Hours of HD Videos
149 Learning Paths
600+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
Financial Analyst Masters Training Program1000+ Hours of HD Videos | 43 Learning Paths | 250+ Courses | Verifiable Certificate of Completion | Lifetime Access
All in One Data Science Bundle1500+ Hour of HD Videos | 80 Learning Paths | 360+ Courses | Verifiable Certificate of Completion | Lifetime Access
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more