Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 238
  • Last Modified:

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

0
Seamus2626
Asked:
Seamus2626
2 Solutions
 
SiddharthRoutCommented:
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
 
harr22Commented:
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now