Xlwings:write Excel macro using Python instead of VBA

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