Monitoring file saves (XL 2000 SP3)

Topic

New Reply

I want a way of creating, or updating if it already exists, a log showing each day a file is accessed or saved. If a file is opened or saved more than once in a day only one access or save is to be recorded. I have written a macro that looks for a sheet called UsageLog, inserts it if it does not exist, and enters today’s date at the end of Column A if the last entry in the column is not already today’s date.

However I am now way out of my depth, I need a way of monitoring every time the file is saved, and entering today’s date if the file has not previously been saved today.

All help gratefully received.

Reply | Quote

Replies

Put this code in the thisworkbook module:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim oLogRange As Range
    Set oLogRange = Worksheets("LogSheet").Range("A65536").End(xlUp).Offset(1)
    If oLogRange.Value  Int(Now) Then
        oLogRange.Offset(1).Value = Int(Now)
    End If
End Sub

Reply | Quote

Jan Karel: Did you test this code? I didn’t, but it looks like it is not doing what you wanted it to do.

1- You set oLogRange to the cell after the last non-empty cell in column A. You then test the value in this cell against Int(Now) (shouldn’t Now have a () after it?). Since oLogRange is an empty cell, Its value will be zero and that will only equal Int(Now()) if the system date is Jan 1, 1900.

2- since you are setting oLogRange.value = Now(), shouldn’t you be comparing Int(Now()) to Int(oLogRange.Value)?

Forget point 2, I misread the code where you were setting oLogRange.Value = Int(Now). Thanks for pointing that out Hans.

Reply | Quote

To pick up only one point: unlike in cell formulas, parentheses () are not obligatory when calling a function without arguments in VBA. You can use either Now() or Now, whichever you prefer.

BTW, Int(Now) is equivalent to Date. Both return the current date without a time component.

Reply | Quote

1) is correct, 2) isn’t, since Jan Karel sets the value to Int(Now).

This version should work:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim oLogRange As Range
Set oLogRange = Worksheets(“UsageLog”).Range(“A65536”).End(xlUp)
If oLogRange.Value Date Then
oLogRange.Offset(1).Value = Date
End If
End Sub

Reply | Quote

Thanks to you all for your efforts. However I have put both Jan’s and Hans’s code into a module without success. So I tried the following

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
MsgBox (“Saving File”)
End Sub

I put this in a code module in my workbook, then saved the file but my message box did not appear, is there somthing more I need to do?

TIA

Reply | Quote

The code should go into the ThisWorkbook module of the workbook, not into a standard code module

Reply | Quote

Thank you Hans

Reply | Quote

This code

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
On Error GoTo ErrorHandler

ActiveWorkbook.Sheets(“UsageLog”).Select
MsgBox (“UsageLog Sheet Exists”)

‘goto last used cell in col A, should be today’s date, inserted when the workbook was opened
Range(“A65536”).End(xlUp).Select
MsgBox (“Cell (A, Last) should be selected”)
ActiveCell.Offset(0, 1).Value = Date & ” ” & Time ‘overwrite any previous saves

ErrorHandler:

End Sub

Does not work, the selected cell remains whereever it was before the workbook is saved, but when the same code is run as an normal macro all proceeds as intended with the selected cell going to the end of Column A

Reply | Quote

Do you have a specific reason for selecting the sheet and selecting the cell? VBA code runs more efficiently if you don’t select sheets and cells.

Anyway, you should have used ActiveCell.Offset(1, 0) instead of ActiveCell.Offset(0, 1), I think.

And are you absolutely sure that you stored the code in ThisWorkbook?

Reply | Quote

This is to be a log of accesses and saves of this workbook, people will rarely be in the UsageLog sheet when they save so I made the macro go to the UsageLog sheet and the appropriate cell to enter the save date and time.

The offset is correct, the idea is to go to the last used cell in Column A, which is the list of accesses and contains today’s date from a workbook open macro, then to update Column B, which contains the Date Saved data, with the date and time of saving, overwriting any previous saves made today.

