[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

writing to Excel from C#

Posted on 2007-08-08
4
Medium Priority
?
7,625 Views
Last Modified: 2008-01-09
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
      }
}

0
Comment
Question by:g_johnson
  • 2
4 Comments
 
LVL 53

Assisted Solution

by:Dhaest
Dhaest earned 200 total points
ID: 19653557
Do you mean that while your procedure is running (and filling the excel) the user switches to another workbook, sheet and gets the error ?
In that case, I won't show the excel-sheet while filling.

oApp = new Excel.Application();
oApp.Visible = false;
0
 
LVL 4

Author Comment

by:g_johnson
ID: 19653588
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!
0
 
LVL 3

Accepted Solution

by:
DelTreme earned 1800 total points
ID: 19653607
0
 
LVL 4

Author Comment

by:g_johnson
ID: 19653664
thanks
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question