Excel add-ins like PyXLL are not installed per-workbook, but for the whole Excel application. This means that any function declared via PyXLL is available to all workbooks.
When PyXLL starts it loads the modules listed in the "modules" item in your pyxll.cfg file. It is also possible to dynamically import modules later which expose more functions to Excel. These functions will also be available to all workbooks. Please see https://www.pyxll.com/docs/userguide/reloading.html#rebinding for details of how to expose functions to Excel following importing a module.
If you are dealing with a large number of modules you may want to split up your modules so that some modules are only loaded when needed. If you manage your modules well and do not have a lot of code that executes in the body of your modules this should not be necessary, but if it is taking a lot of time to import everything on startup then this maybe something you want to try.
Also see How to call a Python function when opening an Excel workbook.
How to load a module dyanamically
As well as the "import" statement typically found at the top of a module it is also possible to import modules in Python using the "__import__" function. This can be used to import modules when you do not know the name of the module ahead of time.
After importing a module you can use pyxll.rebind to tell PyXLL to update the Excel function bindings.
You can write a function as follows to import a particular module and call pyxll.rebind. Using PyXLL's @xl_macro decorator you can expose that function so it can be called from Excel:
from pyxll import xl_macro, rebind
@xl_macro
def import_python_module(name):
# import the module using __import__
print("Importing module %s" % name)
__import__(name)
# Update the Excel bindings using pyxll.rebind
rebind()
You can call this macro from VBA to dynamically import previously unloaded modules, eg:
Sub ImportPythonModule Run "import_python_module", "your_python_module_name" End Sub
Note: To import a module, the module needs to be on the "Python path". That can be set in the pyxll.cfg file, but can also be modified at run time if needed. To change the Python path you can append or insert entries to "sys.path".
Loading a module when a workbook is opened
Now we can import a module (or modules) and tell PyXLL to rebind the Excel functions including those newly imported modules we can do that when a workbook is opened.
To run your "import_python_module" macro when the workbook opens a simple way is to use the Workbook_Open event in VBA (this is also possible using purely Python, we'll come on to that in a bit).
To do that, open the VBA editor in Excel and add the "Workbook_Open" Sub to the Workbook object, and call "Run" in there to import the module you need each time the workbook is opened.
What about when reloading PyXLL?
When you reload PyXLL then it will only reload the modules listed in your pyxll.cfg file and the functions from any modules loaded dynamically will not be reloaded.
While in development or testing, the easiest solution is to add the modules you are testing to the modules list in your pyxll.cfg file to ensure they get reloaded correctly.
Alternatively, we can adapt the Python function that imports modules dynamically to keep track of each module it has imported, and then reload those when PyXLL reloads using the pyxll.xl_on_reload event handler. For example,
from pyxll import xl_macro, xl_on_reload, rebind
import importlib
import sys
# We need a set of the dynamically loaded modules so that we can reload them
# later, but we don't want that set to be cleared when this module gets reloaded.
# So, we stash the set in the sys.modules dictionary and fetch it if it already exists.
_dynamically_loaded_modules = sys.modules.setdefault(__name__ + "-dynamic-modules-list", set())
@xl_macro
def import_python_module(name):
# import the module using __import__
print("Importing module %s" % name)
__import__(name)
# Remember this module so we can reload it later
_dynamically_loaded_modules.add(name)
# Update the Excel bindings
rebind()
@xl_on_reload
def on_reload(reload_info):
# Reload each module imported previously
for name in _dynamically_loaded_modules:
module = sys.modules.get(name, None)
if module is not None:
print("Reloading dynamically loaded module %s" % name)
importlib.reload(module)
# Update the Excel bindings
rebind()
In the code above, because that module itself would get reloaded when reloading PyXLL we have to keep track of the dynamically loaded modules somewhere that will not get reloaded, otherwise it would keep getting reset back to an empty set! Above the set of dynamically imported module names is added to the sys.modules dictionary -- this is a little bit dirty; another solution would be to keep this in a separate module that is not listed in your pyxll.cfg "modules" setting so it does not automatically get reloaded (and you would also need to add it to your "deep_reload_exclude" list if using deep reloading, which is enabled by default).
The above code does not handle deep reloading as that is too complex to cover in this article. Instead it is recommended that if deep reloading is important for your development you add your modules to the pyxll.cfg file during development and only use this technique of dynamic imports once you no longer need to regularly reload your module.
What if I don't want to use any VBA?
The solution outlined above relies on a little bit of VBA in the "Workbook_Open" event handler. If you would rather not use any VBA at all then there are a couple of solutions you can use instead.
1. Use a worksheet function instead of a macro
Instead of using a macro to import modules dynamically you could do exactly the same thing from a worksheet function.
You can call that worksheet function from your workbook, passing in the module name (or names) to import.
When you register the function pass "recalc_on_open=True" to @xl_func, then that function will be called each time the workbook is opened (see Recalculating On Open). Now each time your workbook is opened that function is called and your modules are imported, without the need for any VBA or event handling.
2. Use the Workbook.Open COM event from Python, instead of from VBA.
You can subscribe to exactly the same Excel COM events that are available in VBA using Python.
By registering a COM event handler in Python to respond to the Workbook.Open event you can dynamically import your modules in the same way as above.
Please see the FAQ article Getting Excel Events in Python for full details of how to get Excel events using Python without the need for any VBA.
You will need to know what modules to import for each workbook. That could be hard-coded, or you might store the list of required modules somewhere on the workbook that can be queried using the Excel COM API (e.g. using the Workbook "CustomDocumentProperties" or "CustomXMLParts" properties).
Final Notes
Although you can import modules when workbooks are opened, remember that all functions are shared between all workbooks, regardless of when the modules are imported.
This means that if you have one module with function "foo" and another module with a different module intended to be used with only one workbook also called "foo", only one of the "foo" functions will be registered and all workbooks will call the same function.
A common solution to this is to give your function names a unique prefix so that it's unabiguous which function you are calling. You can use the "name" kwarg to @xl_func to give your functions a different name in Excel to the one they have in Python.