Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Monitoring file saves (XL 2000 SP3)

    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.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Monitoring file saves (XL 2000 SP3)

    Put this code in the thisworkbook module:

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

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Monitoring file saves (XL 2000 SP3)

    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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Monitoring file saves (XL 2000 SP3)

    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

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Monitoring file saves (XL 2000 SP3)

    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.
    Legare Coleman

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Monitoring file saves (XL 2000 SP3)

    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

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Monitoring file saves (XL 2000 SP3)

    The code should go into the ThisWorkbook module of the workbook, not into a standard code module
    Attached Images Attached Images
    • File Type: png x.png (5.5 KB, 0 views)

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Monitoring file saves (XL 2000 SP3)

    Thank you Hans

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Monitoring file saves (XL 2000 SP3)

    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

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Monitoring file saves (XL 2000 SP3)

    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?

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Monitoring file saves (XL 2000 SP3)

    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.

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Monitoring file saves (XL 2000 SP3)

    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

  13. #13
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Monitoring file saves (XL 2000 SP3)

    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?

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Monitoring file saves (XL 2000 SP3)

    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 <F8>"


    The when the message box appears:
    Ctrl-Break
    Press [Debug] when the "code Execution has been interrupted" message appears
    Use <F8> to continue through the code...

    Steve

  15. #15
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Monitoring file saves (XL 2000 SP3)

    Yes I admit I didn't test the code.Silly me. Sorry for the confusion...
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •