g_johnson
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.CheckFileExis ts = false;
this.sfdMain.CheckPathExis ts = 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",Miss ing.Value) ;
range.get_Offset(0,1).set_ Value(Miss ing.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(Miss ing.Value, "Account Number");
range.get_Offset(l,1).set_ Value(Miss ing.Value, "Managemen t Group");
range.get_Offset(l,2).set_ Value(Miss ing.Value, "Customer Type");
range.get_Offset(l,63).set _Value(Mis sing.Value ,"SC Item U/Comparison");
//dates sample formating
range.get_Offset(l,3).Enti reColumn.N umberForma t = "MM/DD/YYYY";
//numbers
range.get_Offset(l,23).Ent ireColumn. NumberForm at = "##,##0.00";
//advance line
l += 1;
// looping through a recordset
range.get_Offset(l,0).set_ Value(Miss ing.Value, (System.St ring)(rec. r_AcctNum. Trim()));
range.get_Offset(l,1).set_ Value(Miss ing.Value, (System.St ring)(rec. r_MgtGroup .Trim()));
range.get_Offset(l,2).set_ Value(Miss ing.Value, (System.St ring)(rec. r_CustType .Trim()));
// when finished looping;
//reset column widths
oSheet.Columns.AutoFit();
}
//save workbook
oBook.SaveAs(sFile,Missing .Value,Mis sing.Value ,Missing.V alue,Missi ng.Value,M issing.Val ue,Excel.X lSaveAsAcc essMode.xl NoChange,M issing.Val ue,Missing .Value,Mis sing.Value ,Missing.V alue,Missi ng.Value);
oBook.Close(true,Missing.V alue,Missi ng.Value);
oApp.Quit();
catch (Exception ex)
{
MessageBox.Show(this, "Error Running Report | " + ex.Message,"Error",Message BoxButtons .OK,Messag eBoxIcon.E rror);
}
finally
{
try
{
oApp.Quit();
}
catch
{
//do nothing
}
}
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.CheckFileExis
this.sfdMain.CheckPathExis
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.
oSheet.Name = "Master";
range = oSheet.get_Range("a1",Miss
range.get_Offset(0,1).set_
l = 1;
// other possible header lines here
//blank line
l += 1;
//header sample fields
range.get_Offset(l,0).set_
range.get_Offset(l,1).set_
range.get_Offset(l,2).set_
range.get_Offset(l,63).set
//dates sample formating
range.get_Offset(l,3).Enti
//numbers
range.get_Offset(l,23).Ent
//advance line
l += 1;
// looping through a recordset
range.get_Offset(l,0).set_
range.get_Offset(l,1).set_
range.get_Offset(l,2).set_
// when finished looping;
//reset column widths
oSheet.Columns.AutoFit();
}
//save workbook
oBook.SaveAs(sFile,Missing
oBook.Close(true,Missing.V
oApp.Quit();
catch (Exception ex)
{
MessageBox.Show(this, "Error Running Report | " + ex.Message,"Error",Message
}
finally
{
try
{
oApp.Quit();
}
catch
{
//do nothing
}
}
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks
ASKER