How do I track who uses my Excel spreadsheet?

Question

I created an Excel spreadsheet that my boss wants to put on the company's internal website. The spreadsheet contains some seldom-used, esoteric, but handy functions that only certain employees within the company will find really useful.

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

He asked that I password-protect the Excel spreadsheet in such a way that one password does NOT unlock all of the copies that people can download from the site. For example, I can't just make the password "stackoverflow" because once a user legitimately gets the password from me, and is shared with other people, it can be used by anyone within the company to unlock all subsequently downloaded spreadsheets. I will never be able to ascertain who is using the spreadsheet. Also, I cannot modify the website, so I hope to achieve this tracking of users through Excel and email.

Is there a way to have Excel randomly generate a string, which the user emails me, and then I respond with the appropriate password that will unlock the file (based off the generated string)? This requires the user to check in with me before using the spreadsheet (the ideal situation).

Is such an arrangement possible in Excel 2010 Professional Plus?

Answer 1

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

He asked that I password-protect the Excel spreadsheet in such a way that one password does NOT unlock all of the copies that people can download from the site.

I can't imagine how this might be possible using only Excel. Maybe an Add-in could do this, but at the file level, I don't think it could be done, at least not easily.

I will never be able to ascertain who is using the spreadsheet.

It sounds like this is the really important bit. You are not using the password as a security measure, only as a gatekeeping method to determine who is using the file. This can be automated in other ways, easiest of which would be to use certain Environment variables, e.g.:

MsgBox Environ("username") will display a message box with the current user's name.

You can assign Environ("username") to a string variable, and then you could for example automate Outlook to send you an email that "John Doe has opened the file", or something to that effect. If you want to avoid getting an email every time, you could do some tweaking with a Named Range variable in the Excel file, so that the macro will only send the email once, etc.

Alternatively, you may be able to write a log/txt file to a shared network location (of course, assuming the user is connected to the network) instead of sending emails.

Update

Here is some example code that I've taken from places around the web, it will send an email from the user. You will have to modify the sendTo lines to use your email address as recipient, etc.

Put this in the Workbook's code module, it should email you any time they 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

Expanding on David's answer you could also use a macro that auto-runs when the sheet is opened and it could write Environ("username") to the next available row in a hidden worksheet. I've used Environ("username") before and it is quite useful, quick, and easy.

This sub on any worksheet will automatically run (IIRC):

Private Sub Auto_Open()

End Sub

You could also put a time stamp in the next column to show when the spreadsheet was used...