Open XLSX File

Save XLSX in VBA

There are many times when we'll need to save a book in VBA and, as in all cases, it will depend on the need or task we want to do. This article touches on the topic of saving a pre-existing XLSX file in an automated way from Microsoft Excel VBA code. Among the different methods that can be used to save a book we can use the following:

Save book

Sub SaveBook ()

ActiveWorkBook.Save

End Sub

Save and Close

Sub SaveAndClose ()

'Ask if you want to save the changes and close

ActiveWorkBook.Close

End Sub

If you want the confirmation message not to appear when saving the book in VBA, and it is assumed by default that it is saved, you can use:

ActiveWorkbook.Close SaveChanges: = True

On the contrary, if when closing the active workbook you do not want to save the workbook in VBA, you can use the following code:

ActiveWorkbook.Close SaveChanges: = False

Save changes and open "save as" dialog box

Sub SaveAs ()

ActiveWorkBook.Save

Application.Dialogs (xlDialogSaveAs).Show

End Sub

There are more methods that can be used, but as we already know, it will depend on what we need, according to that we can choose appropriately. Finally, there is an option with the BeforeClose event:

Private Sub WorkBook_BeforeClose (Cancel as Boolean)

ActiveWorkBook.Save

End Sub

For this we must choose the Workbook object and two drop-down lists will appear where we will place according to the previous image, with this the changes will be saved in the book, but no notification will appear when closing.

The BeforeClose event will be executed before closing the workbook this way the workbook will be saved in VBA every time you close the file.