Marshal.GetActiveObject() Preventing Excel from being Released?
Marshal.GetActiveObject() Preventing Excel from being Released?
Marshal.GetActiveObject() Preventing Excel from being Released?
Marshal.GetActiveObject() Preventing Excel from being Released?
Marshal.GetActiveObject() Preventing Excel from being Released?
Marshal.GetActiveObject() Preventing Excel from being Released? Marshal.GetActiveObject() Preventing Excel from being Released? Marshal.GetActiveObject() Preventing Excel from being Released? Marshal.GetActiveObject() Preventing Excel from being Released? Marshal.GetActiveObject() Preventing Excel from being Released? Marshal.GetActiveObject() Preventing Excel from being Released? Marshal.GetActiveObject() Preventing Excel from being Released? Marshal.GetActiveObject() Preventing Excel from being Released?
Marshal.GetActiveObject() Preventing Excel from being Released? Marshal.GetActiveObject() Preventing Excel from being Released?
Marshal.GetActiveObject() Preventing Excel from being Released?
Go Back  Xtreme Visual Basic Talk > > > Marshal.GetActiveObject() Preventing Excel from being Released?


Reply
 
Thread Tools Display Modes
  #1  
Old 05-17-2010, 05:41 PM
Colin Legg's Avatar
Colin Legg Colin Legg is offline
Out Of Office

Retired Moderator
* Expert *
 
Join Date: Mar 2005
Location: London, UK
Posts: 3,402
Default Marshal.GetActiveObject() Preventing Excel from being Released?


Okay... day 3 using C# 2010 and I'm stuck with a problem.

I've created a simple Windows Forms Application and the idea is I want to identify all instances of Excel, save any open workbooks to my desktop and close all the Excel instances down.

My form has a single button.

Code:
using System.Diagnostics;
using System.Runtime.InteropServices;

