Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Load Excel add-in using win32com from Python

I've seen from various questions on here that if an instance of Excel is opened from Python using:

xl = win32com.client.gencache.EnsureDispatch('Excel.Application')
xl.Visible = True
wb = xl.Workbooks.Open('Test.xlsx')

Then it does not load the default add-ins. I've tried forcing my add-in to load by instead running:

xl = win32com.client.gencache.EnsureDispatch('Excel.Application')
xl.Visible = True
addin = xl.Workbooks.Open('C:/path/addIn.xll')
wb = xl.Workbooks.Open('Test.xlsx')

However, when I do this an instance of Excel opens and I get a security message asking me to verify I want to open this add-in. If I click "Enable this add-in for this session only" Excel immediately closes and does not open my desired xlsx file.

Does anyone have any ideas how to force the add-in to load and then to allow me to open my file?

Thanks very much for your help!

like image 459
Ben Avatar asked Mar 04 '14 10:03

Ben


People also ask

How do you load Excel add in Python?

Calling a Python Function in Excel Save your module and edit the pyxll. cfg file again to add your new module to the list of modules to load and add the directory containing your module to the pythonpath. Go to the Addins menu in Excel and select PyXLL -> Reload.

What is Python win32com client?

Pywin32 is basically a very thin wrapper of python that allows us to interact with COM objects and automate Windows applications with python. The power of this approach is that you can pretty much do anything that a Microsoft Application can do through python.


3 Answers

I have actually managed to resolve this by borrowing something from this MSDN article relating to doing the same thing with VBA:

http://support.microsoft.com/default.aspx?scid=KB;en-us;q213489

The following now works perfectly:

xl = win32com.client.gencache.EnsureDispatch('Excel.Application')
xl.Visible = True
xl.RegisterXLL('C:/path/addin.xll')
wb = xl.Workbooks.Open('Test.xlsx')
like image 58
Ben Avatar answered Oct 19 '22 19:10

Ben


I had the same problem, but couldn't use xl.RegisterXLL('C:/path/addin.xla') from the accepted answer, because it only works with .XLL files, and I had a .XLA file.

Instead, I found that this worked:

xl = win32com.client.gencache.EnsureDispatch('Excel.Application')

# Need to load the addins before opening the workbook
addin_path = r'C:\path\addin.xla'
xl.Workbooks.Open(addin_path)
xl.AddIns.Add(addin_path).Installed = True

wb = xl.Workbooks.Open(r"C:\my_workbook.xlsm")
like image 24
rleelr Avatar answered Oct 19 '22 18:10

rleelr


I have had much better success using Excel via win32com than any of the other methods, but you might want to look at pyxll (https://www.pyxll.com/introduction.html). Here are a few other things:

  • Have you checked that addin.Installed == True?
  • Have you tried AddIns.Add("c:\windows\addins\TSXL\TSXL.xll").Installed = True?
  • Try xl.DisplayAlerts=False before opening the workbooks
  • Have you tried the four steps in last answer of Automating Excel via COM/Python - standard addins won't load at startup, I copy them here:

    1. Open the XLA / XLL file representing the addin in question
    2. Set addins(addin_name).Installed = False
    3. Addins(addin_name).Add(addin_file_path)
    4. Set addins(addin_name).Installed = True
like image 34
Oliver Avatar answered Oct 19 '22 19:10

Oliver