VBA macro to tell who last saved a file and when

[Vera]

Please I need help with a macro (saved on the workbook not in the Personal macro file) which should give the following info (lets say on sheet 1, starting A1, A2) who last saved that file and the date. Thank you so much.

Vera

 

[Greg Truby]

In that case, I think you're back to what pmdown posted early on -- File | Properties | Statistics tab

While we're on the subject - you might find this an interesting tidbit (from J-Walk's site) - you can see who last opened a file, even if he did not save it! (Provided he didn't open it in Read-Only mode.)

 

[Vera]

Too bad that there is no other way. Thank you so much.

 

[pedy]

Howdy all,

I was looking for a macro that would output the user name, last saved time & date and ended up here. It tried the code posted by dk which gave me the results I needed. however, once the file was reopened it no longer seemed to work. Can anyone help please ?

The code I used is :

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("Sheet1").Range("A1") = Application.UserName
Sheets("Sheet1").Range("A2") = Now
End Sub


thanks

 

[Greg Truby]

Welcome to the board. Dan's (dk) code should work fine. But note that the code he (and you) posted overwrites; it does not append. Is that your problem? If so, have a go at adapting the code Joe Was posted; which appends to the bottom of the column(s).

 

[Engineer Joe]

you might try and see how far you can shove your foot up your IT Guy's high security pucker. Maybe that'll open their minds a little.

 

[Joe Was]

People get confused by Office security all the time!

First even though; When security is set to medium and you are warrned that the application could contain a virus, it did no checking for a virus! The only thing security checks for is un-signed code or code modules!

So, some code can be quite envasive and do wide sweeping changes or damage if done poorly or with evil intent, most of the time it is your own application and you need the code to do your work. A self-signed certificate is not that hard to setup, but a pain in that any time you need to make changes you need to sign it again.

High security is a waste [comes fro me a person who uses VB most of the time] if people are concerned then set security to mediun, this gives the user the choice to switch to High [run no code] or Low [run all code] as they see fit!

Leave the real work to the expert utilities: Firewall, Virus checkers, spyware detectors and the like!

P.S.: Don't get me started on "Real Certificates" for they are it's as bad as paying to use your own name, for the casual user who just does some applications for limited use or around the office. Certificates do have a use and that is for commercial applications or known applications, but to make the certificates do there job all applications must be subject to them. So, the self-Certificate is a good no-cost option.

 

[pfarmer]

Hello everybody. I am back to this problem after a while. I was using the following code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("List1").Range("A1111") = Application.UserName
Sheets("List1").Range("A1112") = Now
End Sub


with no problem, because I turned to all my users the macro security level to low. But unfortunately now we are required by IT guys to have it at Higt. I tried the SelfCert certificate but is not an option.

My question is if there is a way to get the info that I need (who saved last the file and when) or using Excel's functions (I wonder why the damned Cell or Info don't have such capabilities [using Excel 2002 Windows]) or VBA even when the macro security is set at High level. Thank you so much


Vera

Are you stating that the users can't use macros or just that you would have to force them to in order for the code to work?

If so then you could have all your sheets hidden (except a sheet telling them to enable macros) hidden with a workbook close event, and then unhide those sheets with a workbook open event. This then would 'force' the users to enable macros in order to effectively use the workbook.

Once enabled then the examples in this post would work fine.

The other requirements of high or very high security however would have to be followed as well.

Could you possibly script the launch of Excel which would record the info you want external of Excel?

Perry

 

[sage_bob]

Joining the conversation late...

I use the code below on quite a few of my workbooks. It doesn't answer the question with regards to the security level but hopefully offers another choice of recording who is using the file, when and in what capacity.

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

Private Sub Workbook_Open()

Dim strUserName As String
Dim lngLength As Long
Dim LngResult As Long
'-- Set up the buffer
strUserName = String$(255, 0)
lngLength = 255
'-- Make the call
LngResult = wu_GetUserName(strUserName, lngLength)
'-- Assign the value
GetUserName = Left(strUserName, InStr(1, strUserName, Chr(0)) - 1)
If ThisWorkbook.ReadOnly = False Then
Status = "Read/Write"
Else
Status = "Read only"
End If

Open ThisWorkbook.Path & "\Recs Usage.log" For Append As #1
Print #1, "File Open ", GetUserName, Application.UserName, Now, Status
Close #1

End Sub

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

Open ThisWorkbook.Path & "\Recs Usage.log" For Append As #1
Print #1, "File Save", GetUserName, Application.UserName, Now, Status
Close #1

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Open ThisWorkbook.Path & "\Recs Usage.log" For Append As #1
Print #1, "File Close", GetUserName, Application.UserName, Now, Status
Close #1

End Sub


Hope it helps.

 

[Joe Was]

Have your Systems Department purchase a Department Certificate that any user can use for internal programs, then sign your workbook.

Otherwise you are SOL, only VBA can do what you want!

 

[pfarmer]

Please I need help with a macro (saved on the workbook not in the Personal macro file) which should give the following info (lets say on sheet 1, starting A1, A2) who last saved that file and the date. Thank you so much.

Vera

In Before_Save put this code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
UserLogging
End Sub

In a module put this code:

Sub UserLogging()

Dim LastRow As Range
Dim StartRow As Long
Dim wb As Workbook
Dim ws As Worksheet

Set wb = ThisWorkbook
Set ws = wb.Worksheets("User Log")

Set LastRow = ws.Cells(Rows.Count, 2).End(xlUp)
        
Start = 1

If LastRow.Row < 5 Then Start = 2

        With LastRow
            .Offset(Start) = Application.UserName
            .Offset(Start, 1) = Now
        End With

End Sub

This will create a list on the sheet named 'User Log'.

Perry