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


6 thoughts on “Log files using VBA in Microsoft Excel

  1. 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.

    • 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”

Leave a Reply

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


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>