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

 

[dk]

Hi,

You can use this code. You need to place it in the workbook code module. To do this, right click the lower of the 2 Excel icons on the left hand of your screen and choose View Code.

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

This will only give the correct name if the user name has been set up in Tools, Options, General. If you want some code which puts the Windows username into A1 then please post back (code is more complicated).

 

[pmdown]

No.

I have better solution:

Use The BuiltinDocumentProperties of Workbook Object. See VBA Help for other details, particularly see "LastAuthor" and "LastSaveTime" items.

 

[dk]

Better?! It's just a different way of doing it, and both methods will produce identical results. The method I've used is slightly simpler and I actually posted a usable procedure.

Regards,
Dan

 

[pmdown]

Hi dk.

I am still confident, that my solution is much better.

For example: if You want register "who last saved that file and the date" by Your way:
You must add Your macro in every new Workbook and in all existing workbooks (!?!)

If You have 500 Workbooks You must in each add Your macro. Inconceivable.

My solution only read the properties of arbitrary Workbook...without macro.

Best Regards

 

[dk]

On 2002-10-11 06:27, pmdown wrote:
Hi dk.

I am still confident, that my solution is much better.

For example: if You want register "who last saved that file and the date" by Your way:
You must add Your macro in every new Workbook and in all existing workbooks (!?!)

If You have 500 Workbooks You must in each add Your macro. Inconceivable.

My solution only read the properties of arbitrary Workbook...without macro.

Best Regards

Are you trying to be deliberately argumentative?

1. You haven't answered the question. They wanted the last saved information displayed in cell A1 and A2. What you've offered is Yes, this information is freely available by choosing File, Properties, let's forget what you actually asked for...

2. Your initial post was to use the BuiltInDocumentProperties object, and that had some validity. However, how would you cause the values in A1 and A2 to be updated without using the BeforeSave event?

3.

You must add Your macro in every new Workbook and in all existing workbooks (!?!)

If You have 500 Workbooks You must in each add Your macro. Inconceivable.

Utter boll**ks! There are hundreds of people on this board who could easily achieve this without adding the macro to 500 workbooks.

4.

My solution only read the properties of arbitrary Workbook...without macro.

See point 1 above.

Regards,
Dan

 

[Mudface]

To add to Dan's comments, I did post a solution using the BuiltInDocumentProperties, but then hastily deleted it when I realised that, if you use it in the BeforeSave event, you will only return the previous user and save time, not the current user and save time.

So where would you place this code- in the Open or another event or as a standalone procedure? In any case you won't get an up-to-date record of who last saved it.

 

[pmdown]

Hi dk.

I'am sorry. You are right.

I don't trying to be deliberately argumentative, I only wrong have been reading The original question. I didn't see the text in parenthesis: "...lets say on sheet 1, starting A1, A2", and i suppose, that Vera wanted to get Name and Time from another other external Workbook.

Forget my posts.

Best regards.

 

[Joe Was]

I use this in one of my applications, as a share file on the network, the file is transferred to and updated on the users PC then saved back to the network. Each person who saves the file is added to the ongoing log on Sheet1(UserLog). I cleaned up the code here to work on a simple file. JSW

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Builds an on going list of who saved file when.
'Code must go into the "ThisWorkbook" module!
'All data is placed in column "A" adjust width and formats.

With Worksheets("Sheet1")
.Range("A1") = "This file last saved ""By"" who ""On"", list!"
Range("A65536").End(xlUp).Offset(1, 0) = _
"By: " & Application.UserName & ", On: " & Now & " "
End With
End Sub

 

[Vera]

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