Saturday 19 January 2008

Passing null values to CLRInterop methods

When using Excel CLR Interop from x++, it is required that all optional parameters are specified, yet when NULL is passed the following error is returned

Exception has been thrown by the target of an invocation.

To get around this a value of System.Reflection.Missing.Value needs to be passed instead.
To instantiate this the following code is needed:

System.Type type = System.Type::GetType("System.Reflection.Missing");
System.Reflection.FieldInfo info = type.GetField("Value");
System.Object missing = info.GetValue(null);

Now you can create code, for example to create a new worksheet:

try
{
application = new Microsoft.Office.Interop.Excel.ApplicationClass();
application.set_Visible(true);
workbooks = application.get_Workbooks();
xlWBATemplate = ClrInterop::parseClrEnum('Microsoft.Office.Interop.Excel.XlWBATemplate', 'xlWBATWorksheet');
workbook = workbooks.Add(xlWBATemplate);

worksheets = application.get_Worksheets();
xlSheetType = ClrInterop::parseClrEnum('Microsoft.Office.Interop.Excel.XlSheetType', 'xlWorksheet');
worksheet = worksheets.Add(missing,missing,1,xlSheetType);

}
catch
{
clrError = clrInterop::getLastException();
if(clrError)
{
error(CLRInterop::getAnyTypeForObject(clrError.get_Message()));
}
}