Solved

VBA Code

Posted on 2011-03-25
5
223 Views
Last Modified: 2012-06-21
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
Comment
Question by:Seamus2626
5 Comments
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 400 total points
ID: 35216696
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
 
LVL 7

Assisted Solution

by:harr22
harr22 earned 100 total points
ID: 35216699
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
 

Author Closing Comment

by:Seamus2626
ID: 35216722
Thanks guys!!
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35216723
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
 

Author Comment

by:Seamus2626
ID: 35230562
Thanks for your contribution too Thomas.

Seamus
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now