Link to home
Start Free TrialLog in
Avatar of g_johnson
g_johnsonFlag for United States of America

asked on

writing to Excel from C#

I write to an Excel spreadsheet using the techniques shown below.  If the user is working in (I think saving) a different worksheet while my worksheet is filling, he will get this error:
Exception from HRESULT:0x800AC472

These are Windows XP workstations

What is causing this?


Excel.Application oApp;
Excel._Workbook oBook;
Excel.Workbooks oBooks;
Excel.Sheets oSheets;
Excel._Worksheet oSheet;
Excel.Range range;
long l = 0;

oApp = new Excel.Application();
oBooks = oApp.Workbooks;
oBook = oBooks.Add(Missing.Value);

try
{
      //let them tell us the spreadsheet name, etc.
      this.sfdMain.CheckFileExists = false;
      this.sfdMain.CheckPathExists = false;
      this.sfdMain.Title = "Select Excel File Path";
      this.sfdMain.Filter = "Excel Files (*.xls) | *.xls";
      this.sfdMain.ShowDialog();
      sFile = sfdMain.FileName;
      if (sFile.Trim().Length == 0)
      {
            throw new Exception("No File Name Chosen");
      }
      if (File.Exists(sFile))
      {
            this.sbp1.Text = "Deleting old file ...";
            File.Delete(sFile);
      }
      //run several stored procedures here
      //
      //
      //read table using proprietary object
      //do the spreadsheet thing
      //set up workbook
      oSheets = oBook.Worksheets;
      oSheet = (Excel._Worksheet)oSheets.get_Item(1);
      oSheet.Name = "Master";
      range = oSheet.get_Range("a1",Missing.Value);
      range.get_Offset(0,1).set_Value(Missing.Value,"Price Report As Of : " + DateTime.Now.ToString());
      l = 1;
// other possible header lines here

      //blank line
      l += 1;
      //header  sample fields
      range.get_Offset(l,0).set_Value(Missing.Value,"Account Number");
      range.get_Offset(l,1).set_Value(Missing.Value,"Management Group");
      range.get_Offset(l,2).set_Value(Missing.Value,"Customer Type");
      range.get_Offset(l,63).set_Value(Missing.Value,"SC Item U/Comparison");
      //dates  sample formating
      range.get_Offset(l,3).EntireColumn.NumberFormat = "MM/DD/YYYY";
      //numbers
      range.get_Offset(l,23).EntireColumn.NumberFormat = "##,##0.00";
      //advance line
      l += 1;

// looping through a recordset
                                    
range.get_Offset(l,0).set_Value(Missing.Value,(System.String)(rec.r_AcctNum.Trim()));
      range.get_Offset(l,1).set_Value(Missing.Value,(System.String)(rec.r_MgtGroup.Trim()));
      range.get_Offset(l,2).set_Value(Missing.Value,(System.String)(rec.r_CustType.Trim()));


// when finished looping;
      //reset column widths
      oSheet.Columns.AutoFit();
}
//save workbook
oBook.SaveAs(sFile,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Excel.XlSaveAsAccessMode.xlNoChange,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value);

oBook.Close(true,Missing.Value,Missing.Value);
oApp.Quit();

catch (Exception ex)
{
      MessageBox.Show(this, "Error Running Report | " + ex.Message,"Error",MessageBoxButtons.OK,MessageBoxIcon.Error);
}
finally
{
      try
      {
            oApp.Quit();
      }
      catch
      {
            //do nothing
      }
}

SOLUTION
Avatar of Dirk Haest
Dirk Haest
Flag of Belgium image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of g_johnson

ASKER

No, it's when they are working in any other workbook, I believe.  I will add the visible = false, though -- that just makes sense to me!
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks