Setting Range.Value2 to array gives Memory is locked. (Exception

[fcctam]

Hi,

I have a problem with using Owc11.SpreadSheetClass and in particular
with Range class.

I am trying to copy a 2-d array of data into a range from a given
dataset (myDataSet). But whenever I assign the 2-d array to
Range.Value2, the error appears:

Memory is locked. (Exception from HRESULT: 0x8002000D
(DISP_E_ARRAYISLOCKED))

I will paste the code snippet below:
================
//variable to be used in place of optional param values
object missingValue = System.Reflection.Missing.Value;

SpreadsheetClass excelDoc = new SpreadsheetClass();

Workbook activeWorkBook = excelDoc.ActiveWorkbook;
//A new workbook starts with 3 work sheets. We clear the extra and
work with the first one
Worksheet activeWorksheet = (Worksheet)activeWorkBook.Worksheets[3];
activeWorksheet.Delete();
activeWorksheet = (Worksheet)activeWorkBook.Worksheets[2];
activeWorksheet.Delete();
activeWorksheet = (Worksheet)activeWorkBook.Worksheets[1];
activeWorksheet.Activate();
//pre-declare Range object for later use (and garbage collect later)
Range oRange = null;


for (int i = 0; i < myDataSet.Tables.Count; i++) {

//write worksheet name
activeWorksheet.Name = myDataSet.Tables.TableName;

#region n:m dimesion array insert method

//create n:m 2d-dimension array for data population
string[,] dataArray = new string[myDataSet.Tables.Rows.Count,
myDataSet.Tables.Columns.Count];
for (int n = 0; n < myDataSet.Tables.Rows.Count; n++) {
for (int m = 0; m < myDataSet.Tables.Columns.Count; m++) {
dataArray[n,m] = myDataSet.Tables.Rows[n][m].ToString();
}
}

//need alternate flow to handle multisheet
oRange = activeWorksheet.get_Range("A1",
activeWorksheet.Cells[dataArray.GetLength(0),
dataArray.GetLength(1)]);
oRange.set_Locked(false);
oRange.Activate();
oRange.Value2 = dataArray;
//oRange.set_Value(XlRangeValueType.xlRangeValueDefault,
(object[,])dataArray);

#endregion

//add next worksheet for next table
if (i < myDataSet.Tables.Count - 1) {
activeWorksheet =
(Worksheet)activeWorkBook.Worksheets.Add(activeWorksheet,
missingValue, missingValue, missingValue);
activeWorksheet.Activate();
}
}

I'd appreciate if anyone can provide some help.

Francis

 

[Alvin Bruney [ASP.NET MVP]]

I'm almost sure this is not possible but I haven't really checked. I'm
certain that this funcationality was added to VSTO and Excel Services but
was not in the OWC.

--

Regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
The O.W.C. Black Book, 2nd Edition
Exclusively on www.lulu.com/owc $19.99