Com Exception - excel

KaurGurpreet

A simple code to export data to excel

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 < 1000; row++)
  {
    for (int column = 0; column < 20; column++)
    {
      cell1 = chars\[column\] + row.ToString();
      objRange = \_objWorkSheet.get\_Range(cell1, cell1);
      objRange.Value2 = cell1;
      
      
    }
  }
  System.Runtime.Int

freakyit

hi, what is the exactly com exception ??

KaurGurpreet

Exception from HRESULT: 0x800AC472

Gurpreet

OkkiePepernoot

Made some changes in the Button_Click event and could not reproduce the issue. //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++) { cell1 = chars[column] + row.ToString(); //objRange = _objWorkSheet.get_Range(cell1, cell1); _objWorkSheet.get_Range(cell1, cell1).Value2 = cell1; } } //System.Runtime.InteropServices.Marshal.ReleaseComObject(objRange); //objRange = null; SaveExcel();

KaurGurpreet

I tried... but I still get the same error (exception from HRESULT: 0x800AC472) While the code in for loop is executing, you need to open any other excel file and click on randon cells.

Gurpreet

OkkiePepernoot

I did but still can't reproduce. Using Excel 2010.

KaurGurpreet

Oh! But I am getting this evevytime i try to do so My environment is: Visual Studio 2010 Excel - 2008 Here is the detailed error:

System.Runtime.InteropServices.COMException (0x800AC472): Exception from HRESULT: 0x800AC472
at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
at Microsoft.Office.Interop.Excel.Range.set_Value2(Object )
at WindowsFormsApplication1.Form1.button1_Click(Object sender, EventArgs e) in c:\documents and settings\kaurrgur\my documents\visual studio 2010\Projects\WindowsFormsApplication1\WindowsFormsApplication1\Form1.cs:line 77
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

Gurpreet

freakyit

hi it might work setting the value into the cell by not using the objRange.Value2 property ?!? this is only a suggestion.

Lost User

try setting the .IgnoreRemoteRequests to true right after you open your instance of excel, and don't forget to set it back to false when done as this affects the behavior of your entire environment.

Check out the CodeProject forum Guidelines[^] The original soapbox 1.0 is back![^]

KaurGurpreet

I tried like below but it does not solve the problem :(

_objWorkSheet.Cells[row, column] = cell1;

Gurpreet

KaurGurpreet

Finally some solution which works :) But still while my code is executing, I cannot open any other excel file... which is not good :( If I look at the behaviour of Visual Studio (when I export my work list to excel), it works perfectly fine... no error and I can open the other excel too... So whats wrong with my code... Is there any other better way to export?

Gurpreet

Lost User

As I said in my previous post, this property effects the behavior of the entire system, do some research, the issue you have is common. (hint, setting it back to false will restore expected behavior)

Check out the CodeProject forum Guidelines[^] The original soapbox 1.0 is back![^]

KaurGurpreet

Yes setting it back restores the behaviour but while export process is on... it is not good that any other excel file cannot be acessed.

Gurpreet

Lost User

Again, other excel files can still be accessed, you must open a new instance of excel first, not simply 'double click' on an excel file icon through explorer. Using this property is still an acceptable and workable solution for you, you just have to do the research to figure out how to keep your excel instance separate from others. Do some research on it. I know, I had the exact same issue you have and worked around it.

Check out the CodeProject forum Guidelines[^] The original soapbox 1.0 is back![^]