Log File

Record your log, audit trail, debugging data activity, in an external ASCII file.

This was one of the first project production utilities I wrote (circa 1994) and it's been in use ever since. I've also seen clones of it on other web-sites!

Example output

Opened: 23 Feb 05 at 08:32 pm
23 Feb 05 at 08:32 pm: Here are a series of demo entries
23 Feb 05 at 08:32 pm: Why not add this into your code while debugging
23 Feb 05 at 08:32 pm: Or use it in projects to audit user activity

The project consists of three routines:

  • BygLogMess, which records messages in the log file
  • BygLogPrint, which displays the log file
  • BygLog, which allows interactive log entries.

To use this in your work you would put BygLogMess within your work:

vba project code
BygLogMess "Here is a demo entry"
more vba project code

The Code

This is the main routine. It records the messages within a log file. If the file does not exist, it creates it.

'' ***************************************************************************
'' Purpose : Record your log, audit trail, debugging data activity, in an external ASCII file
'' : This is either called by "BygLog", or you can place it in your code with appropriate messages
'' : Use it to create audit trails in your applications
'' Written : 19/10/94 by Andy Wiggins - Byg Software Ltd
''
Sub BygLogMess(vtMess$)
Dim viFileNumber%
Dim vtFileLog$
Dim vtTS$
	'' The default path and file name
	vtFileLog = ThisWorkbook.Path & Application.PathSeparator & "PROJECT.LOG" 
	'' Get the date and time
	vtTS = Format(Date, "d mmm yy") & " at " & Format(Time, "hh:mm am/pm") 
	'' Ensure minimum length
	If Len(vtTS) < 21 Then vtTS = Application.Rept(" ", 21 - Len(vtTS)) & vtTS 

	viFileNumber = FreeFile()

	If Dir(vtFileLog) = "" Then 'If the file doesn't exist..
		Open vtFileLog For Append As #viFileNumber '..create and open it..
		Print #viFileNumber, "Opened: " & vtTS '..put in a header..
		Close #viFileNumber '..and close the file after use
	End If

	Open vtFileLog For Append As #viFileNumber 'Open the file for data
	Print #viFileNumber, vtTS & ": " & vtMess 'Enter the data
	Close #viFileNumber 'Close the file after use

End Sub

First, we set up the target file name. This can be changed as your project requires. By default, it creates the log in the same directory as your project.

     vtFileLog = ThisWorkbook.Path & Application.PathSeparator & "PROJECT.LOG"

Next, we generate a date and time stamp for the entry.

    vtTS = Format(Date, "d mmm yy") & " at " & Format(Time, "hh:mm am/pm")

This line ensures a certain amount of consistancy within the file's presentation.

    If Len(vtTS) < 21 Then vtTS = Application.Rept(" ", 21 - Len(vtTS)) & vtTS

Now we get the next file number which is used by the Open statement.

    viFileNumber = FreeFile()

This chunk of code tests whether the file exists. If it doesn't it creates it and adds a special message to record the event. It's not necessary and can be excluded if you don't need this functionality.

    If Dir(vtFileLog) = "" Then 'If the file doesn't exist..
        Open vtFileLog For Append As #viFileNumber '..create and open it..
        Print #viFileNumber, "Opened: " & vtTS '..put in a header..
        Close #viFileNumber '..and close the file after use
    End If

asdsa

If the file exists, the next three lines are used. They open the file, append the message and close the file.

    Open vtFileLog For Append As #viFileNumber 'Open the file for data
    #viFileNumber, vtTS & ": " & vtMess 'Enter the data
    Close #viFileNumber 'Close the file after use

 

The second function opens the file within Notepad. This can be attached to a button within your project (see the example file). It also contains sample code illustrating how you could view the file in your preferred editor.

'' ***************************************************************************
'' Purpose : View the results of the log file
'' Written : 19/10/94 by Andy Wiggins - Byg Software Ltd
''
Sub BygLogPrint()
Dim viRetVal As Double
Dim vtFileLog As String

vtFileLog = ThisWorkbook.Path & Application.PathSeparator & "PROJECT.LOG"

	If Dir(vtFileLog) = "" Then
		MsgBox "No file available", vbOKOnly, "LogPrint" 'Can't find the file
	Else
		'Use this line as a template enabling you to use your preferred editor
		'' viRetVal = Shell("C:\BYG2\PFE\PFE.EXE" & " " & vtFileLog, 3) 
		'' Using NOTEPAD in Windows
		viRetVal = Shell("NOTEPAD.EXE" & " " & vtFileLog, 3) 
	End If
End Sub

The file routine enables you to add random entries to the file. You could attach this to a button and record data whenever required.

'' ***************************************************************************
'' Purpose : Log memo, development and user activity in an external ASCII file
'' Written : 19/10/94 by Andy Wiggins - Byg Software Ltd
''
Sub BygLog()
Dim vtMess
	vtMess = Application.InputBox("Log Entry", "Byg Software")
	If vtMess <> False Then BygLogMess (vtMess)
End Sub

See also:

Published: 29-May-2005
Last edited: 01-Mar-2011 20:51