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

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

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

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.

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

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.