How to track who opens excel workbook

[FLdave12]

I have a workbook with master sheet and then individual sheets for days of the week that is a work schedule of post assignments. I need to be able to track which supervisor opens the file with date and time.

Is this possible and if so how.

Thanks

 

[ExcelToDAX]

Is this file saved in SharePoint or a Shared Drive? It would also be beneficial to update the version of Excel that you have so that we can better assist you.

 

[FLdave12]

It is on a Shares drive. Version is 365

 

[Logit]

Paste the following into a sheet whose tab name is 'Log'. This sheet may be hidden.

Private Sub Workbook_Open()
Dim LR As Long
With Sheets("Log")
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Range("A" & LR + 1).Value = "Opened"
    Range("B" & LR + 1).Value = Now
    Range("C" & LR + 1).Value = Environ("username")
End With
End Sub

Note: The macro produces the computer name as who opened the workbook. It does not provide the actual user name unless their computer is so named.

 

[FLdave12]

Paste the following into a sheet whose tab name is 'Log'. This sheet may be hidden.

Private Sub Workbook_Open()
Dim LR As Long
With Sheets("Log")
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Range("A" & LR + 1).Value = "Opened"
    Range("B" & LR + 1).Value = Now
    Range("C" & LR + 1).Value = Environ("username")
End With
End Sub

Note: The macro produces the computer name as who opened the workbook. It does not provide the actual user name unless their computer is so named.

Tried and not working, is there a way to show user name of person who opened file. thx

 

[Logit]

Note: The macro produces the computer name as who opened the workbook. It does not provide the actual user name unless their computer is so named.
There is no way to capture the computer user's name UNLESS his/her computer is named as such.

How To Rename A Computer : Nom de PC: How to Change the Name of Your Computer

Download workbook : Internxt Drive – Private & Secure Cloud Storage

 

[Edgar_]

You can retrieve a ton of information from the device where the workbook is being opened. You could get processor, bios, OS, version of programs, etc., using WMI. There are 1248 classes where you can retrieve meaningful data about the device, and each class has a bunch of stuff to retrieve data from. In fact, many programs utilize that information to create a "device fingerprint" that should let you know exactly from which device the file was open.

But if you want meaningful names, you can have a login form and check against that.

I would also recommend not writing to the same workbook. I would write to another workbook or an external file. In fact, you could log several types of interactions that you define.

 

[Logit]

Here is a small project recommended by Edgar. It has the basics but there is the opportunity to include some error checking ... give the user three chances to log in then close the workbook.
You'll want to hide the INPUT and DATA sheets from the users.

All in all ... something to get your grey matter working.

Workbook download : Internxt Drive – Private & Secure Cloud Storage

 

[FLdave12]

Is there a way to track who opened an excel file?
I have tried this and it does not work:

Appreciate any help

Sheet called Log with
Open-Close Time Date User

Marco code:
Option Explicit
Private strOpenClose As String
Private strDate As String
Private strTime As String
Private strUser As String
Private strValues As String
Private Const strlogfile As String = "C:\Path\Log.xlsx"

Private Sub Workbook_Open()
strOpenClose = "Opened"
strDate = Format(Date, "dd.mm.yyyy") 'set format to taste
strTime = Format(Time, "HH:MM:SS")
strUser = Environ("username")
strValues = strOpenClose & "', '" & strDate & "', '" & strTime & "', '" & strUser
WriteToWorksheet strWorkbook:=strlogfile, strRange:="Sheet1", strValues:=strValues
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
strOpenClose = "Closed"
strDate = Format(Date, "dd.mm.yyyy")
strTime = Format(Time, "HH:MM:SS")
strUser = Environ("username")
strValues = strOpenClose & "', '" & strDate & "', '" & strTime & "', '" & strUser
WriteToWorksheet strWorkbook:=strlogfile, strRange:="Sheet1", strValues:=strValues
End Sub

Private Function WriteToWorksheet(strWorkbook As String, _
strRange As String, _
strValues As String)
Dim ConnectionString As String
Dim strSQL As String
Dim CN As Object
strRange = strRange & "$]"
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strWorkbook & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES;"";"
strSQL = "INSERT INTO [Sheet1$] VALUES('" & strValues & "')"
Set CN = CreateObject("ADODB.Connection")
Call CN.Open(ConnectionString)
Call CN.Execute(strSQL, , 1 Or 128)
CN.Close
Set CN = Nothing
End Function

 

[Logit]

What part of those macros '"don't work" ?