Coercing xltypeRef to xltypeMulti fails

[ABW]

I am having problems trying to coerce xltypeRef to xltypeMulti if the range selected is not on the same worksheet.

Here is the synopsis. I need to develop an add-in in which one of the macros copies a one column range over to another column on another work(sheet/book). The catch is the paste operation must skip the cells that are either hidden due to a filter being active or hidden by the user. For that I devised a macro that asks for the one column source range and the first cell of the destination column using xlfInput. The source range is coerced into xltypeMulti and the values are copied to the destination cell using cell. It works well if both the source and destination ranges are on the same worksheet but, if one of the ranges is on a work(sheet/book) that is not the active one then the coerce function call fails. The reference to the range that is on the active sheet is of type xltypeSRef and the one that is not on the active sheet is of type xltypeRef just as expected. But it is always the xltypeRef that fails to coerce into multi type.

I must mention that, I am on WIndows 8 64bit with Office 2013 64 bit and I am using Keith Lewis' XLL library from xll.lcodeplex.com

Here is the macro code:

int WINAPI
xll_PasteToVisCells(void)
{
#pragma XLLEXPORT

try
{
//Get the Source Range
OPERX oxSourceRange = ExcelX(xlfInput, OPERX(_T("Source Range")), OPERX(8), OPERX(_T("Select Source Range")));
if (oxSourceRange.xltype == xltypeBool && !oxSourceRange)
{
//Flag this is an error and exit
return 0;
}

if (((oxSourceRange.xltype & xltypeSRef) != 0 && (oxSourceRange.xltype & xltypeRef) != 0) || (oxSourceRange.columns() != 1))
{
//Flag this is an error and exit
return 0;
}
//Get the first cell in Destination Range
OPERX oxDestCell = ExcelX(xlfInput, OPERX(_T("Destination Range")), OPERX(8), OPERX(_T("Select The First Cell In Destination Range To Copy To:")));

if (oxDestCell.xltype == xltypeBool && !oxDestCell)
{
//Flag this is an error and exit
return 0;
}

if (((oxDestCell.xltype & xltypeSRef) != 0 && (oxDestCell.xltype & xltypeRef) != 0) || (oxDestCell.rows() != 1 || oxDestCell.columns() != 1))
{
//Flag this is an error and exit
return 0;
}

//----------This call fails if xltypeRef-------------
OPERX oxSourceValues = ExcelX(xlCoerce, oxSourceRange, OPERX(xltypeMulti));

//----------This call fails if xltypeRef-------------
ExcelX(xlcSelect, oxDestCell, oxDestCell);
}
catch (const std::exception &ex){
XLL_WARNING(ex.what());
}
}

The error message I receive is "Excel returned command failed".

So, how do I go about this and coerce xltypeRef into xltypeMulti and/or use it as is (as in the case of destination range). If you feel that my approach is not the best one or if you have a different approach to this problem I'd be happy to listed to it

Thanks.

[b***@gmail.com]

Any help please ?