Category Archives: win32com

Python Module & Notebook to Flatten MS Project and Create Excel Output

In this quick post I am using the function demonstrated in this post.  I have wrapped it into a module and used it in a notebook to read a MS Project file, then output the results of the flattened MS Project to excel workbooks which I then formatted using a quick and dirty self made excel formatting module (code can be found here).

The input MS Project looks like this:

MSProject

And produces out like this:



Loading

Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.

 

Reading and Interpreting MSDN Object Model Reference

My initial months working with win32com saw me searching and reading MSDN Object Model References, reading other peoples win32com python code, and trawling VBA snippets whilst bashing away in a python IDE trying to get the function I was after. So here are my tips in reading the MSDN object references with the intent of replicating the functionality using win32com in Python.

The structure is roughly:

Object.Method()

Object.Property

Let’s start simple and working from where this article left off but with constants.

Step 1: Import modules and constants

import win32com.client
from MSOconstants.MSO import constants as MSO
from MSOconstants.MSPPT import constants as MSPPT

Step 2: Create and set-up references to a powerpoint application, presentation, slide, add a diamond and save the presentation.

pptApp= win32com.client.gencache.EnsureDispatch (“Powerpoint.Application”)
presentation = pptApp.Presentations.Add()
slide = presentation.Slides.Add(1, MSPPT.ppLayoutBlank)
myDiamond = slide.Shapes.AddShape(MSO.msoShapeDiamond, Top=100,Left=100, Width=20, Height=20)
presentation.SaveAs(“_myPowerPoint”, MSPPT.ppSaveAsPresentation)


So far so good. I have a powerpoint slide with diamond thus:

ppt_diamond

Let’s say I want to add a bar to the slide that represents timeline between two dates such that I can place myDiamond on the timeline at a place that represents that point in time along the timeline; say 1/4 along the timeline.

I need to:

  • Determine Slide Dimension
  • Draw a box the width of the slide using the slide dimension
  • place myDiamond 1/4 away along the box

Step 3: Determine Slide Dimension

A slide is dimensioned in pixels. I want to get the dimensions of my slide. I google “msdn powerpoint slide width” and get this link as the first entry.

slideWidth.PNG

So we need an object that represents the presentation “Application.ActivePresentation” so that we can call the PageSetup objects SlideWidth property. In our case we have a reference to the presentation in the variable “presentation“. So we call the presentation.PageSetup.SlideWidth property

width=presentation.PageSetup.SlideWidth

for me it is 960 pixels.

Note: we can’t use “with” statements in Python.

Step 4: Draw a box the width of the slide using the slide dimension

I google “msdn powerpoint add shape” and get this link first

ppt_addShape

And hyperlink off to MsoAutoShapeType ctrl+f to find msoShapeRoundedRectangle

roundRect

create a rectangle from left=0, 50 pixels down from the top, 960 pixels wide and 10 pixels in height:

RRect = slide.Shapes.AddShape(MSO.msoShapeRoundedRectangle, 0, 50, 960, 10)

ppt_timeline.PNG

Step 5: Place myDiamond 1/4 away along the box

Google “msdn powerpoint shape position” and get this first link

scroll down the page and you’ll see that it has a property called “left” and “top”

I want myDiamond 1/4 along 960 pixels and in the centre of 10 pixels high box that is 50 pixels down.

myDiamond.Left=960/4
myDiamond.Top = 50-10/2

ppt_diamondOnLine.PNG

Easy!



Loading

Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.

How to Generate Python Modules of MS Office Constants

I spent ages trying to work out how to create a .py module that contained the MS Office enumeration constants as referenced in this article s-anands-anand-1

I solved it with reference to this entry in stackoverflow

The snippet below creates four python module for each of Microsoft Office, Excel, Word and Powerpoint in the “MSOconstants” directory of my Jupyter Lab notebook location (I’m using Jupyter Lab notebooks.  You can reference any folder location of your choice).



Loading

Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.

I hope this save you some time and frustration.

Note:  if you have Microsoft Project then you can create the equivalent by referencing “Microsoft Office 14.0 Object Library” (14.0 is the version I happen to have, you may have a different version).  This is important as in a later blog I will outlining how to use win32com with MS Project.

Using win32com To Work with Microsoft Office Applications

Requisites: Installing win32com

References:  Comprehensive Documentation on win32Com  This is a great reference but takes some reading

The following code will open a new PowerPoint document, add a diamond and save it.

import win32com.client
pptApp= win32com.client.gencache.EnsureDispatch (“Powerpoint.Application”)
presentation = pptApp.Presentations.Add()
slide = presentation.Slides.Add(1, 12)
myDiamond = slide.Shapes.AddShape(4, Top=100,Left=100, Width=20, Height=20)
presentation.SaveAs(“I:\\MyPythonWorkArea\\jupyterLab\\myPowerPoint”,1)

So let’s break this down:

import win32com.client

Here we are importing the key module that will allow us to access Microsoft Office applications through the COM object. 

But what is a COM object?  It stands for Component Object Model, which, to me at least, means it gives , through an interface (COM object), to the Microsoft Office applications and their associated methods and attributes.

pptApp = win32com.client.gencache.EnsureDispatch (“Powerpoint.Application”)

In this line we are creating a reference (“pptApp“, you can call it whatever you want) to the powerpoint application

presentation = pptApp.Presentations.Add()

This line adds a presentation to the Presentations collection.

Slide = presentation.Slides.Add(1, 12)

This adds a slide to the Slides collection of the presentation.

The numbers  1 & 12 refer the slide index and layout respectively; in this example index=1 and layout = “blank”. 

The “12” may appear a bit cryptic, and it is.  12 is the number which if you look in MSDN enumeration represents a blank layout: ppslidelayout-enumeration-powerpoint

It is possible to use numbers and “google” the MSDN enumeration however, using constants makes for much readable code.  See this blog for creating a list of constants How to Generate Python Modules of MS Office Constants:

E.G: Slide = presentation.Slides.Add(1, MSOconstants.MSPPT.ppLayoutBlank).  The constant is ppLayoutBlank (the MSOconstants.MSPPT is the folder and python module where I have the constants held)

myDiamond = slide.Shapes.AddShape(4, Top=100,Left=100, Width=20, Height=20)

This lines creates a diamond (enumeration is the number 4; MSOconstants.MSO.msoShapeDiamond), 100 pixles from the top, 100 pixels from the left and 20×20 pixels in size.

presentation.SaveAs(“I:\\MyPythonWorkArea\\jupyterLab\\myPowerPoint”,1)

This line saves the presentation as “myPowerPoint” and the 1 is enumeration of “powerpoint” format.

 

 

There are a number of variations but this works (you can read more here pywin32-docs-win32com-QuickStartClientCom)

See Reference Section for source material I found useful.