Could anyone help me by telling me how to create an excel document that monitors usage of many others by using visual basic program?
I currently produce excel reports for my company but need to somehow monitor how many times they are accessed and, if possible, when and by whom.
All these excel files are situated on a server that our company uses and all users systems are connected to this. We use office 2003 and Outlook as the emailing system.
My guessing is that the programming would have to intiate the usage file, amend it, and then close it each time a report is opened.
For each of the files that I want this file to monitor, I have written the following program which essentially opens up the usage file 'EPoS Usage' which is a simple grid, enters an extra one to the relevant counter cell then closes it. I really need to include a date and time for each opening and, if possible, get a username as well. Here it is:
-
Private Sub workbook_open()
-
-
Application.ScreenUpdating = False
-
Workbooks.Open Filename:= _
-
"S:\Newman Common\CATEGORY MANAGEMENT - Ranges\EPoS Usage.xls"
-
-
Dim Counter As Integer
-
Counter = Cells(2, 8)
-
Counter = Counter + 1
-
Cells(2, 8) = Counter
-
ActiveWorkbook.Save
-
ActiveWindow.Close
-
-
Application.ScreenUpdating = True
-
-
-
End Sub
-
Could anyone help?
Many thanks