How to call excel add-ins from python

Question

Is there a way to call Excel add-ins from python? In my company there are several excel add-ins that are available, they usually provide direct access to some database and make additional calculations.

What is the best way to call those functions directly from python?

To clarify, I'm NOT interested in accessing python from excel. I'm interested in accessing excel-addins from python.

Answer 1

There are at least 3 possible ways to call an Excel add-in, call the COM add-in directly or through automation.

Microsoft provide online documentation for it's Excel interop (https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel). Whilst it's for .NET, it highlights the main limitations. You cannot directly call a custom ribbon (which contains the add-in). This is intended to protect one add-in from another. (Although you can install/ uninstall add-in).

COM add-in You can call the COM add-in directly using win32com.client. However you can only run methods that are visible to COM. This means you may have to alter the add-in. See a C# tutorial https://docs.microsoft.com/en-us/visualstudio/vsto/walkthrough-calling-code-in-a-vsto-add-in-from-vba?view=vs-2019.

Once the method is exposed, then it can be called in Python using win32com.client. For example:

import win32com.client as win32

def excel():
    # This may error if Excel is open.
    return win32.gencache.EnsureDispatch('Excel.Application')

xl = excel()
helloWorldAddIn = xl.COMAddIns("HelloWorld") # HelloWorld is the name of my AddIn.
#app = helloWorldAddIn.Application 
obj = helloWorldAddIn.Object # Note: This will be None (null) if add-in doesn't expose anything.
obj.processData() # processData is the name of my method

Web scraping If you're able to upload your add-in to an office 365 account. Then you could preform web scraping with a package like Selenium. I've not attempted it. Also you'll most likely encounter issues calling external resources like connection strings & http calls.

Automation You can run the add-in using an automation package. For example pyautogui. You can write code to control the mouse & keyboard to simulate a user running the add-in. This solution will mean you shouldn't need to update existing add-ins.

A very basic example of calling add-in through automation:

import os
import pyautogui
import time

def openFile():    
    path = "C:/Dev/test.xlsx"
    path = os.path.realpath(path)
    os.startfile(path)
    time.sleep(1)

def fullScreen():    
    pyautogui.hotkey('win', 'up')
    time.sleep(1)

def findAndClickRibbon():    
    pyautogui.moveTo(550, 50)
    pyautogui.click()
    time.sleep(1)

def runAddIn():    
    pyautogui.moveTo(15, 100)
    pyautogui.click()
    time.sleep(1)

def saveFile():    
    pyautogui.hotkey('ctrl', 's')
    time.sleep(1)

def closeFile():    
    pyautogui.hotkey('alt', 'f4')
    time.sleep(1)

openFile()
fullScreen()
findAndClickRibbon()
runAddIn()
saveFile()
closeFile()