Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
8. August 2010 17:30

I am currently on a project which involves creating a PowerPoint VSTO add-in. I have very limited experience with PowerPoint automation, so before committing to the project, I thought it would be a good idea to explore a bit the object model, to gauge how difficult things could get, and I set to write a small PowerPoint add-in which would automatically translate slides. Sounds like a simple enough project, how difficult could it be?

Turns out, not too difficult, but not completely trivial either. I discovered quickly that the PowerPoint object model, unlike most Office applications, doesn’t have much (any?) documentation for the .Net developer; the best I found is the VBA PowerPoint 2007 developer reference, which gives a decent starting point to figure out what the objects are about. So I thought I would share my exploration of the PowerPoint jungle, and hopefully spare some trouble to other .Net developers.

## The plan

The objective is simple: write an add-in which allows the user to

• select a language to translate from, and a language to translate to,
• create a duplicate of the current slide, translating all the text and keeping the layout

The plan will be to use Google Translate to perform the translation. In order to do that, we will nedd to extract out all pieces of text that require translating.

## Finding all the text in a slide

Lets’ start by identifying where we have text in the current slide. Let’s first create a PowerPoint 2007 Add-in project in Visual Studio. To keep things simple for now, we will add a Ribbon control with a button, and when that button is clicked, we’ll start working on the current slide:

Double-click on the Button (I renamed my button translateButton) to generate an event handler for the Click event, and get the current Slide:

private void translateButton_Click(object sender, RibbonControlEventArgs e)
{
if (powerpoint.ActivePresentation.Slides.Count > 0)
{
var slide = (PowerPoint.Slide)powerpoint.ActiveWindow.View.Slide as PowerPoint.Slide;
}
}

More...

15. July 2010 12:14

Time to wrap this series on VSTO add-ins for Excel 2007. Now that we have a working application-level add-in, we want to deploy it on the user machine. There are two ways to do that: ClickOnce and Windows Installer. In this post, I will go over creating a basic installer using Windows installer with Visual Studio 2008. Very soon, we’ll have a VIP guest blogger who will tell you all you need to know about ClickOnce deployment and VSTO.

This post borrows heavily from the Microsoft white paper linked below, which is absolutely excellent. I mostly paraphrased it, focusing on the how and not the why. I strongly encourage you to go to the source and read it for more details:

Deploying a VSTO 3.0 Solution for Office 2007 System Using Windows Installer

The white paper comes with sample code, covering a few scenarios:

Note: the following applies to Office 2007 projects. If your add-in needs to run on Excel 2003, you should follow this guidance instead: Deploying VSTO Solutions Using Windows Installer (Part 2 of 2)

Surgeon General Warning: prolonged reading of material pertaining to msi deployment can cause drowsiness or confusion; absolutely no risk whatsoever of euphoria is to be expected.

This post is not going to be sexy. My goal is to have a check-list of what to do to get your add-in to install correctly. The steps require no thinking, and are frankly rather boring. I find some steps pretty obscure, and recommend patience and soothing music; you may consider also having  some sacrificial offering ready to appease the Great Installer Voodoo deity (a nice chicken will usually do).

We will start from where we left off, with a working add-in (download the add-in here). Let’s first fill in the fields describing our assembly, by right-clicking on the project:

ClearLines.Anakin > Properties > Application > Assembly information:

Next, let’s set the configuration to Release, so that we feed the optimized release version to the installer. Right-click on the Solution (not the add-in project), select Configuration Manager, and set ClearLines.Anakin to Release instead of Debug.

## More...

7. June 2010 10:23

Some time ago, I came across ExcelDNA, an open-source library designed to integrate .Net into Excel, via a  post by the Grumpy One, who described it as an interesting way to get Excel to talk to a compiled library. Sounds right down my alley, but I still managed to let 6 months pass until I finally tried it.

This reminded me of another post, by J-Walk this time, where he uses a random walk simulation in VBA to benchmark system performance. Back then, I ran the VBA code, and also the equivalent C# in a console app, out of curiosity: 11.38 seconds, vs. 2.73 seconds. Why not try the same experiment, and see if we can get the best of both worlds and bring some of the C# power into Excel via ExcelDNA?

So I created a Class Library, with the following method, a close equivalent to the VBA benchmark code:

public class Experiment
{
public static string RandomWalk()
{
var stopwatch = new Stopwatch();
stopwatch.Start();
var position = 0;
var random = new Random();
for (var run = 0; run < 100000000; run++)
{
if (random.Next(0, 2) == 0)
{
position++;
}
else
{
position--;
}
}
stopwatch.Stop();
var elapsed = (double)stopwatch.ElapsedMilliseconds / 1000d;
return "Position: " + position.ToString() + ", Time: " + elapsed.ToString();
}
}

More...

26. May 2010 06:55

I finally got to reviewing and scrubbing the code for the part 2 of my Excel 2007 VSTO tutorial; you can download the code here. Next chapter, we will venture into the joys of deployment.

In the meanwhile, please feel free to let me know in the comments what you think, like and dislike, and how I can make this better!

6. May 2010 09:27

Today’s post will be much lighter than the previous episode: we will display detailed information about the differences that have been found on the ComparisonView control. To do this, we will bind properties of the Difference to the control, and use a WPF Value Converter to dynamically format the control.

You can find the complete list of episodes, and a link to the source code of the Excel VSTO add-in tutorial here.

## Displaying how the cells differ

The add-in tracks cells where either the values or the formulas are different. We will simply display “side-by-side” the values and formulas of the cell that is being compared in the ComparisonView control:

To do this we need to provide a way to access the value and formula of each side of the comparison, so first we add the following properties to the Difference class:

public class Difference
{
public string OriginalValue
{
get; set;
}

public string OtherValue
{
get; set;
}

public string OriginalFormula
{
get; set;
}

public string OtherFormula
{
get; set;
}

More...

#### Need help with F#?

The premier team for
F# training & consulting.