By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,086 Members | 1,442 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,086 IT Pros & Developers. It's quick & easy.

using VBA to monitor Excel usage

P: 1
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub workbook_open() 
  2.  
  3. Application.ScreenUpdating = False 
  4. Workbooks.Open Filename:= _ 
  5. "S:\Newman Common\CATEGORY MANAGEMENT - Ranges\EPoS Usage.xls" 
  6.  
  7. Dim Counter As Integer 
  8. Counter = Cells(2, 8) 
  9. Counter = Counter + 1 
  10. Cells(2, 8) = Counter 
  11. ActiveWorkbook.Save 
  12. ActiveWindow.Close 
  13.  
  14. Application.ScreenUpdating = True 
  15.  
  16.  
  17. End Sub 
  18.  
Could anyone help?

Many thanks
Mar 27 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.