Solved

Track who opened Excel Sheet

Posted on 2009-04-01
4
648 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

762 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now