VBA code to save workbook with no prompt

sagain2k

Board Regular
Joined
Sep 8, 2002
Messages
94
What's the syntax for saving an Excel workbook file so it saves over the existing same filename without prompting you? Can't seem to find it...thanks!

The code I'm using to save the workbook is:

ActiveWorkbook.SaveAs Filename:="C:\Data\testfile.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
I'm using an excel document in a SharePoint lbrary as a template for a new document.
I wanted to control everything so the user can’t change the FileName nor the path, so I don’t want any prompt at all. The methods Application.DisplayAlerts = False and ThisWorkbook.Saved still prompted the user when I use the method "ActiveWorkbook.SaveAs" in Sub Workbook_BeforeSave .


The trick that did it for me is to use the cancel argument “cancel=true”, which I used after I saved the file.

Problem solved.

The code:
Sub Workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean)
Application.EnableEvents = False 'Don't want any other events getting fired off...
Application.DisplayAlerts = False '...nor any "Are you sure?" warnings displayed.
ActiveWorkbook.SaveAs Filename:=MyPathAndFileName & _
".xlsm" _
, ReadOnlyRecommended:=False _
, Password:="" _
, WriteResPassword:="" _
, CreateBackup:=False _
, FileFormat:=52 ' or xlOpenXMLWorkbookMacroEnabled
Cancel = True
Application.EnableEvents = True 'RESETS EVENTS
Application.DisplayAlerts = True 'RESETS DISPLAY ALERTS
ActiveWorkbook.Saved = True
End Sub
 
Upvote 0
Hi there,

I'm new at this and I built a form, so I need that after filling the form, when hitting an OK button I placed, besides filling everything in the excel sheet and closing the form window (these is already working) I also need it to automatically save the workbook, so I paste the code ActiveWorkbook.Close SaveChanges:=True, and it works fine but I don't want it to close the workbook, how could I write it so that it only save changes without closing?
 
Upvote 0
Don't put your code in "Workbook_BeforeClose". Create another sub with another name, place your code there and have your button call that sub when clicked. Also put a call to that sub in Workbook_BeforeClose.
 
Upvote 0
Hi there,

I'm new at this and I built a form, so I need that after filling the form, when hitting an OK button I placed, besides filling everything in the excel sheet and closing the form window (these is already working) I also need it to automatically save the workbook, so I paste the code ActiveWorkbook.Close SaveChanges:=True, and it works fine but I don't want it to close the workbook, how could I write it so that it only save changes without closing?

Instead of using 'ActiveWorkbook.Close SaveChanges:=True' use only 'ActiveWorkbook.Save':)
 
Upvote 0
Thanks, actually, after posting it, I kept searching and found ThisWorkbook.Save, and it worked, guess both do the same
 
Upvote 0
Hello!
Read all in this thread but it didn't help me!
I need "Save all open workbooks or one by one after clicking on the cross".
I find this code:
Code:
[COLOR=#000000][FONT=Consolas]Dim wb As Workbook    [/FONT][/COLOR][URL="https://www.autohotkey.com/docs/redirect.php?topic=For"][COLOR=#1990B8]For[/COLOR][/URL][COLOR=#000000][FONT=Consolas] Each wb [/FONT][/COLOR][URL="https://www.autohotkey.com/docs/redirect.php?topic=In"][COLOR=#1990B8]In[/COLOR][/URL][COLOR=#000000][FONT=Consolas] Application[/FONT][/COLOR][URL="https://www.autohotkey.com/docs/redirect.php?topic=."][COLOR=#A67F59].[/COLOR][/URL][COLOR=#000000][FONT=Consolas]Workbooks    wb[/FONT][/COLOR][URL="https://www.autohotkey.com/docs/redirect.php?topic=."][COLOR=#A67F59].[/COLOR][/URL][COLOR=#000000][FONT=Consolas]Save[/FONT][/COLOR][COLOR=#000000][FONT=Consolas]Next wb
[/FONT][/COLOR]
I Put it here:
d716c28672.jpg


I have a "13.xlsx" open at the moment.
I put VBA in "PERSONAL.XLSB" to code worked in all documents.
 
Upvote 0
MissClick. How do I edit my post?

ок:
Here is my code:

Dim wb As Workbook


For Each wb In Application.Workbooks
wb.Save
Next wb
Application.DisplayAlerts = False

26633cfc44.jpg


I have nothing works help if you can!?
 
Upvote 0
Re: 0Key I find solution!

Put code in ThisWorkbook:

Code:
Private Sub Workbook_Open()
    Application.VBE.MainWindow.visible = True
    Me.VBProject.VBComponents("TEST").Activate
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top