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.
You can call Python code from Excel and use it. But it doesn't work. At that time, I found the following article.
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.
It is assumed that the above environment is in place. xlwings and pywin32 are installed as a set with Anaconda.
Python code and Excel macro-enabled workbooks that you will create later
Start Excel and click "Visual Basic" on the development tab to start VBE.
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.
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
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 "
.
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.
Win32api import error occurred at pywin32 == 227. Solved by setting pywin32 == 224. Details are unknown.