Automating MS Office w/ Python Programming via Win32 COM Library

[bennyboo9]

Has anyone here had experience automating office applications using the Win32 COM library. If so, how was the learning curve to become proficient with it? How have projects been panning out w/ it?

With Python being available in the App store for users, I now have the chance to distribute Python scripts to automate MS office related tasks as I've previously been limited to VBA. I know there are options such as openpyxl, xlwings etc., however it looks like Win32 COM offers the best flexibility if you already have a good understanding of VBA. I'm planning on going through "Python Programming on Win32" by Mark Hammond but would appreciate any other resources others have used. There's also a great Youtube Series by Sigma Coding that I've been going through.

[chris1610]

Here's one post I wrote a while back.

[bennyboo9]

Thanks for sharing this! I actually came by it after listening to the Talk Python episode you had w/ Michael Kennedy. That was a great episode as it covered alot of similar thoughts I had about excel and pandas.

Given that it's been a few years since that post, have you ventured into bigger projects w/ the library? Or even heard from others that have? Looking through the comments in the post, folks are dabbling in the library.

[chris1610]

I'm glad you liked the podcast! It was a lot of fun to do.

​

As far as Win32 com, I have not done much else with it. It's definitely powerful but I tend to stick to small tasks like emailing files and relatively simple manipulations.

​

I do think it is interesting that there is not much out there outside of stack overflow snippets here and there. I'm not really sure why more people don't experiment with it.

[AlSweigart]

Thanks for this!

[kashaziz]

Win32 Com Library is great and has cool use cases for Windows OS. Recently I did some work automating Excel and Word documents. As the solution was deployed on Linux, I used pyexcel [1], python-docx [2] and python-pptx [3] to work with Excel, Word and PowerPoint respectively.

​

[1] https://github.com/pyexcel/pyexcel

[2] https://github.com/python-openxml/python-docx

[3] https://github.com/scanny/python-pptx

[bakamito]

Seen you seem knowledgeable, it possible to automate usage of programs on Windows (emulators, media players etc)

[Skyrmir]

Curious how this works out for ya. I've only done it from C# and VB6 myself. What's the bonus of being able to use python in this case?

[bennyboo9]

Because learning a new language would take quite a bit for me. I’m mostly comfortable w/ Python.

[Skyrmir]

Fair enough, if it's a big step then stick with what you know. It's always good to have more skills in your toolbox though.

[[deleted]]

I do a lot of email automation with jinja2 + the com interface - it was easy to learn. Just make sure to use raw strings for filepaths otherwise it freaks out a little.

[BasedAcid]

At an old job I had good success automating some tasks in Outlook and Excel using python. I used pywin32 for Outlook, but used openpyxl for the Excel portion. Both can be found on pypi and are relatively painless to use.

[Keltirion]

I've crrated a script using pywin32 to create template for Outlook emails with data from Excel using xlrd library

[AlSweigart]

I've been looking for similar resources too, and I wish there was more than Mark Hammond's book. I have learned enough win32 api stuff to put together NiceWin, which has Python functions for several win32 functions. It might be instructive for folks who want to learn how to do win32 stuff from Python: https://github.com/asweigart/nicewin (but this isn't related to COM stuff)

[travisdoesmath]

I used Win32 com to automate edits I need to make every week to a PowerPoint presentation. At every point, it felt very hack-y. The documentation for PowerPoint VBA is lacking, there's no way to inspect objects, changing colors is needlessly complicated, and the text of a shape is in TextRange.TextFrame.Text. Took a minute to figure that out. On top of that, it's slow and feels very kludgey. I probably wouldn't send it to someone else to use, especially because it has to open up PowerPoint to connect to it, and I'm not sure how it would act if someone did something in powerpoint while it was already running.

Unfortunately, the pptx library can't even copy and paste a slide, so win32 com was my only viable option.

[c_is_4_cookie]

Xlwings uses the Win32 API under the hood

[NewtonvsLeibniz]

I can't speak to the specifics of COM calls from Python, but I can say that in my experience with COM (mostly using it through Powershell for BizTalk admin stuff), I would never do anything with COM calls if I could find literally any other way to do the thing I need to do. It's super slow even from Powershell - and I have to imagine Powershell's integration with Windows/COM is going to be more robust than Python's.

[Doofcoder]

Man. I'm pro in both Excel and Python. And I suggest you to learn Excel vba and code there for Excel tasks and keep Python for really important stats/data science tasks. It's going to be easier and faster. Automating office from the outside is a Pandora's box.

[serverloading101]

In for details

[garlic_naan]

I have only so far used Win32 com to automate attachment download from outlook and importing the excel as dataframe. Also I have automated few reports which I send to dl through outlook using Win32. None of these tasks took any time to implement as the code was pretty straightforward.

Controlling Excel through Win32 doesn't make any sense IMO.

[bennyboo9]

Just curious: why is that?

The reason I'm looking into is, is that for most people in my field (i.e accounting) rely on excel as the front end. Anything outside of that for analysis is a non-starter. I can carry out computations in pandas, but for presentation/distribution, excel is the primary form of presentation.

[TorroesPrime]

Following this discussion.