Workbook Tracker

[laaview]

Hi Friends

i'm trying to write tracker for Workbooks, like Who, When, Book name and location. I've written a code on workbookopen event, but that works with that particular book. i'm looking for an Add-in like personal.xla, which has to log every details into a workbook called Tracker.xls.

Thank Q.

[laaview]

Suggestions please... for above mentioned task.

[laaview]

Apologies for confusion. Yes, I'm looking for guidance.

Personal.xls should be able to make an entry into tracker.xls of Every opened workbook, like who, when and from which path the book was opened.

Thank You.

[laaview]

i'm pasting the code here. Tracker.xls is the shared and password protected readonly file. Personal.xla contains the below code to open the Tracker.xls and make entries of USER, WORKBOOK NAME, PATH AND TIME, then save and closes the file.

' below code is in Class Module:
' Class module renamed as CExcelEvents

Private WithEvents App As Application

Private Sub App_NewWorkbook(ByVal Wb As Workbook)

Dim LstRow As Long
Application.DisplayAlerts = False
Workbooks.Open "D:\Tracker.xls", WriteResPassword:="12345"
Workbooks("Tracker.xls").Activate

LstRow = Workbooks("Tracker.xls").Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
Workbooks("Tracker.xls").Sheets(1).Cells(LstRow + 1, 1).Value = Application.UserName
Workbooks("Tracker.xls").Sheets(1).Cells(LstRow + 1, 2).Value = Wb.Name
Workbooks("Tracker.xls").Sheets(1).Cells(LstRow + 1, 3).Value = Wb.Path
Workbooks("Tracker.xls").Sheets(1).Cells(LstRow + 1, 4).Value = Now
Workbooks("Tracker.xls").Save
Workbooks("Tracker.xls").Close
Application.DisplayAlerts = True

End Sub

Private Sub Class_Initialize()
Set App = Application
End Sub


' below code is in ThisWorkbook:


Private XLApp As CExcelEvents

Private Sub Workbook_Open()
Set XLApp = New CExcelEvents
End Sub


' After above code file Saved as Personal.xla and included in Addin LIbrary.


This code is working only for Every new workbook opening ( If i remove the lines in RED). But i want this code to work for " on opening of existing workbooks " then the the two lines in RED are compulsory.

Please see the Tracker.xls for the data i'm looking for.

Please revert for any clarification.

For Application Events followed instructions from http://www.cpearson.com/excel/AppEvent.aspx