//reference to Excel 14.0 object library required
using Excel = Microsoft.Office.Interop.Excel;
Code:
        private void button1_Click(object sender, EventArgs e)
        {
            //variable to reference the open Excel applications
            Excel.Application xlApp = null;

            //constant for where we want to save down the workbooks
            const string filePath = @"C:\Users\Colin\Desktop\";

            while (true)
            {
                try
                {
                    xlApp = (Excel.Application)Marshal.GetActiveObject("Excel.Application");
                    xlApp.DisplayAlerts = false;
                    xlApp.EnableEvents = false;
                }
                catch (Exception)
                {
                    //assume no Excel Applications are open
                    MessageBox.Show("All done", "All done", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    break;
                }
                finally
                {
                    foreach (Excel.Workbook xlWkb in xlApp.Workbooks)
                    {
                        //save and close each workbook
                        string fileExtension;

                        switch (xlWkb.FileFormat)
                        {
                            case Excel.XlFileFormat.xlOpenXMLWorkbook:
                                fileExtension = ".xlsx";
                                break;
                            case Excel.XlFileFormat.xlOpenXMLWorkbookMacroEnabled:
                                fileExtension = ".xlsm";
                                break;
                            default:
                                fileExtension = ".xls";
                                break;
                        }
                        

                        xlWkb.SaveAs(Filename:
                            (filePath + xlWkb.Name + DateTime.Now.ToString("yy MM dd HHmmss fff") + fileExtension));

                        xlWkb.Close(SaveChanges: false);
                        //Marshal.FinalReleaseComObject(xlWkb);
                    }

                    //clean up, release and quit the Excel application
                    GC.Collect();
                    GC.WaitForPendingFinalizers();
                    GC.Collect();
                    GC.WaitForPendingFinalizers();

                    xlApp.Quit();
                    Marshal.FinalReleaseComObject(xlApp);
                }
            }
        }
I'm pretty new to this C# business so hints on the following two possible issues I've identified would be much appreciated:
  • The ForEach loop doesn't seem to be successfully looping through the XlApp.Workbooks collection as I was hoping.
  • It seems that I can't quit each Excel application which, with my current code structure, is causing an infinite loop. My suspicion is that Marshal.GetActiveObject() is locking it up and preventing it from closing?


If nothing obvious jumps out then I might try the alternative method by Andrew Whitechapel mentioned and linked to on this thread.

Thanks for any help...
Reply With Quote
  #2  
Old 05-17-2010, 08:10 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hey Colin,

Welcome to C# and VS 2010! Very psyched to see it.

I don't see anything wrong with your code. I have some stylistic advice, but no real corrections. I'm not sure why you're having problems.

Here's how I would suggest cleaning up your code a bit:
Code:
        /// <summary>
        ///   Gets an active <see cref="T:Excel.Application"/> instance from the
        ///   running object table (ROT), if available; returns 
        ///   <see langword="null"/> otherwise.
        /// </summary>
        Excel.Application GetExcelObject()
        {
            try
            {
                return (Excel.Application)Marshal.GetActiveObject("Excel.Application");
            }
            catch (Exception)
            {
                return null;
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            // Note: constants use 'PascalCase' in C#:
            const string FilePath = @"C:\Users\Colin\Desktop\"; 

            Excel.Application xlApp = GetExcelObject();

            while (xlApp != null)
            {
                xlApp.DisplayAlerts = false;
                xlApp.EnableEvents = false;
   
                //save and close each workbook
                foreach (Excel.Workbook xlWkb in xlApp.Workbooks)
                {
                    string fileExtension;

                    switch (xlWkb.FileFormat)
                    {
                        case Excel.XlFileFormat.xlOpenXMLWorkbook:
                            fileExtension = ".xlsx";
                            break;
                        case Excel.XlFileFormat.xlOpenXMLWorkbookMacroEnabled:
                            fileExtension = ".xlsm";
                            break;
                        default:
                            fileExtension = ".xls";
                            break;
                    }

                    string fileFullName = 
                        FilePath + xlWkb.Name + DateTime.Now.ToString("yy MM dd HHmmss fff") + fileExtension;

                    xlWkb.SaveAs(Filename: fileFullName);

                    xlWkb.Close(SaveChanges: false);
                    Marshal.FinalReleaseComObject(xlWkb);
                }

                // Only need to call GC.Collect() and GC.WaitForPendingFinalizers() once unless using VSTO:
                GC.Collect();
                GC.WaitForPendingFinalizers();

                xlApp.Quit();
                Marshal.FinalReleaseComObject(xlApp);

                // Get next Excel.Application objcet (if available).
                xlApp = GetExcelObject();
            }

            MessageBox.Show("Done!");
        }
I think that the idea of 'while(true)' and relying on a 'break' statement kind of scares me. Logically, it's fine, but I think it's easier to understand if the exit condition is stipulated within the 'while' statement itself, e.g., 'while(xlApp != null)'.

This, however, requires that we try to get the Ecxel object via 'Marshal.GetActiveObject' twice: once before the loop begins, and then again after. Since this call needs the protection of a try-catch block, I moved this out to a separate method to prevent redundancy.

C# coding convention is to use 'PascalCase' for constants, so I renamed 'filePath' to be 'FilePath'. (Very picky I know!)

I un-commented out the line Marshal.FinalReleaseComObject(xlWkb). My guess is that nothing was working, so you tried commenting this out, but this line should stay. (It's not critical because the calls to 'GC.Collect' and 'GC.WaitForPendingFinalizers' should take care of it anyway, but if you're having trouble releasing, then the call to 'Marshal.FinalReleaseComObject' can only help.)

I removed one set of calls to 'GC.Collect' and 'GC.WaitForPendingFinalizers' because you only need to call this twice if using VSTO. (VSTO seems to wrap Excel objects with its own, and the VSTO objects have finalizers... So an unreferenced VSTO object requires two calls to 'GC.Collect' and 'GC.WaitForPendingFinalizers' in order to be released. This is not the case, however, when VSTO is not involved.)

That's about all I can think of. I don't know if any of this will help. I tend to doubt it. Unfortunately, I cannot even test your code, because I have an Excel add-in that's in the process of being debugged that is throwing an exception at startup, which is apparently preventing the Excel instance from being loaded on the ROT. And it would be a bit tough for me right now to uninstall the add-in (too complex to explain!) So, sorry, I can't actually test your code...

But it looks like it should work. If all else fails, after closing down the workbooks, etc., you can call Process.Kill. Process.Kill is a last-resort situation, but if you've already closed down the workbooks within it and then called Application.Quit, then there really is no risk to calling it that I can think of. (The usually-stated risk is that the file reservation lock could remain on the open workbooks if the Excel application is killed instead of being closed down properly. But if you call Process.Kill after you've taken all the correct steps first, then everything is fine as far as I can tell. I do it all the time.)

The code for Andrew Whitechapel's article does work 100% fine as-is. I've tried it myself. So you could always go that route.

Oh, one last thing, looping through the Workbooks using 'forech' should work just fine. Try out the following code as a test:

Code:
        private void AutomateExcel()
        {
            Excel.Application excelApp = new Excel.Application();
            excelApp.Visible = true;

            excelApp.Workbooks.Add();
            excelApp.Workbooks.Add();
            excelApp.Workbooks.Add();

            foreach (Excel.Workbook wb in excelApp.Workbooks)
            {
                MessageBox.Show("'wb.Name' = " + wb.Name);
            }

            MessageBox.Show("Ready to clean up?");

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

            excelApp.Quit();
            Marshal.FinalReleaseComObject(excelApp);
        }
Now, related to the above, I'm hoping that you can iterate the workbooks via 'foreach' with no problems. If not, then something is very wrong... If it's fine, then this could help you understand what's going on with your original code.

This code, however, *hangs* for me as written. This tends to happen when running the debugger hosted within Visual Studio. This is because the debugger will hold onto variables past its required lifetime so that the user can hover the mouse to see the value. This is an awesome feature, but bad when trying to release objects.

Running the compiled debug version of the EXE also does not release. The compiled release version of the EXE, however, runs clean and releases perfectly.

So this is something to keep in mind when testing for releasing: it's great if it can release in debug mode and/or while running hosted in VS, but the real test is to see if it will release when running the compiled release version of the EXE. Fortunately, it's actually much easier to have the COM objects release when running in release mode.

I also tried the following to see if I could get it to release when running hosted within the VS debugger by adding Marshal.FinalReleaseComObject(wb) to the 'foreach' loop:

Quote:
foreach (Excel.Workbook wb in excelApp.Workbooks)
{
MessageBox.Show("'wb.Name' = " + wb.Name);
Marshal.FinalReleaseComObject(wb);
}
But it didn't help. Oh well. At least it releases when compiled as a release EXE, which is what matters.

-- Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb

Last edited by Mike Rosenblum; 05-17-2010 at 08:16 PM.
Reply With Quote
  #3  
Old 05-18-2010, 02:45 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline
Out Of Office

Retired Moderator
* Expert *
 
Join Date: Mar 2005
Location: London, UK
Posts: 3,402
Default

Thanks for the reply, Mike.
Quote:
C# coding convention is to use 'PascalCase' for constants, so I renamed 'filePath' to be 'FilePath'.
Ah thanks - that one slipped through the net! The conventions and syntax are quite alien to me at the moment. I must say that VS 2010 is an absolute dream and everso helpful with the syntax. At this early stage I couldn't possibly have written even this much without it nursing me through the process. One thing that's annoying is I keep on forgetting to include a ";" at the end of each statement (force of habit from VBA)... is there a key shortcut to go to the end of the previous line? At the moment I'm using UpArrow and End.

Quote:
.....
This code, however, *hangs* for me as written. This tends to happen when running the debugger hosted within Visual Studio. This is because the debugger will hold onto variables past its required lifetime so that the user can hover the mouse to see the value. This is an awesome feature, but bad when trying to release objects.
I'd only tried it in debug - it didn't even occur to me that the compiled release version of the EXE may perform as expected. I'll implement your suggestions and give it a go - it'll have to wait until this evening though... no VS at work!

Cheers
Reply With Quote
  #4  
Old 05-18-2010, 01:36 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Quote:
Originally Posted by Colin Legg View Post
Quote:
Originally Posted by Mike R
C# coding convention is to use 'PascalCase' for constants, so I renamed 'filePath' to be 'FilePath'.
Ah thanks - that one slipped through the net! The conventions and syntax are quite alien to me at the moment.
I'm sure! Don't sweat it.

You wouldn't want it for casual coding, but if you ever start a larger project, you should probably install StyleCop and FxCop at the start. They can really help with enforcing proper coding standards. But I wouldn't use it when first learning -- their rules will drive you crazy when you are first starting out. But keep them in mind.

Quote:
I must say that VS 2010 is an absolute dream
Yeah, it really is.

Quote:
At this early stage I couldn't possibly have written even this much without it nursing me through the process. One thing that's annoying is I keep on forgetting to include a ";" at the end of each statement (force of habit from VBA)...
LOL, yeah, I hear you. I've been using C# for many years now and I *still* make that mistake at times. The compiler really has a tough time with anything past the missing semicolon, although the more recent versions are doing better with it.

Quote:
Is there a key shortcut to go to the end of the previous line?
I think maybe UpArrow and End?

Quote:
At the moment I'm using UpArrow and End.
Yep! LOL.

No worries, you'll stop doing this so much in no time...

Quote:
I'd only tried it in debug - it didn't even occur to me that the compiled release version of the EXE may perform as expected.
Yes, its not obvious in any way, but when the exe runs in VS, it runs via the VS host, and the host itself maintains references to variables, etc, that are reported in the Locals window, when the user hovers the mouse over a variable, etc. These values can be of use to the programmer when running in the debugger past the last point where the code actually accesses them and would ordinarily be able to release them.

Even running an exe unhosted in debug mode will hold onto variables longer than when running an exe compiled in release mode. I'm not sure how/why this happens but it definitely does.

Quote:
I'll implement your suggestions and give it a go - it'll have to wait until this evening though... no VS at work!
Cool, let us know how it goes!

-- Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #5  
Old 05-18-2010, 02:18 PM
Colin Legg's Avatar
Colin Legg Colin Legg is offline
Out Of Office

Retired Moderator
* Expert *
 
Join Date: Mar 2005
Location: London, UK
Posts: 3,402
Default

Hey Mike,

Your cleaner code works much better than my orginal hash!

In debug mode, if I run the code using F5 then it only manages to close one Excel application per button click. If I *slowly* step through the code using F11 then it saves all workbooks and closes all applications successfully.

If I run the compiled release version then unfortunately it also has issues. Like in debug mode (F5), it seems to only be able to close one Excel application per button click. There's obviously a timing issue. It'll be a good exercise for me to put my limited VS 2010 debugging skills to the test!

Thanks for your help...
Reply With Quote
  #6  
Old 05-18-2010, 02:24 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Quote:
Originally Posted by Colin Legg View Post
Your cleaner code works much better than my orginal hash!
Ok, good to hear... But how so, exactly?

Quote:
In debug mode, if I run the code using F5 then it only manages to close one Excel application per button click. If I *slowly* step through the code using F11 then it saves all workbooks and closes all applications successfully.

If I run the compiled release version then unfortunately it also has issues. Like in debug mode (F5), it seems to only be able to close one Excel application per button click. There's obviously a timing issue. It'll be a good exercise for me to put my limited VS 2010 debugging skills to the test!
Are you describing how the original code runs, or the updated version?
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #7  
Old 05-18-2010, 02:40 PM
Colin Legg's Avatar
Colin Legg Colin Legg is offline
Out Of Office

Retired Moderator
* Expert *
 
Join Date: Mar 2005
Location: London, UK
Posts: 3,402
Default

Sorry for not being clear enough, Mike.
Quote:
Ok, good to hear... But how so, exactly?
It's much better because it manages to close one instance per run - my original wasn't able to close any.
Quote:
Are you describing how the original code runs, or the updated version?
I was describing the updated version. Marshal.GetActiveObject("Excel.Application") can't seem to find the next open Excel instance unless I F11 through the code.



I'll do some work on it and let you know if I get anywhere!

Cheers

Last edited by Colin Legg; 05-18-2010 at 03:01 PM.
Reply With Quote
  #8  
Old 05-18-2010, 04:03 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Quote:
Originally Posted by Colin Legg View Post
I was describing the updated version. It's much better because it manages to close one instance per run - my original wasn't able to close any.
LOL, so we're up to only one, eh?

Quote:
Marshal.GetActiveObject("Excel.Application") can't seem to find the next open Excel instance unless I F11 through the code.
Hmmm... just guessing, but this sounds like it needs some kind of time delay for the process to fully die out and/or for the running object table (ROT) to be updated.

Three thoughts here:

(1) Try putting in a sleep for a few seconds. If this works, try to see what the minimum # of seconds that you can reliably get away with.

(2) Instead of guessing how long you need to sleep, after using GetObject to get your Excel instance, but before shutting it down, utilize the Process class to get all the processes by name via the static Process.GetProcessesByName method, and then iterate each one until you find the process whose Process.MainWindowHandle matches the 'Excel.Application.Hwnd' of the Excel.Application that you are using.

Once you have your process, you can shutdown Excel as you currently do, but then wait until the Process.HasExited property returns 'true'. At this point, you should be safe to move on.

(3) Use the Process class to enumerate all of the Excel processes in the first place (instead of using GetObject) and then make use of Andrew Whitechapel's code to access the Excel object for each as described here.

Quote:
I'll do some work on it and let you know if I get anywhere!
Cool, let us know!

,
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #9  
Old 05-22-2010, 04:42 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline
Out Of Office

Retired Moderator
* Expert *
 
Join Date: Mar 2005
Location: London, UK
Posts: 3,402
Default

Quote:
LOL, so we're up to only one, eh?
LOL... I'm afraid so!

Quote:
Hmmm... just guessing, but this sounds like it needs some kind of time delay for the process to fully die out and/or for the running object table (ROT) to be updated.
I think your guess is spot on, as explained on [1] below.

Quote:
(1) Try putting in a sleep for a few seconds. If this works, try to see what the minimum # of seconds that you can reliably get away with.
This did the trick. I added these lines of code in:
Code:
using System.Threading;
Code:
Marshal.FinalReleaseComObject(xlApp);

Thread.Sleep(1000);
When I ran the code (in debug) it closed all the Excel instances perfectly so, as you suggested, it was a timing issue. Using Sleep() like this isn't entirely satisactory because it might delay the program longer than necessary and a constant interval might not be entirely reliable. Onto suggestion (2)...

Quote:
(2) Instead of guessing how long you need to sleep, after using GetObject to get your Excel instance, but before shutting it down, utilize the Process class to get all the processes by name via the static Process.GetProcessesByName method, and then iterate each one until you find the process whose Process.MainWindowHandle matches the 'Excel.Application.Hwnd' of the Excel.Application that you are using.

Once you have your process, you can shutdown Excel as you currently do, but then wait until the Process.HasExited property returns 'true'. At this point, you should be safe to move on.
Okay, so my attempt at this is as follows.

I added a method called GetExcelProcess:
Code:
        Process GetExcelProcess(Excel.Application xlApp)
        {
            Process[] excelProcesses = Process.GetProcessesByName("Excel");

            foreach (Process excelProcess in excelProcesses)
            {
                if (xlApp.Hwnd.Equals(excelProcess.MainWindowHandle.ToInt32()))
                {
                    return excelProcess;
                }
            }
            return null;
        }
Then I amended the cleanup section of the code to:
Code:
//Find the currently referenced Excel process so we can be sure when it has been properly killed
Process xlProcess = GetExcelProcess(xlApp);

// Only need to call GC.Collect() and GC.WaitForPendingFinalizers() once unless using VSTO:
GC.Collect();
GC.WaitForPendingFinalizers();

xlApp.Quit();
Marshal.FinalReleaseComObject(xlApp);

while (!xlProcess.HasExited)
{
    Thread.Sleep(50);
    Application.DoEvents();
}

// Get next Excel.Application objcet (if available).
xlApp = GetExcelObject();
This seems to work.

Before I move onto trying suggestion (3), can you suggest how this could be written better? In particular, is there a cleaner way of 'waiting' until the current Excel process has exited (rather than using a while loop with sleep)?

Cheers
Reply With Quote
  #10  
Old 05-22-2010, 05:35 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Quote:
Originally Posted by Colin Legg View Post
LOL... I'm afraid so!
I think your guess is spot on, as explained on [1] below.

This did the trick. I added these lines of code in:
Code:
using System.Threading;
Code:
Marshal.FinalReleaseComObject(xlApp);

Thread.Sleep(1000);
Cool, we're rolling...


Quote:
Originally Posted by Colin
Using Sleep() like this isn't entirely satisactory because it might delay the program longer than necessary and a constant interval might not be entirely reliable. Onto suggestion (2)...

Okay, so my attempt at this is as follows.

I added a method called GetExcelProcess:
Code:
 
        Process GetExcelProcess(Excel.Application xlApp)
        {
            Process[] excelProcesses = Process.GetProcessesByName("Excel");

            foreach (Process excelProcess in excelProcesses)
            {
                if (xlApp.Hwnd.Equals(excelProcess.MainWindowHandle.ToInt32()))
                {
                    return excelProcess;
                }
            }
            return null;
        }
This method looks very good. Just one minor comment though. If no Excel process is found this way then I think an exception should be thrown because there should be no way for you to *not* find a running Excel process if the method is passed in one!

I would suggest the following minor improvement:
Code:
Process GetExcelProcess(Excel.Application xlApp)
{
    if (xlApp == null)
    {
        throw new ArgumentNullException(
            "xlApp",
            "The argument passed in for the 'xlApp' parameter was null, which is invalid.");
    }

    Process[] excelProcesses = Process.GetProcessesByName("Excel");

    foreach (Process excelProcess in excelProcesses)
    {
        if (xlApp.Hwnd == excelProcess.MainWindowHandle.ToInt32())
        {
            return excelProcess;
        }
    }

    throw new InvalidOperationException(
        "Unexplained operation of the 'Process' class: the Excel process could not be returned.");
}
Quote:
Originally Posted by Colin
Code:
while (!xlProcess.HasExited)
{
    Thread.Sleep(50);
    Application.DoEvents();
}

// Get next Excel.Application objcet (if available).
xlApp = GetExcelObject();
This seems to work.

Before I move onto trying suggestion (3), can you suggest how this could be written better? In particular, is there a cleaner way of 'waiting' until the current Excel process has exited (rather than using a while loop with sleep)?
In general, the way that you looped by waiting for the exit condition while calling Sleep and DoEvents is 100% correct and there would be no other way.

In this case, however, there is a Process.WaitForExit method that you can use. (Or the Process.WaitForExit(Int32)) overload that allows you to specify a time-out value.)

