Xlwings:write Excel macro using Python instead of VBA

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.