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?

This conversation belongs in microsoft.public.excel.programming.
However, here is your answer (I took out all our specific code)
using Visual C++ 2005:

// main pointer for Excel
IDispatch * pExcelApplication = NULL;
// Workbooks collection
IDispatch * pExcelWorkbooks = NULL;
// Workbook object
IDispatch * pExcelWorkbook = NULL;
// Sheet object
IDispatch * pExcelSheet = NULL;

HRESULT OLEMethod (int autoType, VARIANT *pvResult, IDispatch *pDisp, LPOLESTR ptName, const char * errorStr, int cArgs...);

int StartExcelServer (void)
{
// this code is somewhat from http://support.microsoft.com/kb/216686
// and from http://support.microsoft.com/kb/238610

// if there is a current server then release it
if (pExcelApplication)
{
pExcelApplication - Release ();
pExcelApplication = NULL;
}

// Get CLSID for our server...
CLSID clsid;
HRESULT hr = CLSIDFromProgID (L"Excel.Application", & clsid);
if (FAILED (hr))
{
::MessageBox (NULL, "CLSIDFromProgID() failed", "Error", MB_ICONSTOP | MB_OK);
return false;
}

// see if we can connect to existing excel server and get idispatch
// NOTE: the process permission levels must be the same for this process
// and the excel process for GetActiveObject to work correctly.
// So, if running deswin.exe from visual studio then excel must
// be running as administrator also.
IUnknown * pIUnknown = NULL;
hr = GetActiveObject (clsid, NULL, (IUnknown**) & pIUnknown);
if (SUCCEEDED (hr))
{
// convert the iunknown pointer to an idispatch pointer
hr = pIUnknown - QueryInterface (IID_IDispatch, (void**) & pExcelApplication);
// release the iunknown pointer since we dont need it anymore
pIUnknown - Release ();
}
// if failed to talk to an existing excel then start server and get IDispatch...
if (FAILED (hr))
hr = CoCreateInstance (clsid, NULL, CLSCTX_LOCAL_SERVER, IID_IDispatch, (void **) & pExcelApplication);
if (FAILED (hr))
{
::MessageBox (NULL, "Could not start Excel OLE Automation Server", "Error", MB_ICONSTOP | MB_OK);
return false;
}

// Make excel visible (i.e. app.visible = 1)
// if this fails then it is OK
if ( ! FAILED (hr))
{
VARIANT x;
x.vt = VT_I4;
x.lVal = 1;
OLEMethod (DISPATCH_PROPERTYPUT, NULL, pExcelApplication, L"Visible",
"Making Excel visible on the screen (StartExcelServer)", 1, x);
}

// if there is a current workbooks then release it
if (pExcelWorkbooks)
{
pExcelWorkbooks - Release ();
pExcelWorkbooks = NULL;
}

// Get Workbooks collection
{
VARIANT result;
VariantInit ( & result);
OLEMethod (DISPATCH_PROPERTYGET, & result, pExcelApplication, L"Workbooks",
"Getting the Workbooks collection pointer (StartExcelServer)", 0);
pExcelWorkbooks = result.pdispVal;
}

return true;
}


// OLEMethod() - Automation helper function...
// from http://support.microsoft.com/kb/216686
HRESULT OLEMethod (int autoType, VARIANT *pvResult, IDispatch *pDisp, LPOLESTR ptName, const char * errorStr, int cArgs...)
{
// Begin variable-argument list...
va_list marker;
va_start(marker, cArgs);
char buf [2000];
char szName [2000];

// Convert down to ANSI
WideCharToMultiByte (CP_ACP, 0, ptName, -1, szName, 256, NULL, NULL);

if ( ! pDisp)
{
sprintf_s (buf, sizeof (buf), "ERROR: NULL IDispatch passed to OLEMethod() for \"%s\" (OLEMethod).", szName);
strcat_s (buf, sizeof (buf), "\n\nIf there are any warning messages in Excel then please\n"
"clear them and restart the data transfer.\n\n");
strcat_s (buf, sizeof (buf), "Activity: ");
strcat_s (buf, sizeof (buf), errorStr);
MessageBox (NULL, buf, "title", 0x10010);
// _exit (0);
}

// Variables used...
DISPPARAMS dp = { NULL, NULL, 0, 0 };
DISPID dispidNamed = DISPID_PROPERTYPUT;
DISPID dispID;
HRESULT hr;

// Get DISPID for name passed...
hr = pDisp - GetIDsOfNames (IID_NULL, & ptName, 1, LOCALE_USER_DEFAULT, & dispID);
if (FAILED (hr))
{
sprintf_s (buf, sizeof (buf), "ERROR: IDispatch::GetIDsOfNames (\"%s\") failed w/err 0x%08lx (OLEMethod).",
szName, hr);
strcat_s (buf, sizeof (buf), "\n\nIf there are any warning messages in Excel then please\n"
"clear them and restart the data transfer.\n\n");
strcat_s (buf, sizeof (buf), "Activity: ");
strcat_s (buf, sizeof (buf), errorStr);
MessageBox (NULL, buf, "title", 0x10010);
// _exit(0);
return hr;
}

// Allocate memory for arguments...
VARIANT * pArgs = new VARIANT [cArgs+1];
// Extract arguments...
for (int i = 0; i < cArgs; i++)
{
pArgs[i] = va_arg (marker, VARIANT);
}

// Build DISPPARAMS
dp.cArgs = cArgs;
dp.rgvarg = pArgs;

// Handle special-case for property-puts!
if(autoType & DISPATCH_PROPERTYPUT)
{
dp.cNamedArgs = 1;
dp.rgdispidNamedArgs = &dispidNamed;
}

// Make the call! try up to 10 times and then quit after notifying user
hr = -1;
int counter = 0;
while (FAILED (hr) && counter <= 10)
{
hr = pDisp - Invoke (dispID, IID_NULL, LOCALE_SYSTEM_DEFAULT, autoType, & dp, pvResult, NULL, NULL);
// if we failed then sleep for half a second
if (FAILED (hr))
Sleep (500);
counter++;
}
if (FAILED (hr))
{
sprintf_s (buf, sizeof (buf), "ERROR: IDispatch::Invoke (\"%s\"=%08lx) failed w/err 0x%08lx (OLEMethod).",
szName, dispID, hr);
strcat_s (buf, sizeof (buf), "\n\nIf there are any warning messages in Excel then please\n"
"clear them and restart the data transfer.\n\n");
strcat_s (buf, sizeof (buf), "Activity: ");
strcat_s (buf, sizeof (buf), errorStr);
MessageBox (NULL, buf, "title", 0x10010);
// _exit(0);
return hr;
}

// End variable-argument section...
va_end (marker);

delete [] pArgs;

return hr;
}


Lynn