Give it a try, I think it should do it! Fingers crossed...

,
Mike

Edit by Colin: Tidied up tags
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb

Last edited by Colin Legg; 05-22-2010 at 06:02 AM.
Reply With Quote
  #11  
Old 05-22-2010, 06:30 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline
Out Of Office

Retired Moderator
* Expert *
 
Join Date: Mar 2005
Location: London, UK
Posts: 3,402
Default

Fantastic, Mike!

Here's the complete version with amendments:

Code:
using System.Diagnostics;
using System.Runtime.InteropServices;

//reference to Excel object library required
using Excel = Microsoft.Office.Interop.Excel;
Code:
        /// <summary>
        ///   Gets an active <see cref="T:Excel.Application"/> instance from the
        ///   running object table (ROT), if available; returns 
        ///   <see langword="null"/> otherwise.
        /// </summary>
        Excel.Application GetExcelObject()
        {
            try
            {
                return (Excel.Application)Marshal.GetActiveObject("Excel.Application");
            }
            catch (Exception)
            {
                return null;
            }
        }

        Process GetExcelProcess(Excel.Application xlApp)
        {
            Process[] excelProcesses = Process.GetProcessesByName("Excel");

            foreach (Process excelProcess in excelProcesses)
            {
                if (xlApp.Hwnd == excelProcess.MainWindowHandle.ToInt32())
                {
                    return excelProcess;
                }
            }

            throw new InvalidOperationException(
                "Unexplained operation of the 'Process' class: the Excel process could not be returned.");
        }


        private void button1_Click(object sender, EventArgs e)
        {
            // Note: constants use 'PascalCase' in C#:
            const string FilePath = @"C:\Users\Colin\Desktop\";
            const int MaxWait = 60000;

            Excel.Application xlApp = GetExcelObject();

            while (xlApp != null)
            {
                xlApp.DisplayAlerts = false;
                xlApp.EnableEvents = false;

                //save and close each workbook
                foreach (Excel.Workbook xlWkb in xlApp.Workbooks)
                {
                    string fileExtension;

                    switch (xlWkb.FileFormat)
                    {
                        case Excel.XlFileFormat.xlOpenXMLWorkbook:
                            fileExtension = ".xlsx";
                            break;
                        case Excel.XlFileFormat.xlOpenXMLWorkbookMacroEnabled:
                            fileExtension = ".xlsm";
                            break;
                        default:
                            fileExtension = ".xls";
                            break;
                    }

                    string fileFullName =
                        FilePath + xlWkb.Name + DateTime.Now.ToString("yy MM dd HHmmss fff") + fileExtension;

                    xlWkb.SaveAs(Filename: fileFullName, FileFormat: xlWkb.FileFormat);

                    xlWkb.Close(SaveChanges: false);
                    Marshal.FinalReleaseComObject(xlWkb);
                }

                //Find the currently referenced Excel process so we can be sure when it has been properly killed
                Process xlProcess = GetExcelProcess(xlApp);

                // Only need to call GC.Collect() and GC.WaitForPendingFinalizers() once unless using VSTO:
                GC.Collect();
                GC.WaitForPendingFinalizers();

                xlApp.Quit();
                Marshal.FinalReleaseComObject(xlApp);

                //wait for the process to completely close before moving on
                xlProcess.WaitForExit(MaxWait);

                // Get next Excel.Application objcet (if available).
                xlApp = GetExcelObject();
            }

            MessageBox.Show("Done!");
        }
