VBA Code

Hi,

Attached is some code i use to record when a user enters/exits a spreadsheet.

Does amyone have any code that can calculate the time difference i.e. the difference between entering and leaving

Thanks
Seamus
Private Sub Workbook_Open()


   With Application
      .DisplayAlerts = False
   End With
      ActiveWorkbook.UpdateLinks = xlUpdateLinksAlways


   With Application
      .DisplayAlerts = False
   End With




Debug.Print "open"
WriteFile Environ("USERNAME") & " entered at " & Now()
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Debug.Print "closed"
WriteFile Environ("USERNAME") & " left at " & Now()
End Sub

Sub WriteFile(strText As String)
   Dim MyFile As String, fNum As Integer
   MyFile = "\\ukhibmdata02\rights\Asset Services Risk Team\DCDIV002 Breaks\DCDIV.txt"
   'set and open file for output
   fNum = FreeFile()
   Open MyFile For Append As fNum
   'write project info and then a blank line. Note the comma is required
   Print #fNum, strText
   Write #fNum,
   Close #fNum

End Sub

Open in new window

Seamus2626Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
SiddharthRoutConnect With a Mentor Commented:
Try this

Dim dt1 As Date, dt2 As Date
Private Sub Workbook_Open()
    Application.DisplayAlerts = False
    ActiveWorkbook.UpdateLinks = xlUpdateLinksAlways
    Application.DisplayAlerts = True
    dt1 = Now
    WriteFile Environ("USERNAME") & " entered at " & dt1
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    dt2 = Now
    WriteFile Environ("USERNAME") & " left at " & dt2
    WriteFile Environ("USERNAME") & " Time Difference " & Format(dt2 - dt1, "hh:mm:ss")
End Sub

Sub WriteFile(strText As String)
   Dim MyFile As String, fNum As Integer
   MyFile = "\\ukhibmdata02\rights\Asset Services Risk Team\DCDIV002 Breaks\DCDIV.txt"
   'set and open file for output
   fNum = FreeFile()
   Open MyFile For Append As fNum
   'write project info and then a blank line. Note the comma is required
   Print #fNum, strText
   Write #fNum,
   Close #fNum
End Sub

Open in new window


Sid
0
 
harr22Connect With a Mentor Commented:
I would use an open event and stamp the current time somewhere in the sheet
then with a close event you can take the difference.

 
Private Sub Workbook_Open()
 Range("A1").Value = Now()
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
 MsgBox ("File was Open for " & Now() - Range("A1").Value)
End Sub

Open in new window

0
 
Seamus2626Author Commented:
Thanks guys!!
0
 
nutschCommented:
Put in a regular module

Global TimeEntered As Double

Open in new window


Then use it in your code (lines 14 and 23) as:

Private Sub Workbook_Open()


   With Application
      .DisplayAlerts = False
   End With
      ActiveWorkbook.UpdateLinks = xlUpdateLinksAlways


   With Application
      .DisplayAlerts = False
   End With

TimeEntered = Time()


Debug.Print "open"
WriteFile Environ("USERNAME") & " entered at " & Now()
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Debug.Print "closed"
WriteFile Environ("USERNAME") & " left at " & Now() & ", stayed " & Format(Time() - TimeEntered, "hh:mm:ss")
End Sub

Sub WriteFile(strText As String)
   Dim MyFile As String, fNum As Integer
   MyFile = "\\ukhibmdata02\rights\Asset Services Risk Team\DCDIV002 Breaks\DCDIV.txt"

Thomas
   'set and open file for output
   fNum = FreeFile()
   Open MyFile For Append As fNum
   'write project info and then a blank line. Note the comma is required
   Print #fNum, strText
   Write #fNum,
   Close #fNum

End Sub

Open in new window


Thomas
0
 
Seamus2626Author Commented:
Thanks for your contribution too Thomas.

Seamus
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.