Automate a Microsoft software with Powershell

Read my intro to Powershell yet?

Anyway, what I’m about to explain here assumes that you have a basic understanding of Powershell, so you should read a real and complete tutorial on the subject.

To control a software with Powershell, you must first put your hand on it. The easy way is to start it yourself and put the result in a variable, such as shown here:

$x = New-Object -com Excel.Application

Than you can make it visible:

$x.Visible = $true

… and change some value in an active cell:

$a.ActiveCell.Value2 = "x"

Basic Powershell 101 (which should blow your mind, if you never saw it before).

But how can you get a grip on an application that is already running? Say I have a Visio document and need to add automatically some little things in it. In this case, you can store the Visio application with the following:

$visio = [Runtime.InteropServices.Marshal]::GetActiveObject("Visio.Application")

This was the only really tricky part. Everything else can be found in the Visio Automation Object Model Reference.

Obtaining the Visio application would be much easier for future uses with a function:

function get-visio {[Runtime.InteropServices.Marshal]::GetActiveObject("Visio.Application")}

Now let’s try our shiny new methods. How can I publish a page to PDF from Powershell? With the ExportAsFixedFormat method. This method is applicable to a Visio’s document. How do we select one?

$visio_doc = $visio.activedocument

Or in a function:

function get-visiodoc {Param ($visio_instance)
  $visio_instance.activedocument}

This way, we can obtain the active document with the following:

$visio_doc = (get-visiodoc (get-visio))

And now the method itself:

$visio_doc.ExportAsFixedFormat(1,"test-publish.pdf",1,1,1,1,$false,$true,$false,$false,$true)

This will export a single page PDF named “test-publish.pdf”. Ok, not really useful in itself. What if we want to export every page from the document in as many PDF? This is becoming quite a clicking-heavy task for a menu user. Not with Powershell!

Start with a single page publish function:

function export-page-pdf {param ($visio_page, $visio_path = "")
    $filename = $visio_path + $visio_page.name + ".pdf"
$page_to_export = $visio_page.index
    (get-visiodoc (get-visio)).exportasfixedformat(1,$filename,1,1,$page_to_export,$page_to_export,$false,$true,$false,$false,$true)}

Notice the optional argument $visio_path.

And now a looping function to publish every single page of the document:

function export-document-pdf {
    $active_document = (get-visio).activedocument
$document_path = $active_document.path
$active_document.pages | foreach-object {export-page-pdf $_ $document_path }}

Tada! The export-document-pdf function doesn’t require any argument, so simply calling it will result in every page being published in PDF! You can even save the function and pin it to your taskbar. Next time you want to export a document, “click”, done!

With this example and the Visio Automation Object Model Reference, you should be able to do great things! Godspeed!

On Automation

The drones

They are everywhere. Unless you live in a post-apocalyptic bunker with years worth of canned food, you will see the “drones”. Those are individuals who don’t care to do the same things hundreds of times a day.

They will go home at night, proud of their hard work. They think a paycheck should be in proportion to the time they worked on a job, rather than on the job itself.

Automation is way of thinking

Everyone is more or less a drone, even the most active Emacs user. To overcome this “default” mode, you must constantly stop, look at what you are doing, and decide if there’s a better way to do it. My personal experience: whenever I do stop and ask myself “Surely there’s a better way to do this?”, there is indeed!

I connected to IRC like a drone for weeks before it occurred to me I could simply enter my login and favorite channels in a function to connect to them automatically by a simple M-x erc-connect. By the way, IRC is an indispensable productivity tool. Got a question about a particular software? Join the channel and ask it to dozens of experts.

Automation makes you look bad

This claim will surely appear to be insane to most of you. How could being more productive makes you look bad?

Imagine two colleagues: Mark and Jason. Both have the same task to do in a given day. Let’s say Mark finds a way to automate his task and complete it in 30 min instead of 8 hours (quite an accomplishment, but bear with me a little longer). Now suppose he decides to play to the last Mario on his Nintendo 3DS for the rest of the day. Even if he does the same amount of job as Jason in the day, everyone else will think he is lazy. To be a good employee, you must always look busy. More than busy in fact, you must always be running everywhere!

The same goes for organization: if you are organized and on schedule, nobody notices you. However, if you are late and need to disturb everyone to finally getting things done, people will tend to think “Whoah, look at all the efforts he’s doing to get this piece of info! He’s bothering all these poor souls just for me!” Think I’m crazy? How often have you asked for a document to someone and been amazed at how fast they sent it to you? Now, would you have the same level of astonishment for the individual who makes sure you already have it, or don’t even need it? No! Never!

And a last one just for fun: If you solve a problem before it even occurs, your are invisible. If you wait until it shows its ugly head and then solve it, you are an hero.

Why bother then?

Clearly automating things isn’t to our advantage then! Why bother? First of all, because not everyone fall in the look-busy trap. Other “productive” individual are able to see through it. Kinda like the blub paradox, from Paul Graham’s Beating the Averages. Want to work with the bests? Start by being one of those bests!

Second reason: to make interesting stuff! If you do the same thing again and again, you brain will rot! Letting the machine do the boring stuff goes a long way in making your job enjoyable. I’m way happier since I stopped counting the components needed for my projects!

Finally, as I stated before, automation is a way of thinking. Once it becomes a habit, it will not only transpire at work, but also in your personal life. This is a real motivation, as every gain in time you make is yours to keep.