[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

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. image.png

  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 image.png

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

③ 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 ".

⑤ 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. image.png

Other

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

Recommended Posts

[Note] Execute Python code from Excel (xlwings)
Execute Python code from C # GUI
Run python from excel
Execute command from Python
Execute command from python
Run a python script from excel (using xlwings)
Run Python from Excel VBA with xlwings & tutorial supplement
Introduction to Python for VBA users-Calling Python from Excel with xlwings-
Execute Python script from batch file
Excel graph creation using python xlwings
Note: Python
[Note] Get data from PostgreSQL with Python
Execute Python code on C ++ (using Boost.Python)
Note for Pyjulia calling Julia from Python
Try to operate Excel using Python (Xlwings)
Python study note_002
Note: Python Decorator
Edit Excel from Python to create a PivotTable
[Python] Learning Note 1
Create a C array from a Python> Excel sheet
Python study note_004
sql from python
python character code
Load and execute command from yml in python
MeCab from Python
Python study note_003
[Node-RED] Execute Python on Anaconda virtual environment from Node-RED [Anaconda] [Python]
[Python] Algorithm-aware code
[Note] openCV + python
I want to make C ++ code from Python code!
Python beginner's note
I tried to execute Python code from .Net using Pythonnet (Hallo World edition)
Excel with Python
Execute C ++ functions from Python with Pybind11 (for Windows & Visual Studio Code people) Debugging
[Python] Django Source Code Reading View Starting from Zero ①
Reading CSV data from DSX object storage Python code
Machine learning python code summary (updated from time to time)
Manipulate excel files from python with xlrd (personal notes)
Get the return code of the Python script from bat
Read QR code from image file with Python (Mac)
Let's start Python from Excel. I don't use VBA.
Mercurial stopped working after migrating from Python 2 to Python 3 (Note)
Execute Python function from Powershell (how to pass arguments)
How to create a kubernetes pod from python code
Execute C ++ functions from Python with Pybind11 (for Windows & Visual Studio Code people) Environment construction
Touch MySQL from Python 3
[Note] future sentence ~ Python ~
Operate Filemaker from Python
[Note] File reading ~ Python ~
Access bitcoind from python
Changes from Python 3.0 to Python 3.5
Changes from Python 2 to Python 3.0
Python code acceleration approach
Python from or import
Rewrite Python2 code to Python3 (2to3)
infomap python draw code
Automatically execute python file
Use MySQL from Python
Before writing Python code
Handle Excel with python
Use MySQL from Python