When Excel addin runs 'RunPython()', Workb

Question

I found xlwings is a very exceptional project as a bridge between Excel and Python. So I am applying it to my excel addin development.

But I got some problem.

When excel addin calls python module, the Workbook.caller() does not return Workbook object(addin workbook itself)! Just Error!

Instead of Workbook.caller(), I make use of 'Workbook() passing by 'Current ActiveWorkbook's name'. But in that case, I'm afraid that 'Optimize_connection= true' may raise memory garbage issue!

(If 'IsAddin' property in VBA is off, then it runs very well)

Is there anyone to help me?

Thank you in advance.

Answer 1

Here's a work-around to get the Excel add-in that's calling Python code:

import mock
import platform
import xlwings
from   xlwings import Workbook

def get_add_in():
    if platform.system() == 'Windows':
        # Workbook.caller crashers instead of returning the add-in
        get_add_in_caller_on_windows()
    else:
        return Workbook.caller()

@mock.patch('xlwings.Sheet.active')
def get_addin_caller_on_windows(mock_active):
    # The xlwings.Sheet.active method is mocked because the add-in has no
    # active worksheet.
    xl_app = xlwings.xlplatform.get_xl_apps()[0]
    return Workbook(xl_workbook=xl_app.ThisWorkbook)

It works with Python 3.4 and xlwings 0.6.4 on:

• Windows 8.1 with Excel 2013
• OS X 10.10 (Yosemite) with Excel for Mac 2011

Answer 2

I have not tested this, but the solution may be as simple as replacing ThisWorkbook with ActiveWorkbook when using the xlwings VBA module as add-in. Anyhow, I've created an issue to get this resolved: https://github.com/ZoomerAnalytics/xlwings/issues/153