Driving Excel and PowerPoint with C#

C# and .Net are great for getting Excel and PowerPoint to do things for you, but there are a few tricks that new players will need:

• Use System.Type.Missing for unused optional parameters, not null.
• While making calls, set Thread.CurrentThread.CurrentCulture to new System.Globalization.CultureInfo("en-US"). This can help avoid the dreaded 0x800A03EC HRESULT.
• Use Visual Studio 2005 or later to ensure that hidden copies of Excel close when you stop using them.
• Office calls will occasionally fail because the target application is busy. To retry calls you need to implement IOleMessageFilter.
• If copying charts from Excel to (for example) PowerPoint then you want to stop Excel warning you about the large contents of the clipboard when it closes: excel.CutCopyMode = (XlCutCopyMode)0
• You can get a list of open Excel Workbooks or PowerPoint Presentations using the Running Objects Table (see code below). Note that the Running Objects Table contains both application objects and Worksheets/Presentations, but Presentation instances started by the user may not appear. To get around this, iterate through the Presentations collection of the PowerPoint.Application object.
• Some operations will fail because the user has (for example) a modal dialog open. The only way I know around this is to experimentally determine which calls might fail and present a useful error message to your user when it occurs.
• You can stop Excel querying the user when you attempt to delete Worksheets by setting excel.DisplayAlerts = false.
• To copy/paste live charts into PowerPoint use chart.ChartArea.Copy() and ppt.ActiveWindow.View.Paste()
  

Running Objects Table

struct RunningObject
{
public string name;
public object o;
}

// Returns the contents of the Running Object Table (ROT), where
// open Microsoft applications and their documents are registered.
List GetRunningObjects()
{
// Get the table.
List res = new List();
IBindCtx bc;
CreateBindCtx(0, out bc);
IRunningObjectTable runningObjectTable;
bc.GetRunningObjectTable(out runningObjectTable);
IEnumMoniker monikerEnumerator;
runningObjectTable.EnumRunning(out monikerEnumerator);
monikerEnumerator.Reset();

// Enumerate and fill our nice dictionary.
IMoniker[] monikers = new IMoniker[1];
IntPtr numFetched = IntPtr.Zero;
List names = new List();
List books = new List();
while (monikerEnumerator.Next(1, monikers, numFetched) == 0)
{
  RunningObject running;
  monikers[0].GetDisplayName(bc, null, out running.name);
  runningObjectTable.GetObject(monikers[0], out running.o);
  res.Add(running);
}
return res;
}

To use it:

foreach (RunningObject running in GetRunningObjects())
{
if (running.o is Excel.Workbook)
   MessageBox.Show("Found "+running.name);
}