Visual Basic - Tuesday

[Guest]

I have an excel file.
In VB when the file is opened, I want it to identify that it is a Tuesday
and the file must save (only the first time it is opened on a Tuesday) onto
a different location as back up only once on a Tuesday. If the file is
closed on a Tuesday and re-opened on a Tuesday, it must not overwrite the
saved copy.

 

[Chip Pearson]

Ronel,

Put the following code in the ThisWorkbook code module.

Private Sub Workbook_Open()
Dim SavedDate As Variant
Dim HasSaved As Variant
If Weekday(Now) = vbTuesday Then
With ThisWorkbook
On Error Resume Next
Err.Clear
SavedDate = .Names("SavedDate").RefersTo
If Err.Number <> 0 Then
SavedDate = CDate(CLng(Now)) - 1
Else
SavedDate = CDate(Mid(.Names("SavedDate").RefersTo, 2))
End If
Err.Clear
HasSaved = .Names("HasSaved").RefersTo
If Err.Number <> 0 Then
HasSaved = False
Else
HasSaved = CBool(Mid(.Names("HasSaved").RefersTo, 2))
End If
If (SavedDate < CLng(Now)) And (HasSaved = False) Then
.SaveCopyAs Filename:=Left(.FullName, Len(.FullName) - 4) & _
"\Backup\" & "_" & Format(Now, "dd_mmm_yyyy") & ".xls"
End If
.Names.Add Name:="SavedDate", RefersTo:=CDate(CLng(Now)),
Visible:=False
.Names.Add Name:="HasSaved", RefersTo:=True, Visible:=False
End With
End If
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)