Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Last user and file open date?

Posted on 2011-04-20
8
Medium Priority
?
323 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,
0
Comment
Question by:nike_golf
  • 5
  • 3
8 Comments
 
LVL 13

Author Comment

by:nike_golf
ID: 35432640
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
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35432641
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.
0
 
LVL 13

Author Comment

by:nike_golf
ID: 35432998
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,
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35433084
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.
0
 
LVL 13

Author Comment

by:nike_golf
ID: 35434929
I am looking for a more user level approach.

NG,
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35436002
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.
0
 
LVL 13

Accepted Solution

by:
nike_golf earned 0 total points
ID: 35437503
Got it working using the following from a master work book.

Set wb = Workbooks.Open ("C:\myworkbook.xls")
Wb.BuiltinDocumentProperties("Last Author")
Wb.BuiltinDocumentProperties("Last Save time")
0
 
LVL 13

Author Closing Comment

by:nike_golf
ID: 35465147
Self Answered
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

810 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