Log files

Log files are useful in different situations, specially for developers. Log files are plain text files that can store information temporary or more permanent. You don't need much code to create a log file:

Sub LogInformation(LogMessage As String)
Const LogFileName As String = "C:\FOLDERNAME\TEXTFILE.LOG"
Dim FileNum As Integer
    FileNum = FreeFile ' next file number
    Open LogFileName For Append As #FileNum ' creates the file if it doesn't exist
    Print #FileNum, LogMessage ' write information at the end of the text file
    Close #FileNum ' close the file
End Sub

The macro above can be used from other macros like this:

Private Sub Workbook_Open()
    LogInformation ThisWorkbook.Name & " opened by " & _ 
        Application.UserName & " " & Format(Now, "yyyy-mm-dd hh:mm")
End Sub

Log files created in this way can be read by all applications capable of reading plain text files, e.g. Notepad, Write, Word and Excel. It is also possible to read log file contents with VBA, here is an example:

Public Sub DisplayLastLogInformation()
Const LogFileName As String = "C:\FOLDERNAME\TEXTFILE.LOG"
Dim FileNum As Integer, tLine As String
    FileNum = FreeFile ' next file number
    Open LogFileName For Input Access Read Shared As #f ' open the file for reading
    Do While Not EOF(FileNum)
        Line Input #FileNum, tLine ' read a line from the text file
    Loop ' until the last line is read
    Close #FileNum ' close the file
    MsgBox tLine, vbInformation, "Last log information:"
End Sub

Log files can grow large in size, especially if you log much information or log often. You might want to delete large log files. This can be done manually from Windows Explorer, automatically at a given time (e.g. using an AT-command in Windows NT or another suitable tool) or with a macro like this:

Sub DeleteLogFile(FullFileName As String)
    On Error Resume Next ' ignore possible errors
    Kill FullFileName ' delete the file if it exists and it is possible
    On Error Goto 0 ' break on errors
End Sub

The macro above can be used like this from another macro:

DeleteLogFile "C:\FOLDERNAME\TEXTFILE.LOG"

Document last updated 2000-11-02 12:48:50 Printerfriendly version

User comments:

Ole P. from Norway wrote (2004-08-12 13:51:23 CET):

Re: Use Now instead of Date
You are correct, Date will always return the time at midnight (00:00).
The example is updated...

Martin Los from Spain - Madrid wrote (2004-08-12 12:56:34 CET):

Use Now instead of Format(Date, "yyyy-mm-dd hh:mm")?
Using Format(Date, "yyyy-mm-dd hh:mm") I only get the date correct, but the time part always will appear as 00:00.

If you use "Now" instead of "Format(Date, "yyyy-mm-dd hh:mm")", you will get both date and current time.