Excel VBA: Save Workbook Without a Prompt (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

When saving a Macro file in the exact location as before, it always prompts you to replace the file with an alert message. It can be inconvenient when you have to save data frequently and have limited time to do so. As a result, we’ll use a VBA code to resolve the problem. This tutorial will show you how to save a workbook without a prompt with Excel VBA.


How to Save a Workbook without Prompt with Excel VBA: 7 Easy Steps

We’ve included a sample data set in the image below to demonstrate the difference after running the VBA codes to stop the prompting. While saving the file, we’ll use VBA codes to disable the display alert message.

Sample Data


Step 1: Create a Module to Write VBA Code

  • Firstly, press  Alt  F11  to open the VBA Macro.
  • Click on the Insert tab.
  • From the options, select the Module to create a new Module.

Easy Steps to Save a Workbook without Prompt with Excel VBA

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


Step 2: Insert SaveAs Function in Code

  • To use the SaveAs function in this workbook, type () and SaveAs, or select SaveAs from the list of available functions.

Easy Steps to Save a Workbook without Prompt with Excel VBA

Read More: How to Save a Copy as XLSX Using Excel VBA


Step 3: Add File Address and Name in VBA Code

  • To insert the file address, go to the file location.
  • Click on the folder icon and copy the address.

Easy Steps to Save a Workbook without Prompt with Excel VBA

  • Paste the address under the Filename argument of the SaveAs function.
  • After entering the address, type the file name.
  • Finally, close the address and the file name with inverted commas (” “).

Easy Steps to Save a Workbook without Prompt with Excel VBA

  • After adding the address, type a comma and write 52 for the FileFormat argument.

Easy Steps to Save a Workbook without Prompt with Excel VBA

  • That is how the final codes will appear.
Sub Without_Prompt()
ThisWorkbook.SaveAs "C:\Users\Admin\Desktop\Bhubon\Exceldemy\3875_420093_Bhubon_excel vba save workbook without prompt\Save Without Prompt", 52
End Sub

Easy Steps to Save a Workbook without Prompt with Excel VBA

Read More: Excel VBA Save as File Format


Step 4: Run VBA Code and Check for Error

  • A prompt message will pop up as we didn’t disable the Alerts message.
  • Then, click No.

Easy Steps to Save a Workbook without Prompt with Excel VBA

  • Finally, click on End.

Easy Steps to Save a Workbook without Prompt with Excel VBA


Step 5: Write a Code to Disable Prompt Message

  • To disable the prompt message, we have to disable the DisplayAlerts message.
  • Give me the DisplayAlerts. option statement as False to disable the Prompting or alarming message.
  • Write down the following codes in the Module to stop the prompt.
Sub Without_Prompt()
'Command to not to show the Prompt
Application.DisplayAlerts = False
'Apply command to save the file
'In the File Name argument give the file name and address
ThisWorkbook.SaveAs "C:\Users\Admin\Desktop\Bhubon\Exceldemy\3875_420093_Bhubon_excel vba save workbook without prompt\Save Without Prompt", 52
Application.DisplayAlerts = True
End Sub

Easy Steps to Save a Workbook without Prompt with Excel VBA


Step 6: Run VBA Code After Disabling DisplayAlerts Command

  • This time, you click on the run button or press  F5  to run the VBA codes.
  • But no alerting message will be prompted now.

Sample Data


Step 7: Check Final Response in Result

  • In our sample data set, change the recipient’s name from Brine to Bhubon.

Sample Data

  • Press   F5  to run the program and no message will pop up.
  • Close the worksheet without saving it.

Sample Data

  • Open the file, and see that the file has been saved with the latest current edition you have made.

Sample Data


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

I hope this article has given you a tutorial about how to save workbooks without a prompt with Excel VBA. All of these procedures should be learned and applied to your dataset. Take a look at the practice workbook and put these skills to the test. We’re motivated to keep making tutorials like this because of your valuable support.

Please contact us if you have any questions. Also, feel free to leave comments in the section below.

Stay with us and keep learning.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Bhubon Costa
Bhubon Costa

Bhubon Costa, B.Sc. in Naval Architecture & Marine Engineering from Bangladesh University of Engineering & Technology, has worked with the ExcelDemy since 2021. Currently, he has been working as a reviewer. Notably, he has written over 90 articles and led several VBA content development teams. He has a great passion for the fields of data analytics and data science. His areas of expertise include Excel VBA, Power Query, Pivot Table, Power BI, MySQL, PostgreSQL, machine learning, and Python... Read Full Bio

2 Comments
  1. if the new file is opened with the same name, it will be prompted

    • Reply MD. JUNAED-AR-RAHMAN
      Junaed-Ar-Rahman Mar 9, 2023 at 1:00 PM

      Dear HARIRI,

      When you use the following command in your code, the prompt will not appear.
      Application.DisplayAlerts = False
      However, if you want to open a new file in the same name in the same folder, there may be two scenarios. You might be able to do that without any obstacles because you have a file with .xlsm extension only with that name and no .xlsx file with that name. But if you have two files with same name in same folder, one with .xlsm extension and another with .xlsx extension, then you cannot open a new Excel file in that folder. Prompt will appear to warn you that there is a folder with the same name and you will have to discard that action or have to modify the file name.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo