I am trying to log the opening and closing of excel workbooks to replace the Journaling capability in office.

I have created and tested VBA auto_open and auto_close macros in excel, which log these events to a file. I have then copied them to PERSONAL.xlsb. They run successfully when PERSONAL.xlsb is opened or closed. They have no effect when opening or closing any other workbook.

Am I missing something simple, or is this approach doomed to failure?

  • If it is a macro function to that workbook then it would only apply to that workbook, not others. – Eric F Jan 29 '15 at 16:28
  • You could potentially have a master workbook that monitors a directory and logs for each other workbook within that directory. Just a thought.. – Eric F Jan 29 '15 at 16:39
  • Not sure for Office 2013, but for Excel 2010, you should make a xlsb file placed in %AppData%\Microsoft\Excel\XLSTART so Excel runs it on every launch. – PatricK Feb 4 '15 at 23:53

You'd have to put the code in each workbook, have it open a log file, write to the log file, save that log file, and close the log file. But that code would need to live in each workbook if you are going to use VBA standalone.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.