Solved

Track who opened Excel Sheet

Posted on 2009-04-01
4
651 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 250 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 47

Assisted Solution

by:Wayne Taylor (webtubbs)
Wayne Taylor (webtubbs) earned 250 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

776 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