Run Python from Excel VBA with xlwings & tutorial supplement

See the article that you can run Python with Excel VBA using a library called xmlwings in Qiita, and write a little Quick Start + supplement I usually write on my blog, so I think it's a good idea to write a supplement on Qiita.

It seems that xlwings has changed significantly since v0.9, and since it does not work with the current code, I added about v0.9 or later

Development environment

• Windows 7 (32-bit)
• Office 2010 SP-2

Installation

There is nothing special, just install with pip

pip install xlwings

In my environment, it is installed in the following location. Location to refer to when importing files from Excel side later C:\Python27\Lib\site-packages\xlwings

Run sample

Sample code

Python module to call from Excel

• xlwings v0.9 and earlier

mymodule.py

import numpy as np
from xlwings import Workbook, Range

def rand_numbers():
    """ produces standard normally distributed random numbers with shape (n,n)"""
    wb = Workbook.caller()  # Creates a reference to the calling Excel file
    n = int(Range('Sheet1', 'B1').value)  # Write desired dimensions into Cell B1
    rand_num = np.random.randn(n, n)
    Range('Sheet1', 'C3').value = rand_num

• xlwings v0.9 or later

Reference: https://docs.xlwings.org/en/stable/migrate_to_0.9.html

mymodule.py

import numpy as np
import xlwings as xw

def rand_numbers():
    """ produces standard normally distributed random numbers with shape (n,n)"""
    wb = xw.Book.caller()  # Creates a reference to the calling Excel file
    n = int(wb.sheets['Sheet1'].range('B1').value)  # Write desired dimensions into Cell B1
    rand_num = np.random.randn(n, n)
    wb.sheets['Sheet1'].range('C3').value = rand_num

VBA code written on the Excel side

Before v0.9, it could be used even if it was not a RAW string, but in the current version after v0.9, if you use \, use a RAW string or set the path delimiter to / If you do not set it to, an error will occur.

• v0.9 or earlier

ExcelVBA

Sub RandomNumbers()
    RunPython ("sys.path.append('C:\Temp'); import mymodule; mymodule.rand_numbers()")
End Sub

• v0.9 or later

ExcelVBA

Sub RundomNumbers()
    RunPython ("sys.path.append(r'C:\Temp'); import mymodule; mymodule.rand_numbers()")
'    RunPython ("sys.path.append('C:/Temp'); import mymodule; mymodule.rand_numbers()")
End Sub

In this VBA code sample It says RunPython ("import mymodule; mymodule.rand_numbers () "), but I wonder if this is a little trap in the tutorial. It says where to put the file mymodule.py, but it doesn't matter where you put it if you add an arbitrary path before ʻimport of RunPython. This time I put mymodule.py in C: \ Tempwhich is not in the path. I wanted to save Excel itself inC: \ Users \ h_oki \ Documents \ book as test.xlsm` and put the modules in a different location, but make sure that they can be read from anywhere for testing. I wanted to do it as above.

Supplement

It doesn't matter if it's VBA or Excel, it's just executing the Python code, so you can load the module in any place by setting the path after that. ʻImport sysis read by itself in xlwings, so you don't need to enter it. In the sample code, it can not be read unless it is placed in the path or directly under it, but if you want to place it in any path, putsys.path.append ('/ your / to / path') before ʻimport my module All you have to do is write it in. Also, even if you don't add sys.path.append ('/ your / to / path'), there seems to be no problem if the location is set in the environment variable PYTHONPATH.

Execution result

I couldn't upload the image for some reason, so please omit it. However, I was able to execute mymodule.py (the quick start code itself) placed anywhere.

Reference article

Related article

Use xlwings I ran a Python script from Excel and played with xlwings, which can graph the results in Excel.

official

xlwings official Quickstart