Log files using VBA in Microsoft Excel

In computers, a log file is a file that records either events that occur in an operating system or other software runs, or messages between different users of a communication software. Logging is the act of keeping a log. In the simplest case, messages are written to a single log file.

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

Log files are useful in different situations, especially 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. In this article we will focus on creating automatic log files in a specific folder.


Question: How can I create a notepad file which contains the current date, time & User name whenever the file gets open?

Following is the snapshot of Excel workbook containing financial information:

image 1


In this example, the result will be in the form of text file. The notepad will contain date, time & other details; somewhat like this:


image 2


To create log files, we need to follow the below steps to launch VB editor:

  • Click on Developer tab
  • From Code group select Visual Basic

image 3


  • Click on Insert then Module

image 4


  • This will create new module
  • Enter the following code in the Module


Sub LogInformation(LogMessage As String)

Const LogFileName As String = "D:\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


Public Sub DisplayLastLogInformation()

Const LogFileName As String = "D:\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


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


image 5


Copy the following code in the ThisWorkbook module


Private Sub Workbook_Open()

LogInformation ThisWorkbook.Name & " opened by " & _

Application.UserName & " " & Format(Now, "yyyy-mm-dd hh:mm")

End Sub


image 6


  • Now the VBA code is all set to go; next time when we open the excel workbook the date & time will be saved in notepad; refer below image:

image 7


The macro will not overwrite data.


Conclusion: The macro will run every time the workbook gets open at specified path & folder.


If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com



  1. I've tried and I can't seem to get it to work :(. I've been also trying to get this code to print the file to a network path and can't get it to work either :(. Is there a way I can have the file in one location and get the log to print somewhere as on the network?
    Private Sub Workbook_Open()
    Open This Workbook.Path & “\Assistance.log” For Append As #1
    Print #1 Application.UserName, Now
    Close #1
    End Sub

    Much help is needed and would be greatly appreciated

  2. I have log file I need to create a macro for reading logs and display them in Excel in different sheets can someone assist me with sample codes

  3. This is all very good. Is there a possibility to somehow modify the file in order to be able to add a line if a workbook was changed (do not track cell changes)?

  4. I had to change the constant to a dim string, but othet than that your example worked wonders. I hapve adapted to use the app.filepath in a couple of w/books and its awesome. Thanks.

    • Substitute a valid drive letter and folder name referencing the computer where the file will be opened from or where the file resides Change each designations in both Macro's that reference "D:\FOLDERNAME\TEXTFILE.DOC" . Example: "C:\dinesh\textfile.doc"

  5. Great Just what I needed!

    Format(Date, "yyyy-mm-dd hh:mm")
    2014-05-28 00:00
    should be
    Format(Now(), "yyyy-mm-dd hh:mm")
    to capture seconds
    2014-05-28 13:55

    BTW : your capture is working it was showing
    -1 = seven on right of answer field! non sense. Had to refresh page and get the captcha on left of answer field.

Leave a Reply

Your email address will not be published. Required fields are marked *

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.