How to Auto Log the Time When You Open and Close an Excel File

Knowing when you have edited or viewed Excel file is important in your work. In this article, we will show you how to automatically log time when you open or close the Excel file.

By logging the time of opening an Excel file and closing a file, you will know at what time you have modified the file or viewed the file. With those records, you can know your work process. In addition, keeping track of files can also help you prevent your files from being changed by other peoples, deliberately or unintentionally. The following part will show you how to finish this task.

Log the Time

1. Press the shortcut keys “Alt +F11” on the keyboard. Thus, you will open the Visual Basic editor.
2. And then double click the “ThisWorkbook” in the VBA project.
3. Now input the following codes into the window:

Private Sub Workbook_Open()
  Dim nLastUsedRowIndex As Integer
  
  nLastUsedRowIndex = Application.WorksheetFunction.CountA(Worksheets("Access Time").Range("A:A")) + 1
  Range("A" & nLastUsedRowIndex) = Now
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  Dim nLastUsedRowIndex As Integer
  
  nLastUsedRowIndex = Application.WorksheetFunction.CountA(Worksheets("Access Time").Range("B:B")) + 1
  Range("B" & nLastUsedRowIndex) = Now

  'Save the file and then exit the application
  ActiveWorkbook.Save
  Application.Quit
End Sub

When you close the file, there will be new content in the worksheet. In the second macro, when you close the file, Excel will save the new content automatically. Here we will input the time into column A and column B in the worksheet named “Access Time”. You can also modify them according to your need.

4. After that, save this file as a macro-enabled file in your computer.
5. In this step, close the file. The moment you close the file, the time will appear in column B.
6. Next open the file again, and you will see two cells with time.
7. And then delete the time in cell B2. Therefore, the next time you close the file, the time for opening and the time for closing will be in corresponding with each other.

From the above steps, you can see that this task is very easy to finish. When you open the file and close it the next time, there will be new time in two cells.

Do the Right Thing for Your Excel Files

It is unavoidable that you will suffer from Excel corruption. Some of them can be fixed by restart this application. However, more severe conditions cannot be handled so easily. In a data disaster, you need to do the right things for those damaged files. One of the right things is using a third-party tool. This tool can repair corrupted xls and solve other problems in Excel. Therefore, you don’t need to worry about those data disasters.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Word doc file damage and outlook repair software products. For more information visit www.datanumen.com