track file proporties

A

Arjan

Is it possible to track all users that have saved an excel file? I know that
in file properties the author that has last saved the file is stated, but is
more information availble besides the information in the file properties
menu?

Arjan
 
D

Dave Peterson

If you share a workbook (tools|Share workbook), you can turn on track changes.

But sharing a workbook stops a lot of features that you may like:

See Excel's help: "Features that are unavailable in shared workbooks"

for lots of stuff.
 
A

Arjan

Hi Dave,

the situation is that I have not shared the workbook prior to sending it to
someone else. I would like to track everyone that has saved the file before I
have received it back again. Is there any other possibility?

Arjan
 
D

Dave Peterson

Are you sending separate files to multiple recipients? Then this doesn't quite
make sense to me. If you're sending the workbook, getting it back, sending it
again, get it back again, ..., then I kind of see what you want.

You could use a worksheet (Hidden) that logs every save (if macros are enabled).

This goes in a general module:

Option Explicit
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
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

This goes behind the ThisWorkbook module:

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim NextCell As Range
With Me.Worksheets("Log")
Set NextCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

With NextCell
.Value = fOSUserName
With .Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Actually, this runs as soon as the user hits the Save button (or File|Save or
File|SaveAs).

If they cancel out of that save, they still get logged.
 
A

Arjan

thx

Dave Peterson said:
Are you sending separate files to multiple recipients? Then this doesn't quite
make sense to me. If you're sending the workbook, getting it back, sending it
again, get it back again, ..., then I kind of see what you want.

You could use a worksheet (Hidden) that logs every save (if macros are enabled).

This goes in a general module:

Option Explicit
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
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

This goes behind the ThisWorkbook module:

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim NextCell As Range
With Me.Worksheets("Log")
Set NextCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

With NextCell
.Value = fOSUserName
With .Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Actually, this runs as soon as the user hits the Save button (or File|Save or
File|SaveAs).

If they cancel out of that save, they still get logged.
Click to expand...
 
Top