VSTO PowerPoint/Excel Interaction

Question

Hi, I want to extract data from PowerPoint slides and then paste them into Excel workbook, where each slide in PowerPoint corresponds to a sheet in Excel workbook and vice-versa (from Excel to PowerPoint).

How do I do this? I extracted the data PowerPoint but now how do I make it send to data to Excel? What options/possibilities do I have?

I'm using VSTO with C#, please tell me solutions for both Office 2003/2007

Answer 1

Him Manzoor,

Your question is a little open ended, and I'm not really sure what kind of PowerPoint values you are attempting to place into Excel, but you could certainly use Excel Automation to achieve your goal. For example, something like this:

void AutomateExcelExample()
{
    Excel.Application excelApp = new Excel.Application();
    excelApp.Visible = true;

    Excel.Workbook workbook = excelApp.Workbooks.Add(Type.Missing);
    Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];
    Excel.Range range = worksheet.get_Range("A1", Type.Missing);

    range.set_Value(Type.Missing, "Hello World");

    MessageBox.Show("Intentional pause so you can see the result.");

    // Cleanup:
    GC.Collect();
    GC.WaitForPendingFinalizers();

    Marshal.FinalReleaseComObject(range);
    Marshal.FinalReleaseComObject(worksheet);

    workbook.Close(false, Type.Missing, Type.Missing);
    Marshal.FinalReleaseComObject(workbook);

    excelApp.Quit();
    Marshal.FinalReleaseComObject(excelApp);
}

In the example above, the code opens a new Excel application instance, makes the application visible (which you probably do not want to do, but can be useful when testing), opens a new workbook, and then assigns the string value "Hello World" into the cell A1 of the first worksheet in the workbook.

I don't know what kind of PowerPoint data you are retrieving, but an Excel cell can handle most standard value types such as string, double, bool, DateTime, etc.

This solution will work both with or without VSTO, and will work for Excel versions 2007 and above as well as for Excel versions 2003 and below.

Does this help get you started?

Mike

Answer 2

This sample is mainly correct except for the cleanup routine which is wrong...

The part

// Cleanup:    
GC.Collect();    
GC.WaitForPendingFinalizers();

needs to be moved to the end of this method like so..

Marshal.FinalReleaseComObject(range);    
Marshal.FinalReleaseComObject(worksheet);    
workbook.Close(false, Type.Missing, Type.Missing);    
Marshal.FinalReleaseComObject(workbook);    
excelApp.Quit();    
Marshal.FinalReleaseComObject(excelApp);

// move deterministic call to garbage collector to AFTER release
// of all COM objects.
GC.Collect();    
GC.WaitForPendingFinalizers();

GC.Collect();

This is because there is no point trying to finalise the queue of COM objects before most of those objects have even been pushed onto the finalisation queue. Likewise pre-emptively calling GC.Collect will just cause an extra traversal of the objects in memory which will have a negative performance impact and won't guarantee objects are cleaned up.

Also another problem with this sample, why are you using Set_Value and get_Range methods? this seems unnecessary.

In terms of the question, I think you will need to add some foreach statements to loop through the slides in the presentation object, and once you have collected the data you want from each, add a new slide within the scope of the current slide iteration.

i.e.

foreach slide in Presentation.Slides()  
{ 
  string data = getData(slide);
  Excel.WorkSheet worksheet =  workbook.Sheets.Add(Type.Missing);
  worksheet.Range("A1").Value = data;
}
// you will need to write the getData method yourself...

VB.NET side question... For the person asking about VB.Net sample of the above...

1. Remove the semi-colons.

2. Replace any instances of Type.Missing with BLANK (i.e. "no space")

3. Replace get_range() with .Range()

4. Replace set_value() with .Value()

5. Add a Imports Excel = Microsoft.Office.Interop.Excel statement to the top of your class.