Log files using VBA in Microsoft Excel

» Log files using VBA in Microsoft Excel

VBA macro tip contributed by Erlandsen Data Consulting* offering Microsoft Excel Application development, template customization, support and training solutions*

CATEGORY: Files, Workbook, and Worksheets in VBA	VERSIONS: All Microsoft Excel Versions

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:

DeleteLogFile "C:\FOLDERNAME\TEXTFILE.LOG"

Rate this tip



1	2	3	4	5

RATING: 3.74
VIEWS: 69399

Email Tip

Print Tip

Add to My Tips

No comments have been submitted.

Sponsors:

Book Store:

VIEW ALL BOOKS

Recommended Books:

Related MS EXCEL TIPS:

Terms and Conditions of use
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.