excel shared file

  • Thread starter Thread starter Gabriel
  • Start date Start date
G

Gabriel

Hi,

Is there any way to track the activity on a shared excel file? Can it
be done in a VBA code?

I would appreciate any help
Thank you

Gabriel
 
How about the built in Tools|Track Changes (and this requires the workbook to be
shared, too!)
 
Actually I'm interested in building something like an activity log.
For instance, Mr Y is accessing the file at 2pm for 12 minutes. I want
this to be recorded on one of the excel worksheets. Would that be
possible ?

Thank you
 
I don't think I'd put it in the same workbook. You'd have to save the workbook
when they're closing it and if the user made changes that he/she didn't want to
save, then you might make more work for them (or maybe you!).

You could put it in another workbook or in a text file (in a common folder).

If you like the text file idea, how about putting this in a general module in
that workbook:

Option Explicit
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Public myStartTime As Date

Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If lngX <> 0 Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = ""
End If
End Function

And under ThisWorkbook add two procedures:

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim oFilenum As Long
oFilenum = FreeFile()
Close #oFilenum
Open "C:\output.txt" For Append As #oFilenum
Print #oFilenum, "Close: ", fOSUserName, Now, Format(Now - myStartTime,
"hh:mm:ss")
Close #oFilenum
End Sub

Private Sub Workbook_Open()
Dim oFilenum As Long
oFilenum = FreeFile()
Close #oFilenum
Open "C:\output.txt" For Append As #oFilenum
Print #oFilenum, "Open: ", fOSUserName, Now
Close #oFilenum
myStartTime = Now
End Sub

It actually creates two lines in the text file--one with the open and one with
the close. If you don't want both, just keep that "mystarttime = now" in the
Open procedure.
 
Back
Top