I want to write Excel macros to deal with the data, but I am not familiar with VBA language. So I decide the use of Python instead of VBA.
At the beginning, I find XLRD,XLWT for Python Excel operations, and that's doesn ' t support Excel macro. Finally I find xlwings.
Xlwings-make Excel fly!
- Scripting: Automate/interact with Excel from Python using a syntax close to VBA.
- Macros: Replace VBA Macros with clean and powerful Python code.
- UDFs: Write User Defined Functions (UDFs) in Python (Windows only)
You can find more details in the offical document Links: https://www.xlwings.org/
Xlwings have many power functions, I'll only introduce how to write Excel macros
1. Installation
The easiest-to-install xlwings is via PIP:
Pip Install Xlwings
or Conda:
Conda Install Xlwings
I suggest that you install the Anaconda, it already includes a lot of useful moudles, and xlwings would depend on part of them.
2. add-in
You need Windows command line to install/remove the add-in in Excel.
xlwings addin install
: Copies the xlwings add-in to the XLSTART folder
After installing the add-in, it'll be available as Xlwings tab on the Excel Ribbon. You need to give the interpreter path.
Interpreter
: The path to the Python interpreter (works also with virtual or Conda Envs), e.g. "C:\Python35\pythonw.exe"
or "/usr/local/bin/python3.5"
. An empty field defaults to that expects the interpreter to is set in the on pythonw
PATH
Windows or on .bash_profile
Mac.
3.Quickstart
You need Windows command line to create necessary files automatically.
xlwings quickstart myproject
This command was by far the fastest-to-get off the ground:it creates a new folder with an myproject
Excel workbook that ALR Eady have the reference to the Xlwings addin and a Python file, ready to be used right away:
MyProject | -- myproject.xlsm|--myproject.py
4.vba:runpython
In the VBA Editor ( Alt-F11
), write the code below to a VBA module. You can add new module viaInsert > Module
Sub HelloWorld () Runpython ("import Hello; Hello.world ()") End Sub
This calls the following code in hello.py
:
# hello.py Import NumPy as NP Import xlwings as XW def World (): = XW. Book.caller () wb.sheets[0].range ('A1'Hello world! '
You can then attach to HelloWorld
a button or run it directly in the VBA Editor by hitting F5
.
Xlwings:write Excel macro using Python instead of VBA