Save Spreadsheet on Closing

[firlandsfarm]

Hi, I just found out the hard way that BA doesn't save the Excel spreadsheet when closing it through Guardian … yep, I was setting up a template with if's and then's, closed the spreadsheet through Guardian and lost everything! Can BA not be set up to give a warning? Yes I know I can close the spreadsheet through Excel but some of us are only human and we will make mistakes!

[jimibt]

I'm only guessing here, but the reason that it isn't saved is because it is opened and operated upon using Office INTEROP. This means that every action required is explicit and only happens as a result of being programmed. I'd imagine that the CLOSE function ignores prompts and closes, despite the state of the worksheet. I can see a very good reason for this due to temporary crud getting saved everytime with the worksheet.

I for one have ironically been SAVED by this behaviour, after making test changes and forgetting to save as a new sheet. Potentially a global option could be added to Save Excel Automation Sheet on exit. By default, this would be set to NO.

[firlandsfarm]

Yeah, sometimes in other situations I have been saved just like you Jimibt and sometimes not like this one! In your situation of testing new thoughts I always now use "save as" before changing anything and work with the new copy. I have no idea how the INTEROP thingy works but I guess it is a bit like when running a macro (in Access) and you can choose to disable dialogue boxes. I think I would prefer options of, never save, always save and always ask.

[ShaunWhite]

Excel's weakness has always been a lack of a proper change audit. That's why it's not usually the analytical tool of choice for modeling in situations where an 'oops' can cost serious money.

[firlandsfarm]

Excel's weakness has always been a lack of a proper change audit. That's why it's not usually the analytical tool of choice for modeling in situations where an 'oops' can cost serious money.

Hi Shaun … sorry but can you give me a translation of that please!

[ShaunWhite]

Hi Shaun … sorry but can you give me a translation of that please!

Apart from the basic Undo/Redo commands and SaveAs, Excel doesn't allow you to rewind to points in time or to produce a list all the changes you've made. ie If you realise that 3 weeks ago you did something that worked especially well or badly, you can't audit what was different. Source control can allow you to go back to prior versions but there's no way to document the changes. When firms are trusting the returns on a £billion pension fund to a model, there's no room for not knowing how/why/when a model changes.

[sa7med]

Hi Shaun … sorry but can you give me a translation of that please!

Apart from the basic Undo/Redo commands and SaveAs, Excel doesn't allow you to rewind to points in time or to produce a list all the changes you've made. ie If you realise that 3 weeks ago you did something that worked especially well or badly, you can't audit what was different. Source control can allow you to go back to prior versions but there's no way to document the changes. When firms are trusting the returns on a £billion pension fund to a model, there's no room for not knowing how/why/when a model changes.

There's supposedly an autosave vault although i've never figured out how it works

[spreadbetting]

You can add code under ThisWorkbook to ensure you save the sheet if it gets closed without the Save dialog but remember it'll save any errors too

Code: Select all

Option Explicit
Private Sub Workbook_BeforeClose(Cancel as Boolean) 
 If Me.Saved = False Then Me.Save 
End Sub

[firlandsfarm]

You can add code under ThisWorkbook to ensure you save the sheet if it gets closed without the Save dialog but remember it'll save any errors too

Code: Select all

Option Explicit
Private Sub Workbook_BeforeClose(Cancel as Boolean) 
 If Me.Saved = False Then Me.Save 
End Sub

That could be interesting Spreadbetting … can it SaveAs so that it saves the work but as a different file in case of any errors?

Thanks for the 'translation' Shaun. I thought that's what you meant but just wanted to make sure.

[spreadbetting]

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

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