How to schedule an auto "Save As" of an Excel spreadsheet daily...

[Turbine Mechanic]

I have a training matrix that changes dynamically due to conditional
formatting of the cells. I would like to capture these changes automatically
as a new file on a daily basis. Also, I would like to automatically name
these files as the current date and save to a different folder. It would be
nice if there was simple software that would do this.
Thanks in advance,

Dave

 

[NoodNutt]

G'day Dave

Not sure if you want to save everytime a change is made, or just at the end
of a session.

If it is just at the end of the session then, disable the save button on the
Menu & the Ribbon
Insert a CmdBtn and put this code behind it. (Instructing users to use the
Macro CmdBtn only to save)

Try something like this

Sub Save_Me()
'Working in 2000-2007
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim CloseResp As Integer

CloseResp = MsgBox("This will Save the file and End this session",
vbOKCancel)
If CloseResp = vbOK Then

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set Sourcewb = ActiveWorkbook

If Val(Application.Version) < 12 Then
'You use Excel 2000-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
Select Case Sourcewb.FileFormat
Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
Case 52:
If .HasVBProject Then
FileExtStr = ".xlsm": FileFormatNum = 52
Else
FileExtStr = ".xlsx": FileFormatNum = 51
End If
Case 56: FileExtStr = ".xls": FileFormatNum = 56
Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
End Select
End If

TempFilePath = Environ$("temp") & "\"
TempFileName = Sourcewb.Name & " " & Format(Now, "dd-mmm-yy h-mm")

With DestWB
.SaveAs TempFilePath & TempFileName & FileExtStr,
FileFormat:=FileFormatNum
On Error Resume Next
End With

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

Application.Quit

Else

Cancel = True

End Sub

This will save the file name with a date & time extension so you can track
each time the file has been save, this way you can view any changes between
the saved files.

HTH
Mark.

 

[NoodNutt]

Dave

Forgot to include this for autosaving

Put this in the "This Workbook" section


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

Application.OnTime Now + TimeValue("00:30:00"), "Save_Me"

End Sub

Private Sub Workbook_Open()

Application.OnTime Now + TimeValue("00:30:00"), "Save_Me"

End Sub

You will have to try each one, (can't remember which one to use, have got
brain damage from a big night at work.)

The timer is set for 30 mins, but you can change it to autosave at any time
interval you want.

Mark.