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:
In this example, the result will be in the form of text file. The notepad will contain date, time & other details; somewhat like this:
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
- Click on Insert then Module
- 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
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
- 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:
The macro will not overwrite data.
Conclusion: The macro will run every time the workbook gets open at specified path & folder.
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 email@example.com