Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Log to a file everytime I open a spreadsheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Log to a file everytime I open a spreadsheet
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 "Roshintosh" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Chart looses format everytime I open the file | Charts and Charting in Excel | |||
how can I open an excel file everytime on certain sheet? | Excel Discussion (Misc queries) | |||
EULA pops up everytime I open an Excel file in 2003 ver | Excel Discussion (Misc queries) | |||
How do I set up an excel doc. to open on the same tab everytime | Excel Discussion (Misc queries) |