[Note] Execute Python code from Excel (xlwings)

I create an automation tool with openpyxl + PyInstaller to automate the work (Excel data processing) performed on another person's PC, but the startup is slow and frustrating. However, learning Excel VBA is a pain. I found the following article when I was wondering if there was an easier way.

Python installed in Excel? Then --use xlwings

You can call Python code from Excel and use it. But it doesn't work. At that time, I found the following article.

[VBA] Try executing Python code from VBA (1) [Python]

This worked. If you install Anaconda on a Windows PC that has Excel and set it up a little, you can just pass the Excel file and Python file. So I summarized it in a memorandum.

environment

• Windows 10 Home Edition
• PyCharm Community Edition 2019.1.3
• Anaconda 3（xlwings==0.15.8　pywin32==223）
• Excel（Office 365）

Execute Python code from Excel (how to)

It is assumed that the above environment is in place. xlwings and pywin32 are installed as a set with Anaconda.

① Create an Excel macro valid workbook (* .xlsm)

Python code and Excel macro-enabled workbooks that you will create later

1. Must be in the same directory
2. The file name (before the extension) is the same Is a prerequisite for operation. The file name should take that into consideration. This time, the file name is "excel_test.xlsm".

② Import xlwings.bas into Excel VBE

1. Start Excel and click "Visual Basic" on the development tab to start VBE.

2. Click VBE File> Import File, select the file (xlwings.bas) with the following path, and import. Path: C: \ Users \ {username} \ Anaconda3 \ pkgs \ xlwings-{xlwings version}-py {Python version} \ Lib \ site-packages \ xlwings \ xlwings.bas

If the import is successful, "xlwings" will be created in the standard module folder as shown in the figure below.

③ Create a Python file

As mentioned above, create it in the same directory with the same file name (before the extension) as the Excel macro-enabled workbook. This time, the file name was "excel_test.py" and the code was as follows.

excel_test.py

# coding: utf-8

import xlwings as xw


def copy_add_text():
    txt = xw.Range('A1').value
    txt += ', I am the Doctor.'
    xw.Range('B3').value = txt

(4) Add a standard module with VBE and write the code to call the method of the Python file.

The contents of the code are as follows.

1  Option Explicit
2  
3  Public Sub copyText()
4      Call RunPython("import excel_test; excel_test.copy_add_text()")
5  End Sub

[Supplement] The argument on the 4th line is " import module name; module name.method name ".

• Module name = Python file name (no extension required)

⑤ Excel development tab> Call because the method has been added to the macro

The code this time is to paste the character string of cell A1 into cell B3 of "●●, I am the Doctor." The execution result is as shown in the figure below.

Other

Win32api import error occurred at pywin32 == 227. Solved by setting pywin32 == 224. Details are unknown.