Thank you!


For the GetExcelProcess() method, in addition to adding a line to throw an exception if the Excel process couldn't be found, I noticed you also changed this:
Code:
//changed from
if (xlApp.Hwnd.Equals(excelProcess.MainWindowHandle.ToInt32()))


//to this
if (xlApp.Hwnd == excelProcess.MainWindowHandle.ToInt32())
Is the latter just a touch more efficient or are there other points I need to be aware of?


Cheers

Last edited by Colin Legg; 05-22-2010 at 06:43 AM.
Reply With Quote
  #12  
Old 05-25-2010, 12:00 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hey Colin,

Sorry for the late reply, been crazy busy...

Quote:
Originally Posted by Colin Legg View Post
Here's the complete version with amendments: ... Thank you!
No problem, looks like you have a winner.

Quote:
Originally Posted by Colin
For the GetExcelProcess() method, in addition to adding a line to throw an exception if the Excel process couldn't be found, I noticed you also changed this:
Code:
//changed from
if (xlApp.Hwnd.Equals(excelProcess.MainWindowHandle.ToInt32()))

//to this
if (xlApp.Hwnd == excelProcess.MainWindowHandle.ToInt32())
Is the latter just a touch more efficient or are there other points I need to be aware of?
I guess I did that without even thinking. They are both the same, really. It's just a readability issue. E.g., would you generally use:

