We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Last user and file open date?

nike_golf
nike_golf asked
on
Medium Priority
356 Views
Last Modified: 2013-11-05
Is it possible to get the last user that opened a workbook and when that date was not using a macro in the actual workbook?  However it would be OK to use another workbook and VBA to get the stats.

Can this be done via the API to get the file information?

Looking for some ideas and thoughts but they can't be directed at using the actual workbook and VBA to achieve this.

NG,
Comment
Watch Question

Author

Commented:
I had already seen this code posted but this involves running a macro in the actual workbook and won't work.


Private Sub Workbook_Open()

' Stores who and when on Sheet1, column A
Sheets("Sheet1").Select

If Range("A65536").End(xlUp).Row = 1 And Range("A65536").End(xlUp).Value = "" Then
Range("A65536").End(xlUp).Value = "Last opened by " & Environ("username") & " at " & Now
Exit Sub
End If

Range("A65536").End(xlUp).Offset(1, 0).Value = "Last opened by " & Environ("username") & " at " & Now

End Sub
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
You can use Windows security to audit file access. Unless the user changed and saved the workbook, the information will not be stored as a file property as far as I know.

Author

Commented:
How would you profess that would work?

The file that is to be accessed is on a network share so its not on a central computer. I think the information would have to be somehow tied directly to the file.

Thoughts?

NG,
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
The server can be set up to audit file access - I don't know the exact details offhand as it's a Windows thing not Excel and it's been many years since I was a network admin.

Author

Commented:
I am looking for a more user level approach.

NG,
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
The only alternative I can think of would be an addin on each machine that monitored each file opened checking for a particular name and then logged the access to it.
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
Self Answered
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.