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

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

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

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.

ExcelVBA


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

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

Recommended Posts

Run Python from Excel VBA with xlwings & tutorial supplement
Introduction to Python for VBA users-Calling Python from Excel with xlwings-
Run Python with VBA
Run python from excel
Run a python script from excel (using xlwings)
Let's run Excel with Python
Run Aprili from Python with Orange
[Note] Execute Python code from Excel (xlwings)
Excel with Python
Read Excel name / cell range with Python VBA
Run prepDE.py with python3
Handle Excel with python
Run Blender with python
Cloud Run tutorial (python)
Operate Excel with Python (1)
Run iperf with python
Operate Excel with Python (2)
Manipulate excel files from python with xlrd (personal notes)
Let's start Python from Excel. I don't use VBA.
Run iphone safari from mac with python + selenium + safari-webdriver
Operate Excel with Python openpyxl
Run python with PyCharm (Windows)
Run Python with CloudFlash (arm926ej-s)
Run illustrator script from python
Run Label with tkinter [Python]
With skype, notify with skype from python!
Run DHT22 with RasPi + Python
solver> Link> Solve Excel Solver with python
Call C from Python with DragonFFI
Using Rstan from Python with PypeR
Install Python from source with Ansible
Create folders from '01' to '12' with python
Run Rotrics DexArm with python API
Run mruby with Python or Blender
Create an Excel file with Python3
Let's play with Excel with Python [Beginner]
Run XGBoost with Cloud Dataflow (Python)
Run python3 Django1.9 with mod_wsgi (deploy)
Handle Excel CSV files with Python
Python error detection run from Powershell
Run Python scripts synchronously from C #
Call python from nim with Nimpy
Excel graph creation using python xlwings
Until you run python with apache
Run Ansible from Python using API
Read fbx from python with cinema4d
Run Python Scripts from Cisco Memorandum_EEM
Excel aggregation with Python pandas Part 1
Collecting information from Twitter with Python (Twitter API)
Run Cloud Dataflow (Python) from App Engine
Receive textual data from mysql with python
Get html from element with Python selenium
[Note] Get data from PostgreSQL with Python
Run servo with Python on ESP32 (Windows)
Play audio files from Python with interrupts
Create wordcloud from your tweet with python3
[In-Database Python Analysis Tutorial with SQL Server 2017]
Uncle SES modernizes VBA app with Python
Draw Nozomi Sasaki in Excel with python
Quickly create an excel file with Python #python
Run Keras with CNTK backend from CentOS