How to track who opens excel workbook

FLdave12

Board Regular
Joined
Feb 4, 2022
Messages
81
Platform
  1. Windows
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
 
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.
 
Upvote 0
Paste the following into a sheet whose tab name is 'Log'. This sheet may be hidden.

VBA Code:
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.
 
Upvote 0
Paste the following into a sheet whose tab name is 'Log'. This sheet may be hidden.

VBA Code:
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
 
Upvote 0
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
What part of those macros '"don't work" ?
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,918
Members
453,766
Latest member
Gskier

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top