7

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!

Ben
  • 1,638
  • 2
  • 14
  • 21
  • Do you need to interact with Excel or just read spreadsheet data? If the latter then there are routines (that even work on Unix!) you can use to read the data from the spreadsheet (xlrd, python-excel, etc. – holdenweb Mar 04 '14 at 10:56
  • I need to do some calculations in Python, pass the results into Excel and then be able operate on these results using functions provided by an Excel add-in. I am quite aware this probably isn't an ideal set-up but I'm only helping out with a small of a project and am not really in a position to dictate how they're setting everything up! – Ben Mar 04 '14 at 11:09
  • Hi. I also have some xll Excel add-ins that I want to be able to load and use in Python. Did you find a way to do this? Can you share your solution? Thanks – Confounded Nov 08 '19 at 14:01
  • Does my answer below not work for you? – Ben Nov 09 '19 at 17:28

3 Answers3

6

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')
Ben
  • 1,638
  • 2
  • 14
  • 21
  • 1
    Glad you found answer and posted it. That's funny because I came across RegisterLL (although not that particular page you reference, good find) but it's the one option that I didn't have enough info on to determine if it was relevant. – Oliver Mar 05 '14 at 13:13
5

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")
rleelr
  • 1,854
  • 17
  • 26
1

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
Oliver
  • 27,510
  • 9
  • 72
  • 103
  • Thanks for your response Schollii! – Ben Mar 05 '14 at 11:23
  • @Ben Np. I'd be curious to hear if you have tried any of the above. – Oliver Mar 05 '14 at 13:11
  • I didn't get the chance (I stumbled upon the other solution in between posting the question and seeing your response). If I get time I will look into whether they too solve the problem or not. – Ben Mar 10 '14 at 11:46