Run a macro using XLwings

Sometimes in life there are things that you cannot achieve in Excel using XLwings, shocking I know. The 2 examples that I have found so far are password protecting a file and changing the file type e.g. from xlsx to xlsm, these might be possible but I cannot get them to work. For a while I could not get pivots to refresh but as I have covered before that can be done. 

The 2 examples I have given above can easily be achieved using a macro (I may cover these elsewhere). But what this means is you can use XLwings to run the macro and you still have everything being done in a single script. The below gif and code show this. In this example my very simple code just shows a bunch of rows in column B being set to Y. 

The code:

More XLwings

So I wanted to do a little bit more on XLwings. I am hoping to also achieve this using some free gif creation software that I have found. So I am hoping to cover: 

• Creating tabs 
• Switching between tabs
• Setting cell values 
• hiding tabs 
• Refreshing pivot table

The starting point: The picture below shows the starting point. We have a Data tab and a Pivot tab. There is some data in the data tab which has been pivoted on the pivot tab. The data on the data tab has stupidly small column widths and although we will hide it we still want it to look good so will fit those properly. 

So the basic steps I am going to take (as you can see in the code and screenshots) are:

• import required modules 
• open the workbook 
• put in some sleep statements so I can more easily record the gifs
• commands to switch between workbooks
• create a new workbook and hide it
• enter some data on the data tab and auto-fit it. 
• select the pivot tab and refresh the data

The gif below shows it in action, watch carefully and you can see all the actions I describe above. 

The code:

Getting Started: Joining the dots

To be able to use python and its tools to automate the reporting in word and excel you need to be able to get all your data together. Quite simply you will have a variety of data sources. There are many more, but I have personally used and will be covering most of the following:

• Excel
• CSV Files
• Outlook Attachments
• Databases (Oracle / SQL Server / MySQL)
• Outlook attachments

Connecting to Data Sources

I am sure there are many others that people use frequently, I personally like to use databases as my primary data source, but I was originally a databases developer after all. You need to be able to get the data from these sources and into python, probably a pandas dataframe. Luckily there are 2 really good modules for doing this:

1. Pyodbc
2. SQL Alchemy

I like to use both, I find PyODBC is great for kicking off stored procedures etc. but (my understanding at least) is that SQL-Alchemy is the tool to use to use for a wider variety of connections and uses. I have muddled around different sources on the internet to get these to connect correctly.

Importing the required Libraries 

Setting up the connection strings 

Importing to datafarme using PyODBC

Importing to dataframe, from file, using SQL Alchemy.

Connecting to Office:

You then need to be able to connect from this dataframe into Excel and or Word and ideally then send an e-mail either telling people that the files are ready or attaching the relevant file.

There are many ways to connect to Excel, fewer for Word and for Outlook there are again a few ways but the win32com client is the most popular way I have seen of connecting and fairly easy too. My favourite way of automating office is using XLwings, it is great for automating most things in Excel and unlike some other packages it doesn’t remove all the graphs! As a side note the biggest issue I still have with it is that I have not been able to create password protected Excel documents, but you can get round this (using an Excel personal macro).

With word I use Python-Docx. Again, it does have some limitations but there are word arounds for most of the issues. For example, I do not think that you can currently create contents pages however you can get around this.

At this point I won’t go into any more detail on this however you can easily see how you can connect to a database, load data, collect information or run a stored procedure and then you can push this into word and Excel

Anyway so that is the basic process and modules I use.  

Outline of basic flows to get data into and out of a data source and into office using python.