Code:
if (x = 5)
{
   // Do stuff here
}
or

Code:
if (x.Equals(5))
{
   // Do stuff here
}
Same result, it's just that one looks more awkward. There are times to explicitly call .Equals(), typically when you don't know the types that you are comparing, but in general use the equals operator, where available. (Not all types will have the equals operator defined, but all will have an Equals method because Equals is a member of the Object type, from which all .NET types are derived.

There are, however, some rare edge-cases of which to be aware. E.g.:

Code:
    object x = 5;
    object y = 5;
    bool areEqual = x == y; // False!
The above is hard to grasp at first. What's happening is that 'x' and 'y' are 'object' types that box integers. So when you evaluate x == y, this is the same as calling '(object)x == (object)y' and since the 'object' type is a reference type the '==' does a reference comparison, and so is equivalent to calling Object.ReferenceEquals((object)x, (object)y). But since these two boxed objects are distinct, Object.ReferenceEquals((object)x, (object)y) will always return 'false' even though the values that they box are both the same.

Code:
    object x = 5;
    object y = 5;
    bool x.Equals(y); // True! :-)
The above works because the equals method is virtual and overridden by the integer class. So the call to x.Equals is a call to Int32.Equals() which knows how to compare itself to the integer value passed in, even if the value passed in is boxed as an object.

I know, the above looks like madness, but this is pretty rare stuff. One shouldn't be comparing objects like this, to be honest -- always make sure that both operands are of the same type, or are explicitly casted or converted to the same specific type before making the comparison.

Usually though, just use the '==' sign and don't worry about it.

- Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Marshal.GetActiveObject() Preventing Excel from being Released?
Marshal.GetActiveObject() Preventing Excel from being Released?
Marshal.GetActiveObject() Preventing Excel from being Released? Marshal.GetActiveObject() Preventing Excel from being Released?
Marshal.GetActiveObject() Preventing Excel from being Released?
Marshal.GetActiveObject() Preventing Excel from being Released?
Marshal.GetActiveObject() Preventing Excel from being Released? Marshal.GetActiveObject() Preventing Excel from being Released? Marshal.GetActiveObject() Preventing Excel from being Released? Marshal.GetActiveObject() Preventing Excel from being Released? Marshal.GetActiveObject() Preventing Excel from being Released? Marshal.GetActiveObject() Preventing Excel from being Released? Marshal.GetActiveObject() Preventing Excel from being Released?
Marshal.GetActiveObject() Preventing Excel from being Released?
Marshal.GetActiveObject() Preventing Excel from being Released?
 
Marshal.GetActiveObject() Preventing Excel from being Released?
Marshal.GetActiveObject() Preventing Excel from being Released?
 
-->