CODEWITHSUNDEEP
vbaexcel
Toast
Toast

Reputation: 530

Get Date/Time Workbook Was Opened

I've got some code that needs to check the date that a spreadsheet was opened. Not the date it was created, and not necessarily today's date--some users apparently leave the spreadsheet open for several days, and this code only runs when the workbook is printed.

Previously, I'd used this code:

Dim FileSystem as Object

Set FileSystem = CreateObject("Scripting.FileSystemObject")
Debug.Print FileSystem.getFile(ThisWorkbook.Path & "\" & ThisWorkbook.Name).DateCreated

That initially worked fine, but now I'm running into another problem. For some users it works fine--they are opening this spreadsheet through the version controlled server, which downloads a copy to a protected directory. In that case, the .DateCreated represents the date they pulled the copy from the server. But other users are opening the workbook by opening a link to the file on the server through Internet Explorer, which skips the protected directory. For those users the code doesn't work at all--opening the spreadsheet through the link and checking it's file path, I get the following:

debug.print ThisWorkbook.Path

Output: http://servername.com/serverfolder/

Unsurprisingly, FileSystem.getFile crashes when pointing it towards a web server instead of a filepath. I tried instead to use ThisWorkbook.BuiltinDocumentProperties("Creation Date"), but the problem there is that it's giving me the date the workbook was originally created--in this case, all the way back in 2005. Is there any simple way to get the date and time the workbook was opened?

Is it stored anywhere at all?

Upvotes: 0

Views: 1336

Answers (2)

AlwaysData
AlwaysData

Reputation: 555

Maybe try using now() in the Workbook_Open event to store the datetime that the workbook was opened in one of the worksheets. This does make the assumption that no one will change the value. 

Private Sub Workbook_Open()

    Worksheets("Sheet1").Range("A1").Value = Now()

End Sub

Upvotes: 1

Realitybites
Realitybites

Reputation: 443

Depending on the end result of this, you could do something as simple as:

entering =now() into a cell then running a macro to copy and paste as values.

Upvotes: 1

Related Questions