Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Sep 2014
    Posts
    3
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Unhappy Add word timestamp in Excel

    Hi,

    When ever i open a word document, I want to record the time stamp of when it was opened and what is the name of the document. And i want to store this result in an Excel sheet and i also want to know when it is closed(not mandatory, but would be helpful in getting it).

    Can anyone please write a macro to track this. I'm really new to this.

    But i found one thing, if i edit the .docm /.dot file of word, the data is directly reflected once the document is opened, i want the macro to here.

    This is for a tracking of our work purpose.

    please provide me a macro that saves these details in an Excel sheet. it should contain. Name of the document opened, when is it opened, when is it closed.

    I'm really sorry to ask you guys to provide me a macro, but I'm left with no option.


    Thanks,
    Sunny

  2. #2
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    Borrowing heavily from Guy Thomas (http://www.computerperformance.co.uk...entextfile.htm) I came up with this code to get you started.

    The basic workflow is to store the time value as you open the document and then write all the requested information to a csv file as the document is closed. There is no need to complicate it by writing twice for each activity (unless you crash Word while you have the file open). CSV files open in Excel by default.

    Code:
    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
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  3. The Following 2 Users Say Thank You to Andrew Lockton For This Useful Post:

    Charles Kenyon (2015-09-04),sunnykeerthi (2014-09-10)

  4. #3
    New Lounger
    Join Date
    Sep 2014
    Posts
    3
    Thanks
    4
    Thanked 0 Times in 0 Posts
    You are really awesome Andrew, this is working like what i needed. I really want to thank you.

    Thanks you very much. Can't imagine my happiness now.

    Here I've another question, if i go to task manager and close the document, is there a way that i get get the time recorded even then?
    Thanks again
    Last edited by sunnykeerthi; 2014-09-10 at 11:20.

  5. #4
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    Sunny

    Exiting via the task manager will still be captured (I think). Certainly, it worked when I tested it but your mileage may vary. Other (less planned) Word or system crashes might be more problematic.

    Other issues you will discover will be dealing with unsaved documents - filename doesn't exist if doc isn't yet saved; opening writes a change to the file and therefore the user gets nagged to save even though they didn't make any overt changes. Each of these can be dealt with by writing progressively more intricate code as each of the 'edge cases' can be identified successfully.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  6. The Following User Says Thank You to Andrew Lockton For This Useful Post:

    sunnykeerthi (2014-09-11)

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

    Excel Document macro

    Quote Originally Posted by Andrew Lockton View Post
    Borrowing heavily from Guy Thomas (http://www.computerperformance.co.uk...entextfile.htm) I came up with this code to get you started.

    The basic workflow is to store the time value as you open the document and then write all the requested information to a csv file as the document is closed. There is no need to complicate it by writing twice for each activity (unless you crash Word while you have the file open). CSV files open in Excel by default.

    Code:
    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



    I would like to do the same thing for tracking excel documents that have been opened and closed. Is this possible?

  8. #6
    Silver Lounger Charles Kenyon's Avatar
    Join Date
    Jan 2001
    Location
    Sun Prairie, Wisconsin, Wisconsin, USA
    Posts
    2,048
    Thanks
    124
    Thanked 119 Times in 116 Posts
    Quote Originally Posted by timsr1 View Post
    I would like to do the same thing for tracking excel documents that have been opened and closed. Is this possible?
    I'm sure it is but this is the Word processing forum drawing on experienced Word users. Andrew or someone else may have an answer here.

    If not, I would recommend you ask in the Spreadsheet forum or the VBA forum, perhaps with a link back to this thread. If you do that, please also put a link to the new question here.
    Last edited by Charles Kenyon; 2015-09-04 at 09:13.
    Charles Kyle Kenyon
    Madison, Wisconsin

Tags for this Thread

Posting Permissions

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