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(Date, "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: