Solved

Track who opened Excel Sheet

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

867 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

13 Experts available now in Live!

Get 1:1 Help Now