Bhubon Costa
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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
7 Easy Steps to Save a Workbook without Prompt with Excel VBA
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.
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.
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.
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.
- 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 (” “).
- After adding the address, type a comma and write 52 for the FileFormat argument.
- 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
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.
- Finally, click on End.
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
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.
Step 7: Check Final Response in Result
- In our sample data set, change the recipient’s name from Brine to Bhubon.
- Press F5 to run the program and no message will pop up.
- Close the worksheet without saving.
- Open the file, and see that the file has been saved with the latest current edition you have made.
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.
We, the Exceldemy Team, are always responsive to your queries.
Stay with us and keep learning.
Related Articles
- Excel VBA Macro to Save PDF in Specific Folder (7 Ideal Examples)
- VBA Code for Save Button in Excel (4 Variants)
- How to Save Multiple Sheets in Excel (6 Ideal Methods)
- Excel VBA: Save Sheet as New Workbook without Opening
- Excel VBA to Save as File Using Path from Cell (With Quick Steps)
- How to Save Excel as PDF Landscape (With Quick Steps)
if the new file is opened with the same name, it will be prompted
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.