I'm developing an add-in in C++ using the Excel 2010 XLL SDK, and in
the process I have to return values to other cells than the one the
function in entered into. To that end, I think Automation might work.
I've managed to get automation to work inside the UDF, using the
following:
http://support.microsoft.com/kb/216686
The problem, however, is that the program opens a new instance of
Excel, creates a new worksheet and puts data into that. I wish to put
the data onto the sheet on which the UDF was entered - not a new sheet
in a new workbook. How might I achieve this goal?

And this is how you get the current cell selection and sheet name
in the spreadsheet that you just opened:

// return a standard string with the file + sheet + current selected cell
// for OLE, will get something like '[EXPANDER-in.xls]Mat Bal'!F3

std::string GetExcelCurrentSelection (void)
{
std::string selection = "";

// for OLE, will get something like '[EXPANDER-in.xls]Mat Bal'!F3
// first get the name of the spreadsheet
VARIANT result1;
VariantInit ( & result1);
OLEMethod (DISPATCH_PROPERTYGET, & result1, pExcelApplication, L"ActiveWorkbook",
"Getting the pointer of the active spreadsheet (GetExcelCurrentSelection)", 0);
if (result1.vt == VT_DISPATCH)
{
IDispatch *pDisp = result1.pdispVal;
VARIANT result2;
VariantInit ( & result2);
OLEMethod (DISPATCH_PROPERTYGET, & result2, pDisp, L"Name",
"Getting the name of the active spreadsheet (GetExcelCurrentSelection)", 0);
if (result2.vt == VT_BSTR)
{
selection += "\'[";
selection += _bstr_t (result2.bstrVal);
selection += "]";
}
pDisp - Release ();
}

// get the name of the sheet
VARIANT result2;
VariantInit ( & result2);
OLEMethod (DISPATCH_PROPERTYGET, & result2, pExcelApplication, L"ActiveSheet",
"Getting the pointer of the active sheet of the active spreadsheet (GetExcelCurrentSelection)", 0);
if (result2.vt == VT_DISPATCH)
{
IDispatch *pDisp = result2.pdispVal;
VARIANT result3;
VariantInit ( & result3);
OLEMethod (DISPATCH_PROPERTYGET, & result3, pDisp, L"Name",
"Getting the name of the active sheet of the active spreadsheet (GetExcelCurrentSelection)", 0);
if (result3.vt == VT_BSTR)
{
selection += _bstr_t (result3.bstrVal);
selection += "\'!";
}
pDisp - Release ();
}

// get the selected cell(s) addresses
VARIANT result3;
VariantInit ( & result3);
OLEMethod (DISPATCH_PROPERTYGET, & result3, pExcelApplication, L"Selection",
"Getting the pointer of the selected cell of the active spreadsheet (GetExcelCurrentSelection)", 0);
if (result3.vt == VT_DISPATCH)
{
IDispatch *pDisp = result3.pdispVal;
// this will get the contents of the selected cell
// VariantInit ( & result);
// OLEMethod (DISPATCH_PROPERTYGET, & result, pDisp, L"Value", 0);
// int res_value = result.vt;
// this will get the address of the selected cell
VARIANT result4;
VariantInit ( & result4);
OLEMethod (DISPATCH_PROPERTYGET, & result4, pDisp, L"Address",
"Getting the address of the selected cell of the active spreadsheet (GetExcelCurrentSelection)", 0);
if (result4.vt == VT_BSTR)
{
std::string absoluteReference = _bstr_t (result4.bstrVal);
int len = absoluteReference.size ();
for (int i = 0; i < len; i++)
{
if (absoluteReference [i] != '$')
selection += absoluteReference [i];
}
}
pDisp - Release ();
}

return selection;
}


Lynn