Save Spreadsheet on Closing

[jimibt]

Yes the worksheet_beforeclose just executes before the workbook closes so you can put whatever code in the sub you want.

The if code just checks if it's been saved if not save it to itself but you can amend it to save to another name with date etc

Code: Select all

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim sFileName As String
    Dim sDateTime As String

    With ThisWorkbook
        sDateTime = " (" & Format(Now, "yyyy-mm-dd hhmm") & ").xlsm"
        sFileName = Application.WorksheetFunction.Substitute _
          (.FullName, ".xlsm", sDateTime)
        .SaveCopyAs sFilename
    End With
End Sub

nice solution that puts the ball back in the user's court, without change to the BA code - +1

[firlandsfarm]

Code: Select all

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim sFileName As String
    Dim sDateTime As String

    With ThisWorkbook
        sDateTime = " (" & Format(Now, "yyyy-mm-dd hhmm") & ").xlsm"
        sFileName = Application.WorksheetFunction.Substitute _
          (.FullName, ".xlsm", sDateTime)
        .SaveCopyAs sFilename
    End With
End Sub

I'm probably being very stupid jimibt but I have a dangerous very limited knowledge of VBA and how to use it! I just right click on the sheet tab, select View Code and pasted the code then saved the wb. I then opened the wb from Guardian, changed a cell and closed the workbook from Guardian without first saving it and … I can't find a newly saved wb. I'm reading your code as changing the filename to "[Filename] (yyyy-mm-dd hhmm).xlsm", yes? I'm looking down the same folder path, is that where it should be?

[jimibt]

Hi there...

the code was originally from SB, but here's how it rolls:

* When the file is closed, it does a SaveAs using the original filename and it then appends the datetime onto that i.e.

original filename=C:\Users\Home\Documents\Betfair\My-Macrofile-Original.xlsm
new filename = C:\Users\Home\Documents\Betfair\My-Macrofile-Original (2019-11-15 0935).xlsm

this happens due to the line: sFileName = Application.WorksheetFunction.Substitute (.FullName, ".xlsx", sDateTime). This means that whatever path is used for the original file, will be honoured in the backup copy.

Hope this makes sense.

[spreadbetting]

I'm probably being very stupid jimibt but I have a dangerous very limited knowledge of VBA and how to use it! I just right click on the sheet tab, select View Code and pasted the code then saved the wb. I then opened the wb from Guardian, changed a cell and closed the workbook from Guardian without first saving it and … I can't find a newly saved wb. I'm reading your code as changing the filename to "[Filename] (yyyy-mm-dd hhmm).xlsm", yes? I'm looking down the same folder path, is that where it should be?

Whe you click the view code you should see all the sheets listed , the code needs to be posted under ThisWorkbook rather than individual sheets as it saves the whole workbook on close. So simply left click a sheet, select view code to bring up the vba editor then double click the ThisWorkbook tab and enter the code under that.

[firlandsfarm]

Code: Select all

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim sFileName As String
    Dim sDateTime As String

    With ThisWorkbook
        sDateTime = " (" & Format(Now, "yyyy-mm-dd hhmm") & ").xlsm"
        sFileName = Application.WorksheetFunction.Substitute _
          (.FullName, ".xlsm", sDateTime)
        .SaveCopyAs sFilename
    End With
End Sub

Just to say that works a treat spreadbetting … even more than a treat in that it saves the finishing wb every time I close it from BA … I assume it sees BA updates as requiring a save. No problem, better for me to delete what I don't need than miss what I do. Thanks for your help on this.

[spreadbetting]

No problem, glad it works for you. There's also a similar Workbook_Open code you can use that fires off code once a worksheet has been opened. I use it to set up certain variables and ensure any trigger cells are clear when a workbook opens. Might be of use somewhere down the line to you. Again it just goes in the ThisWorkbook module.

Code: Select all

Private Sub Workbook_Open()

'any code in here

End Sub

[sa7med]

No problem, glad it works for you. There's also a similar Workbook_Open code you can use that fires off code once a worksheet has been opened. I use it to set up certain variables and ensure any trigger cells are clear when a workbook opens. Might be of use somewhere down the line to you. Again it just goes in the ThisWorkbook module.

Code: Select all

Private Sub Workbook_Open()

'any code in here

End Sub

Nice one SB, had no idea there was a 'before close' event. Cheers!