Sometimes you may want to call a Python function whenever a workbook is opened. For example, to pre-load some data or maybe even to track usage.
If you have spent much time writing VBA you are probably familiar with using a VBA "Workbook_Open" Sub that get calls each time the workbook is open, but how can you do the same thing using Python?
Using Excel Events Directly in Python
You can do exactly the same thing using Python as all Excel events are exposed to Python! You can write event handlers to listen for all of the Excel events, including the Workbook.Open event. This FAQ article explains how to do just that: Getting Excel Events in Python.
The downside to this method is that your event handler will be called for all Workbook.Open events for any workbook. You will have to code your event handler so that it does the right thing depending on what workbook has been opened.
If you need to run something (like open tracking) regardless of what workbook has been opened this isn't a problem, but if you want to run different code specific to each workbook then you will need to handle that youself. For example, you might call a different function from your event handler depending on the name of the opened workbook; or you might read some data from the opened workbook to determine what should be done.
A Simpler Way using an Excel Worksheet Function (UDF)
You may already have written worksheet functions (or UDFs) using PyXLL, but if not take a look at the documentation Worksheet Functions - Introduction for details.
Normally a worksheet function will be called when one or more of its inputs are changed and Excel determines it needs to be recalculated. The PyXLL @xl_func decorator takes a keyword argument "recalc_on_open", and we can use this to make our function get called when the workbook is first opened.
By adding a worksheet function that uses the "recalc_on_open" feature to our workbook we can have a Python function that's called when the workbook is opened. Unlike using the Excel events directly as above, this is specific to the workbook and the function will only be called for workbooks that use it, not all workbooks. We can also pass in arguments specific to the workbook if needed.
Note: The function will also be called if the inputs are changed or if the workbook is completely recalculated.
Mixing VBA and Python
If neither of the above methods are right for your application then there is one more way.
You can use a VBA "Workbook_Open" Sub, saved in your Excel workbook. With PyXLL there is no need to use VBA, as you've seen above, but occasionally the pragmatic solution is to use a little bit of VBA in order to call your Python code.
From a VBA Sub like "Workbook_Open" you can call a Python Excel macro using "Run". Writing a Python Excel macro is similar to writing a worksheet function as above, but instead of @xl_func you would use @xl_macro.
For more details on writing Excel macros in Python please see this page of the PyXLL documentation: Macro Functions.