How to track who uses an Excel spreadsheet?

Question

I created an Excel spreadsheet that my boss wants to host on the company's internal website. The spreadsheet contains some rarely used, esoteric, but convenient features that may only be useful to some employees within the company.

The problem is that I don’t know who the future users are, and my boss wants me to determine who uses my spreadsheet.

He asked me to protect the password of the Excel spreadsheet so that one password does NOT unlock all copies that people can download from the site. For example, I can’t just make the password “stackoverflow”, because as soon as the user legally receives the password from me and shares it with other people, it can be used by anyone inside the company to unlock all subsequently downloaded spreadsheets. I can never figure out who uses the spreadsheet. In addition, I can’t change the site, so I hope to achieve this user tracking through Excel and email.

Is there a way to get Excel to randomly generate a line that the user sends me an email and then I respond with the appropriate password that will unlock the file (based on the generated line)? This requires the user to register with me before using the spreadsheet (ideal situation).

Is this placement possible in Excel 2010 Professional Plus?

Answer 1

I think that password protection in the method you describe is unnecessarily cumbersome, even if it is possible at all.

He asked me to protect the password of the Excel spreadsheet so that one password does NOT unlock all copies that people can download from the site.

I can not imagine how this is possible using only Excel. Perhaps the add-in can do this, but at the file level I don’t think it can be done, at least not so easily.

I can never figure out who uses the spreadsheet.

This seems like a really important bit. You do not use a password as a security measure, only as a data verification method to determine who is using the file. This can be automated in other ways, the easiest of which is to use certain Environment variables, for example:

MsgBox Environ("username") displays a message box with the current username.

You can assign Environ("username") string variable, and then you can, for example, automate Outlook to send you an email that "John Doe opened the file", or something like that. If you want to avoid receiving emails every time, you can make some adjustments using the Named Range variable in the Excel file, so that the macro will send messages only once, etc.

Alternatively, you can write the / txt log file to a shared network folder (of course, provided that the user is connected to the network) instead of sending emails.

Update

Here is an example of the code that I took from places on the Internet, it will send an email from the user. You will need to change the sendTo lines to use your email address as a recipient, etc.

Put this in the workbook code module, it should send you an email every time you open this file:

 Option Explicit Private Sub Workbook_Open() ' This example uses late-binding instead of requiring an add'l reference to the ' MS Outlook 14.0 Object Library. Dim oApp As Object 'Outlook.Application 'Object Dim ns As Object 'Namespace Dim fldr As Object 'MAPIFolder Dim mItem As Object 'Outlook.MailItem Dim sendTo As Object 'Outlook.Recipient Dim bOutlookFound As Boolean On Error Resume Next Set oApp = GetObject(, "Outlook.Application") bOutlookFound = Err.Number = 0 On Error GoTo 0 If Not bOutlookFound Then Set oApp = CreateObject("Outlook.Application") 'New Outlook.Application '# Set the namespace and folder so you can add recipients Set ns = oApp.GetNamespace("MAPI") Set fldr = ns.GetDefaultFolder(6) 'olFolderInbox '# create an outlook MailItem: Set mItem = oApp.CreateItem(0) 'olMailItem '# assign a recipient Set sendTo = mItem.Recipients.Add(" [e-mail] ") sendTo.Type = 1 'To olTo '# assign another recipient Set sendTo = mItem.Recipients.Add(" [e-mail] ") sendTo.Type = 1 '# Validate the recipients (not necessary if you qualify valid email addresses: For Each sendTo In mItem.Recipients sendTo.Resolve Next mItem.Subject = "A user has opened the Excel file" mItem.Body = "This is an automated message to inform you that " & _ Environ("username") & " has downloaded and is using the file." mItem.Save mItem.Send 'If outlook was not already open, then quit If Not bOutlookFound Then oApp.Quit Set oApp = Nothing End Sub 

Answer 2

Turning around David’s answer, you can also use a macro that automatically runs when a sheet is opened, and it can write Environ ("username") to the next available line on a hidden sheet. I have used Environ ("username") before, and it is very useful, fast and easy.

This section on any worksheet will automatically start (IIRC):

 Private Sub Auto_Open() End Sub 

You can also put the timestamp in the next column to show when the spreadsheet was used ...