Error 0x800A03EC trying to set range value

[mdengler]

When trying to set the range value of the spreadsheet I get the
following error: Exception from HRESULT: 0x800A03EC.

BuildXls method follows:

public string BuildXls(string FileName, ref string[,] AryFieldsAll,
System.Data.DataTable MyDataTable)
{
System.Globalization.CultureInfo enUS = new
System.Globalization.CultureInfo("en-US");

System.Threading.Thread.CurrentThread.CurrentCulture = enUS;

string return_message;
int num_rows=MyDataTable.Rows.Count;
int num_cols=MyDataTable.Columns.Count;
int max_rows_per_sheet=65000;
if (num_rows<=max_rows_per_sheet)
{
max_rows_per_sheet=num_rows;
}
int row_index;
int col_index;
int sheet_index=1;
int sheet_row_counter=0;
object[,] objData = new object[num_rows,num_cols];
object[,] objData2 = new object[max_rows_per_sheet,num_cols];
object[] objHeaders = new object[num_cols];

// Excel object references.
Excel.Application objExcel = null;
Excel.Workbooks objBooks = null;
Excel._Workbook objBook = null;
Excel.Sheets objSheets = null;
Excel._Worksheet objSheet = null;
Excel.Range objRange = null;
Excel.Font objFont = null;

// Start a new workbook in Excel.
objExcel = new Excel.Application();
objBooks = (Excel.Workbooks)objExcel.Workbooks;
objBook = (Excel._Workbook)(objBooks.Add(objOpt));
objSheets = (Excel.Sheets)objBook.Worksheets;
objSheet = (Excel._Worksheet)(objSheets.get_Item(sheet_index));

//build a header array
col_index=0;
foreach(DataColumn col in MyDataTable.Columns)
{
objHeaders[col_index] = col.ColumnName;
col_index++;
}

//build an array of data rows
row_index=0;
foreach(DataRow row in MyDataTable.Rows)
{
col_index=0;
foreach(object item in row.ItemArray)
{
//create an array of row values. prefix all values with a single
quote.
objData[row_index,col_index] = "'"+item.ToString();
col_index++;
}
row_index++;
}

//build the spreadsheet
try
{
for(row_index=0; row_index<num_rows; row_index++)
{
if (sheet_row_counter==0)
{
objData2 = new object[max_rows_per_sheet,num_cols];
}

//create an array of row values
for (col_index=0; col_index<num_cols; col_index++)
{
objData2[sheet_row_counter,col_index] =
objData[row_index,col_index];
}
sheet_row_counter++;

if (sheet_row_counter==max_rows_per_sheet ||
row_index==num_rows-1)
{
if (sheet_index>3)
{
objSheet =
(Excel._Worksheet)objBook.Worksheets.Add(Missing.Value,objSheet,Missing.Value,Missing.Value);

objSheet.Name="Sheet"+(sheet_index);
}
else
{
objSheet =
((Excel._Worksheet)objExcel.Worksheets["Sheet"+sheet_index]);
}

//add the header to the worksheet
objRange =
objSheet.get_Range((Excel.Range)objSheet.Cells[1,1],(Excel.Range)objSheet.Cells[1,num_cols]);
objRange.set_Value(Missing.Value, objHeaders);
objFont = objRange.Font;
objFont.Bold=true;

//add the data rows to the worksheet
objRange =
objSheet.get_Range((Excel.Range)objSheet.Cells[2,1],(Excel.Range)objSheet.Cells[max_rows_per_sheet+1,num_cols]);
objRange.Value2 = objData2; //this is where the error occurs
//objRange.set_Value(Missing.Value, objData2);
//objRange.set_Value(OWC10.XlRangeValueType.xlRangeValueDefault,objData2);

sheet_row_counter=0;
sheet_index++;
}
}

MyDataTable.Dispose();
GC.Collect();

//format the spreadsheet
for(int i=1;i<=objBook.Worksheets.Count;i++)
{
objSheet=(Excel._Worksheet)objBook.Worksheets["Sheet"+i];
int j=0;
foreach(DataColumn col in MyDataTable.Columns)
{
j++;
objRange=(Excel.Range)objSheet.Cells[1,j];
objRange=objRange.EntireColumn;
//objRange.AutoFit();
//objRange.set_HorizontalAlignment(Excel.XlHAlign.xlHAlignRight);

// set the font
objFont = objRange.Font;
objFont.Name = "Arial";
objFont.Size = 8;

// format the columns
for (int f=0; f<AryFieldsAll.GetLength(0); f++)
{
// does the selected column name equal the AryFieldsAll field
name value?
if (col.ColumnName.ToString() == AryFieldsAll[f,3])
{
switch (AryFieldsAll[f,17]) //format info
{
case "{0:N0}": // number
objRange.NumberFormat = "###,##0;[Red]###,##0";
break;
case "{0:c}": // currency
objRange.NumberFormat = "$###,##0.00;[Red]$###,##0.00";
break;
case "{0:p}": // percentage
objRange.NumberFormat = "###,##0.00%;[Red]###,##0.00%";
break;
case "{mm/dd/yyyy}": // date
objRange.NumberFormat = "mm/dd/yyyy";
break;
} //switch (AryFieldsAll[f,17]) //format info
break;
} //if (MyDataColumn.ColumnName.ToString() ==
AryFieldsAll[f,0])
} //for (int f=0; f<AryFieldsAll.GetLength(0); f++)
} //foreach(DataColumn col in MyDataTable.Columns)
} //for(int i=1;i<=objBook.Worksheets.Count;i++)

((Excel._Worksheet)objExcel.Worksheets["Sheet1"]).Activate();
// Save the Workbook
objBook.SaveAs(FileName, objOpt, objOpt, objOpt, objOpt, objOpt,
Excel.XlSaveAsAccessMode.xlNoChange, objOpt, objOpt, objOpt, objOpt,
objOpt);

return_message = "success";
}
catch (Exception e)
{
return_message = e.Message.ToString();
}
finally
{/*
// Need all following code to clean up and extingush all
references!!!
objBook.Close(null,null,null);
objExcel.Workbooks.Close();
objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject
(objRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject
(objExcel);
System.Runtime.InteropServices.Marshal.ReleaseComObject
(objSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject (objBook);
objSheet=null;
objBook=null;
objExcel = null;
GC.Collect(); // force final cleanup!
*/
}

