Macro to record history of Excel files
 

Dear All,

I am trying to work out a macro in Excel to record a history of excel files
that are created, modified and accessed. I have tried to use personal.xls to
do this, however, it always generates error like this, but it is very easy to
impliment it in word VBA:
In Excel: personal.xls workbook:

Const Rec = "C:\Excel_documnet_record.txt"
Public Sub Workbook_Open()
dim Record_Str

Record_Str= Record_Str & vbTab & "-Opened" & vbTab &
Application.Workbooks(2).Path & "\" & Application.Workbooks(2).Name

Open Rec For Append As #1
Print #1, Record_Str
Close #1
End Sub

Error message: Runtime error 9

However if it is MS word, Similar macro works:

Private Sub Document_Open()
Dim a
a = Now()
a = a & vbTab & "-Opened" & vbTab & Me.Path & "\" & Me.Name

Open Rec For Append As #1
Print #1, a
Close #1
End Sub

====
I actually want to make similar macros to powerpoint also, but it seems
there is no way at all.

Any suggestion will be highly appreciated!
Thanks

Macro to record history of Excel files
 

Application.Workbooks(2).

My guess is you only have one workbook open, if so an attempt to reference a
non-existent workbook in the collection would generate your error 9

Replace the '2' with "theName", or Workbooks.count (that's vague), or
preferably use a reference to the workbook you want to detail.

You might consider looking into trapping events at application level, which
would enable you to trap open, save and close events of all workbooks.

Regards,
Peter T

"Excel_VBA programmer" <Excel_VBA
wrote in message ...
Dear All,

I am trying to work out a macro in Excel to record a history of excel
files
that are created, modified and accessed. I have tried to use personal.xls
to
do this, however, it always generates error like this, but it is very easy
to
impliment it in word VBA:
In Excel: personal.xls workbook:

Const Rec = "C:\Excel_documnet_record.txt"
Public Sub Workbook_Open()
dim Record_Str

Record_Str= Record_Str & vbTab & "-Opened" & vbTab &
Application.Workbooks(2).Path & "\" & Application.Workbooks(2).Name

Open Rec For Append As #1
Print #1, Record_Str
Close #1
End Sub

Error message: Runtime error 9

However if it is MS word, Similar macro works:

Private Sub Document_Open()
Dim a
a = Now()
a = a & vbTab & "-Opened" & vbTab & Me.Path & "\" & Me.Name

Open Rec For Append As #1
Print #1, a
Close #1
End Sub

====
I actually want to make similar macros to powerpoint also, but it seems
there is no way at all.

Any suggestion will be highly appreciated!
Thanks