Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Sep 2015
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Time Stamping For Excel Documents when opened and closed

    This macro was written for MS Word. I'd like to do the same thing for when I open/close an excel document. Can this been adapted?




    Sub AutoOpen()
    ActiveDocument.Variables("LastOpen").Value = Now()
    End Sub

    Sub AutoClose()
    Dim objFSO, objFolder, objTextFile, objFile
    Dim strDirectory, strFile, strText
    strDirectory = "d:\work"
    strFile = "\work.csv"

    ' Create the File System Object
    Set objFSO = CreateObject("Scripting.FileSystemObject")

    ' Check that the strDirectory folder exists
    If objFSO.FolderExists(strDirectory) Then
    Set objFolder = objFSO.GetFolder(strDirectory)
    Else
    Set objFolder = objFSO.CreateFolder(strDirectory)
    Debug.Print "Just created " & strDirectory
    End If

    If objFSO.FileExists(strDirectory & strFile) Then
    Set objFolder = objFSO.GetFolder(strDirectory)
    Else
    Set objFile = objFSO.CreateTextFile(strDirectory & strFile)
    Debug.Print "Just created " & strDirectory & strFile
    End If

    Set objFile = Nothing
    Set objFolder = Nothing
    ' OpenTextFile Method needs a Const value
    ' ForAppending = 8 ForReading = 1, ForWriting = 2
    Const ForAppending = 8

    Set objTextFile = objFSO.OpenTextFile(strDirectory & strFile, ForAppending, True)

    'Build the string to write
    strText = """" & ActiveDocument.FullName & """" & "," & ActiveDocument.Variables("LastOpen").Value & "," & Now()

    ' Writes strText every time you run this VBScript
    objTextFile.WriteLine (strText)
    objTextFile.Close
    End Sub

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Timesr1,

    Welcome to the Lounge as a New Poster!

    You don't need a macro to do this as Excel automatically updates the builtin workbook properties.

    You can access these values via the following VBA:
    Code:
    Sub DocumentHistory()
    
       Dim zMsg As String
        
       With ActiveWorkbook.BuiltinDocumentProperties
       
          zMsg = "Creation:" & vbTab & vbTab & .Item("Creation date") & vbCrLf
          zMsg = zMsg & "Updated:" & vbTab & vbTab & .Item("Last save time") & vbCrLf
          zMsg = zMsg & "Last Print:" & vbTab & vbTab & .Item("Last print date")
       
       End With   'ActiveWorkbook
       
       MsgBox zMsg, vbOKOnly + vbInformation, _
              "Document Update History:"
    
    End Sub   'DocumentHistory
    WorkbookProps.JPG

    Available Properties:
    Title
    Subject
    Author
    Keywords
    Comments
    Template
    Last author
    Revision number
    Application name
    Last print date
    Creation date
    Last save time
    Total editing time
    Number of pages
    Number of words
    Number of characters
    Security
    Category
    Format
    Manager
    Company
    Number of bytes
    Number of lines
    Number of paragraphs
    Number of slides
    Number of notes
    Number of hidden Slides
    Number of multimedia clips
    Hyperlink base
    Number of characters (with spaces)
    Content type
    Content status
    Language
    Document version

    HTH
    Last edited by RetiredGeek; 2015-09-03 at 14:50.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Sep 2015
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Time Stamping For Excel Documents when opened and closed

    Thank you for the quick reply. I am trying to log when the file is opened, edited and closed again. I'd also like it to write this information to another Excel spreadsheet. Any idea
    how to accomplish this?



    Quote Originally Posted by RetiredGeek View Post
    Timesr1,

    Welcome to the Lounge as a New Poster!

    You don't need a macro to do this as Excel automatically updates the builtin workbook properties.

    You can access these values via the following VBA:
    Code:
    Sub DocumentHistory()
    
       Dim zMsg As String
        
       With ActiveWorkbook.BuiltinDocumentProperties
       
          zMsg = "Creation:" & vbTab & vbTab & .Item("Creation date") & vbCrLf
          zMsg = zMsg & "Updated:" & vbTab & vbTab & .Item("Last save time") & vbCrLf
          zMsg = zMsg & "Last Print:" & vbTab & vbTab & .Item("Last print date")
       
       End With   'ActiveWorkbook
       
       MsgBox zMsg, vbOKOnly + vbInformation, _
              "Document Update History:"
    
    End Sub   'DocumentHistory
    WorkbookProps.JPG

    Available Properties:
    Title
    Subject
    Author
    Keywords
    Comments
    Template
    Last author
    Revision number
    Application name
    Last print date
    Creation date
    Last save time
    Total editing time
    Number of pages
    Number of words
    Number of characters
    Security
    Category
    Format
    Manager
    Company
    Number of bytes
    Number of lines
    Number of paragraphs
    Number of slides
    Number of notes
    Number of hidden Slides
    Number of multimedia clips
    Hyperlink base
    Number of characters (with spaces)
    Content type
    Content status
    Language
    Document version

    HTH

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    timsr1,

    Ok, see if this is what you want.
    usagelog.JPG

    If so you need this code in the ThisWorkbook Module of every workbook you want to log. Remember you must save those workbooks as "Macro Enables", e.g. .xlsm or xlsb:
    Code:
    Option Explicit
    
    Private Sub Workbook_Open()
    
       Dim wkbLog  As Workbook
       Dim wkbCur  As Workbook
       Dim vOpened As Variant
       
       vOpened = Now()   '*** Capture Time Opened
       Set wkbCur = ActiveWorkbook
       Set wkbLog = Workbooks.Open("G:\BEKDocs\Excel\Test\ExcelWorkbookUsageLog.xlsx")
       
       With Cells(Rows.Count, 1).End(xlUp)
           .Offset(1, 0).Value = wkbCur.Name
           .Offset(1, 1).Value = Now()
           .Offset(1, 3).Value = Environ("UserName")
       End With 'Cells...
       
       wkbLog.Save
       wkbLog.Close
       Set wkbLog = Nothing
       
    End Sub
    
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
       Dim wkbLog  As Workbook
       Dim wkbCur  As Workbook
       Dim vOpened As Variant
       
       Application.EnableEvents = False
       
       vOpened = Now()   '*** Capture Time Closed
       Set wkbCur = ActiveWorkbook
       Set wkbLog = Workbooks.Open("G:\BEKDocs\Excel\Test\ExcelWorkbookUsageLog.xlsx")
       
       Cells(Rows.Count, 1).End(xlUp).Offset(0, 2).Value = Now()
       
       wkbLog.Save
       wkbLog.Close
       Set wkbLog = Nothing
       
       Application.EnableEvents = True
       
    End Sub
    Note: The code is tied to the layout of the ExcelWorkbookUsageLog.xlsx workbook shown above.

    Test Files: LogWorkbookNo2.zip

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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