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
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
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 in
C: \ 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.
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, put
sys.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
.
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.
Use xlwings I ran a Python script from Excel and played with xlwings, which can graph the results in Excel.
Recommended Posts