How to create a LOG file in excel and access - VBA

Welcome to Logically Proven blog.

This post demonstrates how to create a log file in excel or access using VBA (Visual Basic for Applications) code.

Consider a scenario, the application is logging the user details into the log file who are accessing the application.

Here is application may be excel or access which is located in the network location which is accessed by the multiple users.

The log data contains the application name, who are accessing the application and the application accessed date.

In the below example I am considering the excel application which logs the data into the LOG file. The application appends the data to the LOG file every time the application is accessed.

Achieving this functionality by logging the user details when the workbook is opened.

So,  the workbook open event code looks like this -

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

LogInfo is the sub-routine which saves the application name, user name and the access date of an application.

LogInfo sub-routine :

Sub LogInfo(LogMessage As String)

    'set path and name of the log file where you want to save
    'the log file
    Const LogFileName As String = "D:\LogData\LogFile.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

If the log file doesn't exist, the application creates automatically at the run-time. But the path should be valid and exists otherwise the application runs into error. And make sure the application is having the permissions to create and write into the log file.

This method takes one argument which is the message that you want to write into the log file.
This method appends the log message every time the application is accessed.

This way the application logs the user details who are accessing the application. You can add the same functionality in many cases for example when user modifies something in the excel or access database you can trigger the code to save the user details and what modifications did.

LOG file is very useful to analyse the application. So keep logging your application.


Please write your comments if you find anything is incorrect or do you want to share more information about the topic discussed above.


Logically Proven
Learn, Teach, Share

Karthik Byggari

Author & Editor

Computer Science graduate, Techie, Founder of logicallyproven, Love to Share and Read About pprogramming related things.