Creating A Usage Log

Category: General VBA | [Item URL]

This tip describes how to create a usage log that keeps track who opens a workbook, and at what time.

If a workbook's ThisWorkbook code module contains a procedure named Workbook_Open, that procedure is executed whenever the workbook is opened. Add the following procedure to the ThisWorkbook code module.

Private Sub Workbook_Open()
    Open ThisWorkbook.Path & "\usage.log" For Append As #1
    Print #1, Application.UserName, Now
    Close #1
End Sub

When this procedure is executed, it appends the user name and the current date/time to a text file. The file is stored in the workbook's directory, and is named usage.log. If the text file does not exist, it is created. You can, of course, change the code so the text file is written to a different directory.

NOTE: Keep in mind that this procedure is not executed if the workbook is opened with macros disabled.

Search for Tips

All Tips

Browse Tips by Category

Tip Books

Needs tips? Here are two books, with nothing but tips:

Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks

Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers

© Copyright 2019, J-Walk & Associates, Inc.
Privacy Policy