|
|||||||||
|
|||||||||
|
||||||||||||||||||||||||||||
» Log files using VBA in Microsoft Excel
 | ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
|
Log files are useful in different situations, specially 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:
Sub LogInformation(LogMessage As String)
Const LogFileName As String = "C:\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 SubThe macro above can be used from other macros like this:
Private Sub Workbook_Open()
LogInformation ThisWorkbook.Name & " opened by " & _
Application.UserName & " " & Format(Date, "yyyy-mm-dd hh:mm")
End Sub
Log files created in this way can be read by all applications capable of reading plain text files,e.g. Notepad, Write, Word and Excel. It is also possible to read log file contents with VBA, here is an example:
Public Sub DisplayLastLogInformation()
Const LogFileName As String = "C:\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 SubLog files can grow large in size, especially if you log much information or log often. You might want to delete large log files. This can be done manually from Windows Explorer, automatically at a given time (e.g. using an AT-command in Windows NT or another suitable tool) or with a macro like this:
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 SubThe macro above can be used like this from another macro: DeleteLogFile "C:\FOLDERNAME\TEXTFILE.LOG" | ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
|
READER COMMENTS (view all comments) |
||||||||||||||||
|
No comments have been submitted. | ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
 | ||||||||||||||||
Investing for Dummies, Third Edition
Understanding Financial Statements
Microsoft Outlook 2002 for Dummies
Microsoft Office XP Introductory Concepts and Techniques
Getting to Yes: Negotiating Agreement Without Giving In
Special Edition Using Microsoft Excel 2002 | ||||||||||||||||
| ||||||||||||||||
RELATED MICROSOFT EXCEL TIPS
 |
• Excel VBA books
• Accounting books
• Business Plans
• MS Office books
• Taxes books
|
Terms
and Conditions of use |
|
|
Copyright © 2003 ExcelTip.com |
|