auto save workbook upon close

[steelstorm]

HI all,

We have been having issues in my company recently with workbooks occassionally vanishing off of shared drives and after a little research I saw that VBA may be a quick solution for us until we work on setting up privelages for this drive we all work out of currently.

We have workbooks that get updated alot and I was wondering if anyone possibly has any code laying around that will automatically save a workbook to a specified path everytime it is closed.....even if no changes were made. And if it's not too much to add coding wise possibly delete the existing copy that was already in this location? Or maybe even easier put some kind of date or time stamp on the saved workbook so we can tell which is the most current.

I looked up some examples online that in theory looked like they should of worked to me but I could not get any of them to work.

Thanks in advance for any help or suggestions,

[steelstorm]

Just wanted to add that this would be a backup saved in a totally different spot than the one we use everyday. I am planning to setup a hidden folder that only I have access to or know about so when these things disappear again I can just forward the user the most recent copy before it was deleted.

[GTO]

What version(s) of excel are users using? Are the workbooks being saved in .xls, .xlsx, .xlsm format?

[steelstorm]

We are using 2010, Microsoft Excel Worksheet (.xlsx).

[HaHoBe]

Hi, steelstorm,

beside making sure that all options are properly set for using macros on every computer the normal way would be using the Workbook_BeforeClose(Cancel As Boolean) event in ThisWorkbook. This would also mean a different workbook style as this has to be changed from xlsx(macrofree) to xlsm(macrenabled).

The code for the single workbook could look like this (please change the directory as needed):

[vba]Private Sub Workbook_BeforeClose(Cancel As Boolean)

Const cstrFolderOfBU = "C:\MyExcelBackUps\"

With ActiveWorkbook
.SaveCopyAs cstrFolderOfBU & Left(.Name, Len(.Name) - 5) & Format(Now, "yymmdd hhnnss") & ".xlsx"
End With

End Sub[/vba] Besides having the same effort for each user I´d use class programming in an addin where the link to the addin would be put in the startup directory of Excel for the user.

In a class Module called XLEvent I´d put the following code:
[vba]Option Explicit

Private WithEvents xlApp As Excel.Application

Private Sub Class_Initialize()
Set xlApp = Application
End Sub

Private Sub Class_Terminate()
Set xlApp = Nothing
End Sub

Private Sub xlApp_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)

Const cstrFolderOfWB As String = "E:\Quiver\"
Const cstrFolderOfBU = "C:\MyExcelBackUps\"

With ActiveWorkbook
If InStr(1, .FullName, cstrFolderOfWB) > 0 Then
.SaveCopyAs cstrFolderOfBU & Left(.Name, Len(.Name) - 5) & Format(Now, "yymmdd hhnnss") & ".xlsx"
End If
End With

End Sub[/vba] Into ThisWorkbook:

[vba]Private p_evtEvents As XLEvents

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Set p_evtEvents = Nothing

End Sub

Private Sub Workbook_Open()

Set p_evtEvents = New XLEvents

End Sub[/vba] Any time a workbook in the wanted directory is closed a copy with date & time stamp is created.

HTH
Holger

[steelstorm]

I thought about it after I replied earlier and went back to work that I bet I did not change the file extension according in the macros I tried earlier.

What you supplied is exactly what I was looking and more, alot of the ones I did see online were extremely basic.....but would of worked I'm sure if I would of mod'd them correctly.

Thanks so much for your help, this will definitely make my life alot easier