ok I kinda found a solution to my problem. I can get the date to display but the time shows up as 00:00 rather than current time. Here's the code:
----------------------------------------------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Open "W:\RSSC\Getronics\backup\log.txt" For Append As #1
Print #1, Environ("username") & " Closed Getronics SLA-Billable " & Format(Date, "yyyy-mm-dd hh:mm")
Close #1
On Error GoTo errhandle
Dim yesterdayfile As String
Dim todayfile As String
yesterdayfile = "W:\RSSC\Getronics\backup\Getronics SLA-Billable" & Weekday(Date - 3) & ".xls"
todayfile = "W:\RSSC\Getronics\backup\Getronics SLA-Billable" & Weekday(Date) & ".xls"
question = MsgBox("Would you like to save the Getronics SLA-Billable? This will save any and all changes made during this session. ", vbYesNo, "Warning")
If question = vbYes Then
Save
If FileExist(yesterdayfile) Then
Kill yesterdayfile
End If
srcfile = "W:\RSSC\Getronics\Getronics SLA-Billable.XLS"
tgtfile = "W:\RSSC\Getronics\backup\Getronics SLA-Billable.XLS"
If FileExist(todayfile) Then
Kill todayfile
SaveAs todayfile
Else
SaveAs todayfile
End If
Exit Sub
Else
MsgBox "Getronics SLA-Billable not saved.", vbCritical, "Error"
Exit Sub
End If
errhandle:
MsgBox "Error Saving backup of spreadsheet.", vbCritical, "Error"
End Sub
Public Function FileExist(asPath As String) As Boolean
If UCase(Dir(asPath)) = UCase(TrimPath(asPath)) Then
FileExist = True
Else
FileExist = False
End If
End Function
Public Function TrimPath(ByVal asPath As String) As String
If Len(asPath) = 0 Then Exit Function
Dim x As Integer
Do
x = InStr(asPath, "\")
If x = 0 Then Exit Do
asPath = Right(asPath, Len(asPath) - x)
Loop
TrimPath = asPath
End Function
Private Sub Workbook_Open()
Open "W:\RSSC\Getronics\backup\log.txt" For Append As #1
Print #1, Environ("username") & " Opened Getronics SLA-Billable" & Format(Date, "yyyy-mm-dd hh:mm")
Close #1
End Sub
|
