Log to a file everytime I open a spreadsheet

[Roshintosh]

I would like a VBA macro to log to a file every spreadsheet I open.

Is it possible in VBA ?

Can I have a macro to fire when a spreadsheet is open to log the
filepath+filename to a file ? Most of the spreadsheets I run are from
external sources, and do not contain macros

Cheers
Jim

 

[Peter T]

You could log all workbook events, including "Open" from your Personal xls
or some addin. Try the following in (say) your personal

Insert a Class module, rename it from Class1 to "clsAppEvents"

Add following code in clsAppEvents, ThisWorkbook module and in a normal
module as indicated -


'' Class named clsAppEvents

Public WithEvents xlApp As Excel.Application

Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)
LogFileOpen Wb.FullName
End Sub


Sub LogFileOpen(sFullName As String)
Dim iFF As Integer
Dim sText As String
Dim sFile As String
Const cPATH As String = "c:\temp\" ' < change to suit
Const cLOG As String = "LogWorkbooks.txt" ' < change to suit

On Error GoTo errH
sFile = cPATH & cLOG

sText = Now & " " & vbTab & sFullName
iFF = FreeFile
Open sFile For Append As #iFF
Print #iFF, sText
done:
On Error Resume Next
Close #iFF
Exit Sub

errH:
Debug.Print "LogFileOpen error: " & Err.Description
Resume done
End Sub


'' in ThisWorkbook module

Private Sub Workbook_Open()
SetAppEvents
End Sub


' in a normal module
Private mClsEvnts As clsAppEvents

Sub SetAppEvents()
Set mClsEvnts = New clsAppEvents
Set mClsEvnts.xlApp = Application
End Sub



For testing, manually run SetAppEvents; this will create an instance of the
class to trap Application level events.

Open some workbooks and look at LogWorkbooks.txt

Assuming all appears to be working as expected, in future SetAppEvents will
be called from Workbook_Open when Personal.xls opens.

If you edit any of the code you'll need to run SetAppEvents again.

Regards,
Peter T