C# Automate PowerPoint Excel

Question

Hey I'm automating PowerPoint and Excel from a C# WinForms application; what I do is read slides from PowerPoint and save them in Excel and then quit both apps. Excel quits successfully but PowerPoints doesn't quits. The problem is when I convert first time it doesnt quits, but when I convert again it does.

Here is my code

try
{
    PowerPoint.Application ppApp;
    PowerPoint.Presentation ppPres;
    List<Company> companies = new List<Company>();

    ppApp = new PowerPoint.Application();
    ppApp.Visible = Microsoft.Office.Core.MsoTriState.msoTrue;
    ppApp.WindowState = Microsoft.Office.Interop.PowerPoint.PpWindowState.ppWindowMinimized;

    ppPres = ppApp.Presentations.Open(fileTxtBox.Text,
                                      Microsoft.Office.Core.MsoTriState.msoFalse,
                                      Microsoft.Office.Core.MsoTriState.msoFalse,
                                      Microsoft.Office.Core.MsoTriState.msoTrue);

    int slides = ppPres.Slides.Count;

    for (int slide = 1; slide <= slides; slide++)
    {
        int rows = 1;
        PowerPoint.Cell cell;
        int shape = 1;

        for (; shape < ppPres.Slides[slide].Shapes.Count; shape++)
        {
            if (ppPres.Slides[slide].Shapes[shape].HasTable == Microsoft.Office.Core.MsoTriState.msoTrue)
            {
                cell = ppPres.Slides[slide].Shapes[shape].Table.Cell(1, 1);

                if (cell.Shape.TextFrame.TextRange.Text.Trim().ToLower().Contains("realized"))
                {
                    rows = ppPres.Slides[slide].Shapes[shape].Table.Rows.Count;
                    break;
                }
            }
        }

        Company comp = new Company(rows);
        InitializeCompany(ref comp, ppPres.Slides[slide]);
        companies.Add(comp);
    }

    SaveInExcel(companies);

    ppPres.Close();
    ppPres = null;
    ppApp.Quit();
    ppApp = null;

    return;
}

catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}

finally
{
    GC.Collect();
    GC.WaitForPendingFinalizers();
}

Answer 1

It looks like you are making the Power Point application instance visible, but if not, it's possible that there is a dialog being presented that you don't see - perhaps a save changes dialog. If the app is running hidden, make it visible to see if any such dialogs are popping up, preventing Power Point from closing.

Answer 2

After invoking Quit() for your ppApp object try the following

System.Runtime.InteropServices.Marshal.ReleaseComObject(ppApp);

No promises however, it might work, or it might do nothing at all. Properly closing down Office COM stuff has always been a bit like voodoo for me.

Answer 3

This falls under the category of "Doctor, it hurts when I hold my arm like this" and my response under the category "stop holding your arm like that then". If you are committed to using COM interop, stop reading here and let me just wish you good luck.

===Still with me?====

COM interop with Office is voodoo as Matthew Ruston mentions. Having been down the COM interop road and been burned repeatedly, I eventually settled on Aspose's office manipulation tools, although there are plenty of others on the market. Any third party library we tried was more stable than using COM.

Before we stopped using COM interop, we had to restart the server involved repeatedly and the support calls were non-stop due to hung instances of one Office product or another. While not perfect, the third party library bugs at least were of a type that could be worked around and more importantly, provide months of uptime once we were done.

I don't miss support calls about "stuck" instances of Word, Outlook, Excel and the like. The exact source of them has remained a mystery: I had the same code on seeming the same deployment (i.e., from a Ghost image) and yet on one it would fail and the other would chug along like a champ. The number of variables involved just become absurd.

EDIT: Mike Rosenblum's linked article contains all the techniques that are supposed to fix the problem. Even after all of those considerations though (been there, done that) I find that Office apps like to hang around due to unrelated reasons. One factor that used to cause us trouble was antivirus applications which would hook into the process, or add-ins such as Adobe Acrobat.

Answer 4

Shutting down your Microsoft Office application can seem tricky to get right at first, but once you establish the correct order of operations, it's actually not very hard at all.

Releasing your MS Office application can be done safely and effectively in two stages:

(1) First release all the minor objects to which you do not hold a reference within a named variable. You do this via a call to GC.Collect() and then GC.WaitForPendingFinalizers() . Note that if you are using Visual Studio Tools for Office (VSTO), then you need to call this pair of commands twice in order to get the COM objects to successfully release. You are not using VSTO, however, so calling them once is sufficient.

(2) Then explicitly release the objects which you hold via a named variable using a call to Marshall.FinalReleaseComObject() on each variable you have.

Remember to explicitly release all variables that you have to COM components. If you miss even one, then your MS Office application will hang. In your code, you seem to have three named variables that hold a reference to your PowerPoint application: ppApp, ppPres, and cell.

Taking this all into account, I think that your cleanup should look something like the following, which makes use of using System.Runtime.InteropServices either within the namespace or at the top of the code document:

// using System.Runtime.InteropServices

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

Marshal.ReleaseComObject(cell);

ppPres.Close();
Marshal.ReleaseComObject(ppPres);

ppApp.Quit();
Marshal.ReleaseComObject(ppApp);

Give it a try, I think this should work for you... (If not, you might have to show even more of your code.) For further information, I give a detailed explanation on how to properly release an Excel application here:

How to properly clean up Excel interop objects in C#.

Hope this helps, let us know how it goes...

-- Mike

Answer 5

Here is a MSDN article that describes the problem and solution

http://msdn.microsoft.com/en-us/library/aa679807%28office.11%29.aspx

basically recomondation is to do(Note : two iterations of GC.Collect()

 GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.WaitForPendingFinalizers();