Introduction
May 05, 2021 Install Python. To install Python using the Microsoft Store: Go to your Start menu (lower left Windows icon), type 'Microsoft Store', select the link to open the store. Once the store is open, select Search from the upper-right menu and enter 'Python'. Select which version of Python you would like to use from the results under Apps. Setup-python V2 What's new Usage Getting started with Python + Actions Available versions of Python Available versions of PyPy Hosted Tool Cache Specifying a Python version Specifying a PyPy version Using setup-python with a self hosted runner Windows Linux Mac Using Python without setup-python Using setup-python on GHES License Contributions. Python Run Shell Command On Windows Capturing Output. The problem with this approach is in its inflexibility since you can’t even get the resulting output as a variable. Os.system doesn’t return the result of the called shell commands.
Python has many options for natively creating common Microsoft Office filetypes including Excel, Word and PowerPoint. In some cases, however, it may be too difficultto use the pure python approach to solve a problem. Fortunately, python hasthe “Python for Windows Extensions” package known as pywin32 that allows us to easilyaccess Window’s Component Object Model (COM) and control Microsoft applicationsvia python. This article will cover some basic use cases for this type of automationand how to get up and running with some useful scripts.
What is COM?
From the Microsoft Website, the Component Object Model (COM) is:
a Platform-independent, distributed, object-oriented system for creating binarysoftware components that can interact. COM is the foundation technology forMicrosoft’s OLE (compound documents) and ActiveX (Internet-enabled components)technologies. COM objects can be created with a variety of programming languages.
This technology allows us to control Windows applications from another program.Many of the readers of this blog have probably seen or used VBA for some level of automationof an Excel task. COM is the foundational technology that supports VBA.
pywin32
The pywin32 package has been around for a very long time. In fact, the bookthat covers this topic was published in 2000 by Mark Hammond and Andy Robinson.Despite being 18 years old (which make me feel really old :), the underlyingtechnology and concepts still work today. Pywin32 is basically a very thin wrapper ofpython that allows us to interact with COM objects and automate Windows applicationswith python. The power of this approach is that you can pretty much do anythingthat a Microsoft Application can do through python. The downside is that you haveto run this on a Windows system with Microsoft Office installed. Before we gothrough some examples, make sure you have pywin32 installed on your system usingpip or conda
One other recommendation I would make is that you keep a link to Tim Golden’s page handy.This resource has many more details on how to use python on Windows for automationand other administration tasks.
Python Compilers Idle
Getting Started
All of these applications start with similar imports and process for activating an application.Here is a very short example of opening up Excel:
Once you run this from the command line, you should see Excel open up. When you press ENTER,the application will close. There are a few key concepts to go through before weactually make this a more useful application.
The first step is to import the win32 client. I’ve used the convention of importingit as win32 to make the actual dispatch code a little shorter.
The magic of this code is using EnsureDispatch to launch Excel. In this example,I use gencache.EnsureDispatch to create a static proxy. I recommend readingthis article if you want to know more details about static vs. dynamic proxies.I have had good luck using this approach for the types of examples included in this articlebut will be honest - I have not widely experimented with the various dispatch approaches.
Now that the excel object is launched, we need to explicitly make it visible bysetting excel.Visible = True
The win32 code is pretty smart and will close down excel once the program is donerunning. This means that if we just leave the code to run on its own, you probablywon’t see Excel. I include the dummy prompt to keep Excel visible on the screenuntil the user presses ENTER.
I include the final line of excel.Application.Quit() as a bit of a belt andsuspenders approach. Strictly speaking win32 should close out Excel when the programis done but I decided to include excel.Application.Quit() to show how toforce the application to close.
This is the most basic approach to using COM. We can extend this in anumber of more useful ways. The rest of this article will go through some examplesthat might be useful for your own needs.
Open a File in Excel
In my day-to-day work, I frequently use pandas to analyze and manipulate data,then output the results in Excel. The next step in the process is to open up theExcel and review the results. In this example, we can automate the file openingprocess which can make it simpler than trying to navigate to the right directoryand open a file.
Here’s the full example:
Here’s the resulting Excel output:
This simple example expands on the earlier one by showing how to use the Workbooksobject to open up a file.
Attach an Excel file to Outlook
Another simple scenario where COM is helpful is when you want to attach a fileto an email and send to a distribution list. This example shows how to do some datamanipulation, open up a Outlook email, attach a file and leave it open for additionaltext before sending.
Here’s the full example:
This example gets a little more involved but the basic concepts are the same.We need to create our object (Outlook in this case) and create a new email.One of the challenging aspects of working with COM is that there is not a very consistentAPI. It is not intuitive that you create an email like this: new_mail = outlook.CreateItem(0)It generally takes a little searching to figure out the exact API for the specific problem.Google and stackoverflow are your friends.
Once the email object is created, you can add the recipient and CC list as well as attachthe file. When it is all said and done, it looks like this:
The email is open and you can add additional information and send it. In this example,I chose not to close out Outlook and let python handle those details.
Copying Data into Excel
The final example is the most involved but illustrates a powerful approach for blendingthe data analysis of python with the user interface of Excel.
It is possible to build complex excel with pandas but that approach can be verylaborious. An alternative approach would be to build up the complexfile in Excel, then do the data manipulation and copy the data tab to the finalExcel output.
Here is an example of the Excel dashboard we want to create:
Yes, I know that pie charts are awful but I can almost guarantee that someone isgoing to ask you to put one in the dashboard at some point in time! Also, this templatehad a pie chart and I decided to keep it in the final output instead of trying to figureout another chart.
It might be helpful to take a step back and look at the basic process the code will follow:
Let’s get started with the code.
In the section we performed our imports, read in the data and defined allthree files. Of note is that this process includes the step of summarizing the datawith pandas and saving the data in an Excel file. We then re-open that fileand copy the data into the template. It is a bit convoluted but this is the bestapproach I could figure out for this scenario.
Next we perform the analysis and save the temp Excel file:
Now we use COM to merge the temp output file into our Excel dashboard tab and savea new copy:
The code opens up Excel and makes sure it is not visible. Then it opens up thedashboard template and data files. It uses the Range('A:D').Select() toselect all the data and then copies it into the template file.
The final step is to save the template as a new file.
This approach can be a very convenient shortcut when you have a situation where youwant to use python for data manipulation but need a complex Excel output. You maynot have an apparent need for it now but if you ever build up a complex Excel report,this approach is much simpler than trying to code the spreadsheet by hand with python.
Python
Conclusion
My preference is to try to stick with python as much as possible for my day-to-daydata analysis. However, it is important to know when other technologies can streamlinethe process or make the results have a bigger impact. Microsoft’s COM technology is a mature technologyand can be used effectively through python to do tasks that might be too difficult todo otherwise. Hopefully this article has given you some ideas on how to incorporate thistechnique into your own workflow. If you have any tasks you like to use pywin32 for,let us know in the comments.
Changes
- 29-Nov-2020: Updated code to capitalize
QuitandVisible.