When was the file opened last time

[Overkill32]

Is there a way (function or macro) to get the time or date that a specific file was opened (not saved)?

Thanks for the help

 

[Rick Rothstein]

You are going to have to decide where you want the date/time stored at, but you can use the workbook's Open event to store the results of the VB Now function. For example, if you chose to store it in A1 on Sheet1, the event code would look like this...

Private Sub Workbook_Open()
  Worksheets("Sheet1").Range("A1").Value = Now
End Sub

You would store this code in the ThisWorkbook module (double-click that item in the Project window in the VB editor and put the code in the code window that opens up).

 

[Overkill32]

The only way i could see this working is to use the Now function only when the workbook close so the range() that the value is assigned for will display the last time opened... Is there a private sub workbook_close() that exist??

 

[Rick Rothstein]

In that case, store the Now value in a global variable during the workbook Open event and write that value out to the cell in the Close event. To create a global variable, Dim the variable at the top of the sheet module (this Dim statement stands on it own... do not put it inside any procedures). So, you would have something like this in the sheet module...

Dim ThisWorkbooksOpenDateTime As Date
 
Private Sub Workbook_Open()
  ThisWorkbooksOpenDateTime = Now
End Sub
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
  Worksheets("Sheet1").Range("A1").Value = ThisWorkbooksOpenDateTime
End Sub