[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Track who opened Excel Sheet

Posted on 2009-04-01
4
Medium Priority
?
658 Views
Last Modified: 2012-08-14
Hello everyone;

We have a shared drive on our network and we make a lot of daily reports, I would like to know who opened the reports and the time. I made a search on Google and I came to these steps but since i'm not more than just a beginner whe it came to Excel or Access  I need someone to explain it step by step.

here is the steps:

If your Excel Sheets are stored on a shared location and you also want to tracked who opened that Excel and Saved it then use the following steps.

1. Create a Sheet named "Log" with the following format.

Timestamp      Username

2. Copy the following code in the Workbook_Open Event  ......... (where can I find that ?)

Private Sub Workbook_Open()
Worksheets("Log").Range("A65536").End(xlUp).Offset(1, 0).Value _
    = Format(Now(), "mm-dd-yy HH MM AMPM")
Worksheets("Log").Range("B65536").End(xlUp).Offset(1, 0).Value _
    = Environ("UserName")
End Sub

3. Next whenever anyone opens the Excel Sheet his name will be stored in the Sheet "Log". You can hide this sheet so that others cannot see and modify it.  ...............  (where to save this log file)
0
Comment
Question by:amlife180
  • 2
4 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 1000 total points
ID: 24038447
For step 2 you can right-click the Excel icon just to the left of the File menu and choose View Code. This will open the VB Editor at the ThisWorkbook module, which is where your code needs to be.
You would also need to add a line:
ThisWorkbook.Save
before the End Sub line of your code, since otherwise the user can simply close the workbook without saving and you won't have a record of it.
You should also be aware that if the user has macros disabled, this will not work.
Regards,
Rory
 
0
 
LVL 48

Assisted Solution

by:Wayne Taylor (webtubbs)
Wayne Taylor (webtubbs) earned 1000 total points
ID: 24038455
I assume you've already added the new worksheet named "Log". The code above should be pasted into the ThisWorkbook module.

Open up the Visual Basic Editor (VBE) by pressing Alt+F11. In the Project Explorer (normally a window on the far left), double click "ThisWorkbook" and paste your code there. Then close the VBE, save you workbook, then close. When you re-open the workbook, you will see your windows login name and time have been entered into the 'Log' worksheet.

Wayne
0
 

Author Comment

by:amlife180
ID: 24038786
Thanks very much for the input, dose any one know if there is any other way we can know who opened it. not necessary to know all users but al least who opened it last ?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24038876
Not if they don't save it.
You can probably do it using File auditing tools on the server, but there's nothing in Excel.
Regars,
Rory
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question