Yes it is in ThisWorkbook and it works in that it writes the date and time in the cell to the right of whatever cell is selected at the time the save is made (I have been in sheet UsageLog during all the testing, just selecting different cells before I save), it just does not move the selection to the last cell of Column A before doing the writing.

Reply | Quote

Hans’s point is that you can put the data on a different sheet than the active sheet in any cell without selecting. There is no need to have the code select a new sheet or a new cell.

The code:
ActiveWorkbook.Sheets(“UsageLog”).Range(“A65536″).End(xlUp).Offset(0, 1).Value = Date & ” ” & Time

Will do what you have without changing what is currently selected…

Steve

Reply | Quote

Thanks for that Steve.

I still find it puzzling that the selected cell does not change when the code is run as a WorkbookBeforeSave macro but it works perfectly as an ordinary macro.

Incidentally is it possible to step through a WorkbookBeforeSave macro?

Reply | Quote

I find it puzzling also. Have you confirmed that the code being run as a “before save”? Typically this may not occur if the code in not in the “ThisWOrkbook” object of is named improperly.

You can step through it, by adding a MSGBOX line as the first line of code (for example):
MsgBox “‘Before Save’ event triggered ” & vbCrLf & _
“Press ctrl-break then [Debug] to step thru code with ”

The when the message box appears:
Ctrl-Break
Press [Debug] when the “code Execution has been interrupted” message appears
Use to continue through the code…

Steve

Reply | Quote

Yes, you can step through the Workbook Before Save event code. The easy way is to put a Stop statement at the beginning of the code. Then when the event routine is entered, VBA will break the execution when it gets to the Stop statement with the VBA Window displayed and you can step through the code from there.

It is probably going to be impossible for us to determine what you are doing wrong without seeing your workbook. People keep asking if you put the code into the module behind the ThisWorkbook object, but we can’t tell if you actually did that. Could you alter the workbook to disguise any confidential data and upload it? You will have to ZIP it if it is larger than 99k.

Reply | Quote

Is there a way to add User information to this log. In other words, the log maintains the date of access and save, but also populates an adjacent cell/column with user ID information such as a network logon or something like that? I have seen this done with an MS Access database, but that database was also linked to a personnel table.

Reply | Quote

You can use something like

ActiveCell.Offset(0, 2).Value = Environ(“username”)

to store the Windows login name in the cell two columns to the right of the active cell.

Reply | Quote

Thanks for the help everyone. I’ll deal with the points raised in turn.

The code is exactly as I put it in post 683787, which I believe is a ‘before save’. Also the macro is partly executing when I save as I explained in post 683809, it is putting the date into the cell to the right of the selected cell, it just is not moving the selected cell to the end of Column A

The code is in the ‘ThisWorkbook’ object; not having in there was my early error but Hans put me right on that in post 683772.

Legare, I am not clear what the ‘the module behind the ThisWorkbook object’ is, I double clicked on ThisWorkbook in the Project Window and pasted the code in there, if I try to add a module whilst in ThisWorkbook VBA adds a module into the Modules, not into theObjects/ThisWorkbook.

I think adding user information is a good idea too.

Reply | Quote

The only and hence last non-blank cell in column A is A1. So the code will jump to A1 and place the date and time in B1 every time.

Reply | Quote

Sorry people.

My attachment did not have a Date Accessed in cell A2, this was because my original development Workbook is at work and I will not have access to it for two weeks, so I had to start a new one and I forgot to put a date in A2, in my original book this is done with a workbook open macro.

Apologies. This attachment is correct

Reply | Quote

In this workbook, the last non-blank cell in column A is A2.
When I save the workbook, A2 is selected and the current date/time is placed in B2.
So the code works correctly.

Reply | Quote

Does the attached do what you want?

Edited by Legare Coleman to replace the macro that will still work if the worksheet has more than 65k rows.

Reply | Quote

Yes I admit I didn’t test the code.Silly me. Sorry for the confusion…

Reply | Quote