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.
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.
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.
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.
- 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
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.
- 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 it.
- Open the file, and see that the file has been saved with the latest current edition you have made.
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
- Excel VBA: Save Workbook in Specific Folder
- How to Use Macro to Save Excel File with New Name
- Excel VBA: Save Workbook as New File in Same Folder
- Excel VBA to Save File with Variable Name
- Excel VBA to Save Workbook in Specific Folder with Date
- Excel VBA to Save as File Using Path from Cell
- How to Save Excel Macro Files as Filename from Cell Value
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.