Microsoft Excel - Generate Logfile of workbook

Hi All,

I have workbook, i need  to generate log file,at what time the workbook is open and close and i have few button in the workbook, also need that when a user click on any button like  if button name is ( BT-1 ) is clicked it write on the logfile that (BT-1) is clicked on this date time ?

Thanks
farrukh

I have a WriteAuditRecord procedure when this is required.  It can be used to write entries to an Audit worksheet, or to a .txt file on the network.  This sample writes it to a text file:

Public Sub WriteAuditRecord(strComment As String)

'MUST set reference to Windows Script Host Object Model in the project to use this code!

    Dim objFS         As FileSystemObject
    Dim objFile       As File

    Dim datCurrent      As Date
    Dim strRecord       As String
    Dim strFileName     As String

    strRecord = ThisWorkbook.FullName & " " _
      & Format(Now(), "yyyy-mm-dd HH:MM:SS AMPM") & ", " & UserName() & ": " _
      & strComment
    strFileName = ThisWorkbook.Path & "\J" & Format(Day(Now()), "00") & ".txt"
   
    Open strFileName For Append As #1
    Write #1, strRecord
    Close #1

    Set objFS = New FileSystemObject
    Set objFile = objFS.GetFile(strFileName)
    objFile.Attributes = Hidden

Proc_Exit:

    Set objFile = Nothing
    Set objFS = Nothing

End Sub

in a separate module:
Declare Function GetUserName Lib "advapi32.dll" _
    Alias "GetUserNameA" _
    (ByVal lpBuffer As String, nSize As Long) As Long

Function UserName() As String
On Error GoTo Proc_Error

    strUserName = Space(255)
    If GetUserName(strUserName, Len(strUserName) + 1) Then
      strUserName = Trim$(strUserName)
      strUserName = Left(strUserName, Len(strUserName) - 1)
      UserName = strUserName
    Else
      UserName = "ErrorName"
    End If

Proc_Exit:
    Exit Function

Proc_Error:

    Select Case Err
      Case Else
        MsgBox "Error " & CStr(Err) & ": " & Err.Description
        Resume Proc_Exit
    End Select

    Exit Function

End Function

You will need to set a reference to the "Windows Script Host Object Model" to use the FileSystemObjects.  In this sample, it creates a new file based on the day of entry, but you can name a specific file as well.

Just call this from the Workbook_Open, Workbook_Close and button_click events with the appropriate comment.  You've posted enough here I'm sure you know how to put this in...

Hi Farrukh,

Try this one, It looks so simple, I am not sure if it works....
I design to locate the log information in sheet "Log", you can hide it later if you want...
I place sample button in sheet SampleOject, there when you click the button, will be log in sheet log.

Here is sample file -----> Vba_TimeStampLog.zip

------------------- Place these code in Module, there are 4 codes here -----------------------

Sub Auto_open()

Sheets("Log").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = "FileOpen"
Sheets("Log").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = Now()
ActiveWorkbook.Save

End Sub
    ------------------------------------
Sub Auto_Close()

Sheets("Log").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = "FileClose"
Sheets("Log").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = Now()
ActiveWorkbook.Save

End Sub

    ---------------------------------------
Sub LogCommand1()
Sheets("Log").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = "CommandButton1_Click"
Sheets("Log").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = Now()
ActiveWorkbook.Save
End Sub

    ---------------------------------------
Sub LogCommand2()
Sheets("Log").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = "CommandButton2_Click"
Sheets("Log").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = Now()
ActiveWorkbook.Save
End Sub
-----------------------------------------------------------------------------------

And this even procedure in the control button

Private Sub CommandButton1_Click()
Call LogCommand1
End Sub

    ----------------------------------
Private Sub CommandButton2_Click()
Call LogCommand2
End Sub
    ----------------------------------

Pichart Y.

hi



refer link
http://www.excelforum.com/excel-programming/665924-create-a-log-file-of-who-has-opened-spreadsheet-and-when.html#post2600551

wally eye , pichart Y and smr,


Thank you all all methods works great.




Thank you