VBA code to save workbook with no prompt

[sagain2k]

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

 

[scolty1985]

Worked perfectly, thank you very much

Hi, save this macro in the ThisWorkbook object rather than in a module.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Save
End Sub

 

[salimohammed007]

Try this...

Sub SaveWbWithoutPrompt()

Activeworkbook.Saved=True

End Sub

This code "Activeworkbook.Saved=True" does not save the file. Instead it only removes the Prompt message.

 

[Midknight63]

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

 

[epardo87]

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?

 

[Midknight63]

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.

 

[salimohammed007]

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'

 

[epardo87]

Thanks, actually, after posting it, I kept searching and found ThisWorkbook.Save, and it worked, guess both do the same

 

[rediffusion]

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:

[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:

I have a "13.xlsx" open at the moment.
I put VBA in "PERSONAL.XLSB" to code worked in all documents.

 

[rediffusion]

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

I have nothing works help if you can!?

 

[rediffusion]

Re: 0Key I find solution!

Put code in ThisWorkbook:

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