Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Track who opened Excel Sheet

Posted on 2009-04-01
4
Medium Priority
?
657 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

610 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