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

BuildXls method follows:
****************** start of BuildXls method *********************

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.CurrentCultu re = 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_index3)
{
objSheet =
(Excel._Worksheet)objBook.Worksheets.Add(Missing.V alue,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.xlRangeV alueDefault,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.xl HAlignRight);
// 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.ReleaseComO bject (objRange);
System.Runtime.InteropServices.Marshal.ReleaseComO bject (objExcel);
System.Runtime.InteropServices.Marshal.ReleaseComO bject (objSheet);
System.Runtime.InteropServices.Marshal.ReleaseComO bject (objBook);
objSheet=null;
objBook=null;
objExcel = null;
GC.Collect(); // force final cleanup!
*/
}

return return_message;
}

****************** end of BuildXls method *********************

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.

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

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

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?

I've run into the same problem, and also discovered the 911-character limit.
Since it's in an existing application, and we've been running it with the
same data that cause the problem (a string of about 1871 characters), it's
likely that some fairly recent Office update has caused it.

Note that it doesn't happen in VBA; I can start Excel and put a 2K-character
string into a cell with no complaints. It's when I'm working at a lower
lever using a SAFEARRAY of VARIANTs that I get the problem.

Don