return return_message;
}



when the Datatable contains this data, it works:

Placement #365 - 02/14/02 - Keyword Bundle: hotel reservation,
hotel reservations, car rental, car rentals Placement #366 -
02/14/02 - Keyword Bundle: cruise line, cruise lines, cruise ship,
cruise ships, luxury cruise, luxury cruises, cruises Placement
#367 - 02/14/02 - Keyword Bundle: broadband, portal
Placement #368 - 02/14/02 - keyword Bundle: personal ads, online
personals Placement #369 - 02/14/02 - Keyword Bundle: bed
and breakfast; bed and breakfasts; bed and breakfast inn; bed and
breakfast inns; bed breakfast; bed breakfasts; bed breakfast inn; bed
breakfast inns; bed and breakfast guide; bed and breakfast directory;
bed breakfast directory; country inn; country inns; inns

when the DataTable contains this data, it fails:

This Order replaces BS03210110 Placement 514 Exclusive
Keywords;camcorder accessories; camcorder bags; camcorder lights;
camcorder microphones; camcorder stand; camcorder stands; camcorder
tripods; camera bags; conversion lens; sony accessories; sony
accessorry; tripod stands; video accessories; video cables; video
editing; video lights; camcorder lens Placement 524
audiovox accessories; audiovox chargers; audiovox free; audiovox
headsets; cell accessories; cellular accessories; cellular accessory;
digital accessories; ericsson accessories; ericsson chargers; ericsson
free; ericsson headsets; headset; headsets; motorola accessories;
motorola chargers; motorola free; motorola headsets; nextel
accessories; nextel chargers; nextel free; nextel headsets; nokia
accessories; nokia chargers; nokia free; nokia headsets
Placement 523 9 volt; aa batteries; aa battery; aaa batteries;
battery; batteries; alkaline batteries; alkaline battery; battery
charger; battery chargers; ; camcorder batteries; camcorder battery;
cell phone batteries; cell phone battery; cellphone batteries;
cellphone battery; cellular batteries; cellular battery; cellular
batteries; cellular battery; computer batteries; computer battery;
cordless phone batteries; cordless battery; digital camera batteries;
digit Placement 513 Exclusive Keywords;audiovox
accessories; audiovox chargers; audiovox free; audiovox headsets; cell
accessories; cellular accessories; cellular accessory; digital
accessories; ericsson accessories; ericsson chargers; ericsson free;
ericsson headsets; headset; headsets; motorola accessories; motorola
chargers; motorola free; motorola headsets; nextel accessories; nextel
chargers; nextel free; nextel headsets; nokia accessories; nokia
chargers; nokia fre Placement 525 camcorder accessories;
camcorder bags; camcorder lights; camcorder microphones; camcorder
stand; camcorder stands; camcorder tripods; camera bags; conversion
lens; sony accessories; sony accessorry; tripod stands; video
accessories; video cables; video editing; video lights; camcorder lens
Placement 515 keyword; Battery Placement 516
All Placements in Shopping Channel Placement 512 9 volt; aa
batteries; aa battery; aaa batteries; batteries; alkaline batteries;
alkaline battery; battery charger; battery chargers; ; camcorder
batteries; camcorder battery; cell phone batteries; cell phone battery;
cellphone batteries; cellphone battery; cellular batteries; cellular
battery; cellular batteries; cellular battery; computer batteries;
computer battery; cordless phone batteries; cordless battery; digital
camera batteries; digital camera


Any ideas or thoughts would be appreciated.


--
mdengler
Posted from - http://www.officehelp.in

[mdengler]

Here's what I know so far....

If the text exceeds 911 chars, it fails. 911 chars and below, it
works. Any ideas?


--
mdengler
Posted from - http://www.officehelp.in

[mdengler]

I've been searching the www for an answer and the only solution I can
find is to truncate the string to 911 characters. I would prefer to
not take this approach if I don't have to. I have to believe there is
a way to programmatically add more than 911 characters because I'm able
to manually copy 32k characters into an excel spreadsheet cell. Has
anyone else overcome this problem?


--
mdengler
Posted from - http://www.officehelp.in