CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 2 of 2

Thread: Excel crashes

  1. #1
    Join Date
    Jun 2010
    Posts
    1

    Angry Excel crashes

    A simple code to export data to excel
    <pre>

    public partial class Form1 : Form
    {

    Microsoft.Office.Interop.Excel.Application _objAppln;
    Workbook _objWorkBook;
    Workbooks _objWorkBooks;
    Worksheet _objWorkSheet;


    public Form1()
    {
    InitializeComponent();
    InitializeExcelObjectModel();
    }

    ~Form1()
    {
    DisposeExcelObjects();
    }

    void SaveExcel()
    {

    _objAppln.DisplayAlerts = false;//Since, we are using SaveFileDialog's overwrite prompt(control is on view).

    _objWorkBook.SaveAs("C:\\tmp.xls",
    XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, false, Type.Missing, XlSaveAsAccessMode.xlNoChange,
    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
    _objWorkBook.Close(true, "C:\\tmp.xls", false);
    _objAppln.DisplayAlerts = true;//restore back for other display alerts
    }

    private void InitializeExcelObjectModel()
    {

    _objAppln = new Microsoft.Office.Interop.Excel.Application(); // To initialize excel file
    //_objAppln.Visible = true;
    if (_objAppln != null)
    {
    _objWorkBooks = _objAppln.Workbooks;
    _objWorkBook = _objWorkBooks.Add(Type.Missing); // To add workbook with sheets in excel file
    _objWorkSheet = (Worksheet)_objAppln.ActiveSheet; // To get the current active sheet in excel file

    }
    }

    public void DisposeExcelObjects()
    {

    System.Runtime.InteropServices.Marshal.ReleaseComObject(_objWorkSheet);
    System.Runtime.InteropServices.Marshal.ReleaseComObject(_objWorkBook);
    System.Runtime.InteropServices.Marshal.ReleaseComObject(_objWorkBooks);
    System.Runtime.InteropServices.Marshal.ReleaseComObject(_objAppln);
    _objWorkSheet = null; _objWorkBooks = null; _objWorkBooks = null; _objAppln = null;
    }

    private void button1_Click(object sender, EventArgs e)
    {

    Range objRange=null;
    string cell1 = string.Empty, cell2 = string.Empty;
    string[] chars = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T" };


    for (int row = 1; row &lt; 1000; row++)
    {
    for (int column = 0; column &lt; 20; column++)
    {
    cell1 = chars[column] + row.ToString();
    objRange = _objWorkSheet.get_Range(cell1, cell1);
    objRange.Value2 = cell1;


    }
    }
    System.Runtime.InteropServices.Marshal.ReleaseComObject(objRange);
    objRange = null;
    SaveExcel();
    }
    }
    </pre>

    Step 1: Add the above code to a solution and add reference for Microsoft.Office.Interop.Excel.dll
    Step 2: Run the application and Execute the code in button1_Click.
    Step 3: While the processing is on, open any other excel file and just click on cells here and there.
    COM exception occurs. Anything I am doing wrong above?

    How to resolve?

  2. #2
    Join Date
    Oct 2007
    Location
    Fredericksburg, VA
    Posts
    41

    Re: Excel crashes

    Try using the following code, it looks like the interop/automation server is busy when you're trying to hit it because the dll's it needs are already in use.

    I made a wrapper function that basically takes any other function (in this case, your unsafe COM calls) as a delegate, and returns an object that you can then cast back to the type you need.

    The wrapper function checks to see if it gets a COM exception, and if it does, waits a slice, and tries again; you'll probably want to limit that to certain errors, or a certain number of retries, and have it throw the real error after 50 tries or something like that.

    Code:
    using System;
    using System.Windows.Forms;
    using Microsoft.Office.Interop.Excel;
    using System.Runtime.InteropServices;
    
    namespace ExcelExport
    {
        public partial class Form1 : Form
        {
            Microsoft.Office.Interop.Excel.Application _objAppln;
            Workbook _objWorkBook;
            Workbooks _objWorkBooks;
            Worksheet _objWorkSheet;
            public Form1()
            {
                InitializeComponent();
                InitializeExcelObjectModel();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                Range objRange = null;
                string cell1 = string.Empty, cell2 = string.Empty;
                string[] chars = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T" };
    
                for (int row = 1; row < 1000; row++)
                {
                    for (int column = 0; column < 20; column++)
                    {
                        //simple method call
                        cell1 = chars[column] + row.ToString();
                        objRange = (Range) COMExecute(() => _objWorkSheet.get_Range(cell1, cell1));
                        objRange.Value2 = cell1;
                    }
                }
                System.Runtime.InteropServices.Marshal.ReleaseComObject(objRange);
                objRange = null;
                SaveExcel();
    
            }
            ~Form1()
            {
                DisposeExcelObjects();
            }
    
            void SaveExcel()
            {
                _objAppln.DisplayAlerts = false;//Since, we are using SaveFileDialog's overwrite prompt(control is on view). 
                _objWorkBook.SaveAs("C:\\tmp.xls",
                XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, false, Type.Missing, XlSaveAsAccessMode.xlNoChange,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                _objWorkBook.Close(true, "C:\\tmp.xls", false);
                _objAppln.DisplayAlerts = true;//restore back for other display alerts 
            }
    
            private void InitializeExcelObjectModel()
            {
                _objAppln = new Microsoft.Office.Interop.Excel.Application(); // To initialize excel file 
                //_objAppln.Visible = true; 
                if (_objAppln == null) return;
                _objWorkBooks = _objWorkBooks = _objAppln.Workbooks;
                _objWorkBook = _objWorkBook = _objWorkBooks.Add(Type.Missing); // To add workbook with sheets in excel file 
                _objWorkSheet = (Worksheet) _objAppln.ActiveSheet; // To get the current active sheet in excel file
            }
    
            public void DisposeExcelObjects()
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(_objWorkSheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(_objWorkBook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(_objWorkBooks);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(_objAppln);
                _objWorkSheet = null; _objWorkBooks = null; _objWorkBooks = null; _objAppln = null;
            }
    
            delegate object COMWrapper();
            private object COMExecute(COMWrapper executeCommand)
            {
                bool executeFailed = true;
                while (executeFailed)
                {
                    try
                    {
                        return executeCommand();
                    }
                    catch (COMException ex)
                    {
                        System.Threading.Thread.Sleep(50);
                        if (executeFailed) continue;
                    }
                }
                return false;
            }
        }
    }
    Hope that helps, it fixed the errors on my box at least.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured