Use Python For Your Excel Macros

2020-03-23

Almost everyone has (ab)used excel as a data processing tool.Excel is used since its ubiquitous and easy to get started with. Once, you start doing really interesting things, you'll end up with equations ready to break at the slightest change or spanning many sheets. The industrious have even learned Visual Basic to take it further or try to wrangle it to a sense of sanity. In the end you're not using the right tool for the job.

Excel can still be used as the frontend, but the heavy lifting should be done with Python. It's easier to write and read with support for tons of packages that make it more useful and maintainable long term. You can even ditch Excel for Libreoffice(which has native support for Python macros), a standalone desktop gui or web gui in the long run. Let's take a look at how using python would work.

You'll need to install the xlwings package using pip or conda and add xlwings to Excel. The easiet way to get started is opening a command prompt and type xlwings quickstart myproject. Then import it in your python file.

import xlwings as xw

Next you connect to the book: wb = xw.Book.caller() Now you can start grabbing data from the file using its cell position or cell name if you given it one:

x = xw.range('C1').value  
y = xw.range('name').value

Naming cells is good practice to make the sheet more robust. The name will follow the cell if it's moved, allowing the sheet to be rearranged without breaking the macro. The data is now available to Python allowing you to do pretty much anything. Database access for postgres, mysql/mariadb, sqlite etc. is easy using their respective drivers.

 conn = sqlite3.connect('E:\\WinPython-64bit-3.4.3.5\\notebooks\\CraneCapacityLookUp\\crane.db')
    c = conn.cursor()
    if xw.Range('Boom_Config').value == 'SF' or xw.Range('Boom_Config').value == 'SA':  
        c.execute("SELECT Capacity FROM {} WHERE BoomType == ?1 and Counterweight == ?2 and Outrigger == ?3 and BoomLen == ?4 and BoomAngle is Null and JibLen == ?6 and JibAngle == ?7 and Radius >= ?8".format(crane), dbin)
    elif xw.Range('Boom_Config').value == 'SH':
        c.execute("SELECT Capacity FROM {} WHERE BoomType == ?1 and Counterweight == ?2 and Outrigger == ?3 and BoomLen == ?4 and BoomAngle is Null and JibLen is Null and JibAngle is Null and Radius >= ?8".format(crane), dbin)
    Capacity = c.fetchone()

The data is then written back to Excel by doing the opposite of what we did grabbing the data:

xw.Range((row,colCap)).value = Capacity

The user would run the script by clicking the Run button in the xlwings tab on the ribbon. You get all advantages of a macro without having to use Visual Basic.