vba 如何跟踪谁使用了我的 Excel 电子表格？

How do I track who uses my Excel spreadsheet?

提问by phan

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.

我创建了一个 Excel 电子表格，我的老板想把它放在公司的内部网站上。该电子表格包含一些很少使用、深奥但方便的功能，只有公司内的某些员工才会发现这些功能真正有用。

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.

他要求我对 Excel 电子表格进行密码保护，以确保一个密码不会解锁人们可以从该网站下载的所有副本。例如，我不能只将密码设置为“stackoverflow”，因为一旦用户合法地从我这里获得密码并与其他人共享，公司内的任何人都可以使用它来解锁所有随后下载的电子表格。我永远无法确定谁在使用电子表格。另外，我不能修改网站，所以我希望通过Excel和电子邮件来实现对用户的跟踪。

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).

有没有办法让 Excel 随机生成一个字符串，用户通过电子邮件发送给我，然后我用适当的密码响应以解锁文件（基于生成的字符串）？这需要用户在使用电子表格之前与我签到（理想情况）。

Is such an arrangement possible in Excel 2010 Professional Plus?

Excel 2010 Professional Plus 中是否可以进行这样的安排？

回答by David Zemens

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.

他要求我对 Excel 电子表格进行密码保护，以确保一个密码不会解锁人们可以从该网站下载的所有副本。

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.

我无法想象仅使用 Excel 怎么可能做到这一点。也许外接程序可以做到这一点，但在文件级别，我认为它无法做到，至少不容易。

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 whois using the file. This can be automated in other ways, easiest of which would be to use certain Environmentvariables, e.g.:

听起来这是非常重要的一点。您不是将密码用作安全措施，而是用作确定谁在使用文件的守门人方法。这可以通过其他方式自动化，其中最简单的是使用某些Environment变量，例如：

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

MsgBox Environ("username")将显示一个带有当前用户名的消息框。

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.

您可以分配Environ("username")给一个字符串变量，然后您可以例如自动化 Outlook 向您发送一封电子邮件，“John Doe 已打开文件”，或类似的内容。如果您想避免每次都收到电子邮件，您可以对 Excel 文件中的命名范围变量进行一些调整，以便宏只会发送一次电子邮件，等等。

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.

或者，您可以将 log/txt 文件写入共享网络位置（当然，假设用户已连接到网络）而不是发送电子邮件。

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 sendTolines to use your email address as recipient, etc.

这是我从网络上的一些地方获取的一些示例代码，它将发送来自用户的电子邮件。您必须修改sendTo行以使用您的电子邮件地址作为收件人等。

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("[email protected]")
        sendTo.Type = 1 'To olTo
    '# assign another recipient
    Set sendTo = mItem.Recipients.Add("[email protected]")
            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

回答by jerussell

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.

扩展大卫的答案，您还可以使用一个宏，该宏在打开工作表时自动运行，并且可以将 Environ("username") 写入隐藏工作表中的下一个可用行。我以前使用过 Environ("username")，它非常有用、快速且简单。

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

任何工作表上的这个子将自动运行（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...

您还可以在下一列中放置时间戳以显示电子表格的使用时间...