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 here is how you open an existing spreadsheet:

int ConnectToNotebook (std::string newNotebookName, int runInTestMode)
{
int tries = 0;

// set the error counter to zero each time
g_NumberOfErrors = 0;

g_buffer = newNotebookName;
int len = g_buffer.size ();
// copy the new name in with a .XLS extension if not there already
if (len < 4 || 0 != _strnicmp (& (g_buffer.c_str () [len - 4]), ".xls", 4))
g_notebookName = g_buffer + ".xls";
else
g_notebookName = g_buffer;
g_notebookPrefix = g_buffer;

GetStartupDir ();
if ( ! MakeSureNotebookExists (g_notebookName))
return FALSE;

int ret = StartExcelServer ();
if ( ! ret)
{
g_buffer = "Can't start conversation with Excel.\n"
"Problem with either Excel or OLE automation.";
MessageBox (0, g_buffer.c_str (), "title", MB_ICONSTOP | MB_TASKMODAL);
return false;
}

// if we got here then Excel is alive and ready to take input

// get the names of the currently open spreadsheets and see if this one is open already
// otherwise open the spreadsheet
VARIANT result1;
VariantInit ( & result1);
OLEMethod (DISPATCH_PROPERTYGET, & result1, pExcelWorkbooks, L"Count",
"Getting the number of currently open spreadsheets (ConnectToNotebook)", 0);
int numberOfWorkbooks = 0;
if (result1.vt == VT_I4)
numberOfWorkbooks = result1.intVal;
int workbookOpenAlready = false;
if (numberOfWorkbooks 0)
{
for (int i = 0; i < numberOfWorkbooks && ! workbookOpenAlready; i++)
{
VARIANT result2;
VariantInit ( & result2);
VARIANT itemNumber;
itemNumber.vt = VT_I4;
// put the index of the workbook to get into the variant, the index starts with 1
itemNumber.intVal = i + 1;
OLEMethod (DISPATCH_PROPERTYGET, & result2, pExcelWorkbooks, L"Item",
"Getting the index of the spreadsheet (ConnectToNotebook)", 1, itemNumber);
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 indexed spreadsheet (ConnectToNotebook)", 0);
if (result3.vt == VT_BSTR)
{
// this will be the workbook name without the path
std::string workbookName = _bstr_t (result3.bstrVal);
// strip the path from the current workbook name
std::string noPathNotebookName = g_notebookName;
int lastSlash = g_notebookName.size ();
while (lastSlash = 0 && g_notebookName [lastSlash] != '\\')
lastSlash--;
if (lastSlash = 0)
noPathNotebookName.erase (0, lastSlash + 1);
if (workbookName == noPathNotebookName)
{
workbookOpenAlready = true;
// if there is a current excel workbook then release it
if (pExcelWorkbook)
{
pExcelWorkbook - Release ();
pExcelWorkbook = NULL;
}
// copy the dispatch pointer to the workbook pointer
pExcelWorkbook = pDisp;
// now activate the workbook
VARIANT result5;
VariantInit ( & result5);
OLEMethod (DISPATCH_PROPERTYGET, & result5, pDisp, L"Activate",
"Activating the spreadsheet that we want to use (ConnectToNotebook)", 0);
int res = result5.vt;
}
else
pDisp - Release ();
}
}
}
}

// we need to open the spreadsheet file if not done already
if ( ! workbookOpenAlready)
{
VARIANT result;
VariantInit ( & result);
VARIANT fname;
fname.vt = VT_BSTR;
_bstr_t notebookNameBstr = _bstr_t (g_notebookName.c_str ());
fname.bstrVal = notebookNameBstr;
OLEMethod (DISPATCH_METHOD, & result, pExcelWorkbooks, L"Open",
"Opening the spreadsheet that we want to use (ConnectToNotebook)", 1, fname);
// copy the dispatch pointer to the workbook pointer
if (result.vt == VT_DISPATCH)
pExcelWorkbook = result.pdispVal;
else
return false;
}

return TRUE;
}


// stuff startup path into global
void GetStartupDir (void)
{
// get the directory that the exe was started from
char directory [4096];
GetModuleFileName (NULL, directory, sizeof (directory));
// cut the exe name from string
char * p = & (directory [strlen (directory) - 1]);
while (p = directory && *p && '\\' != *p)
p--;
*p = '\0';
g_StartupDir = directory;
}


// NOTE: if this function returns FALSE then there is a major error
int MakeSureNotebookExists (std::string newNotebookName)
{
HANDLE found = NULL;
WIN32_FIND_DATA findData;
memset ( & findData, 0, sizeof (findData));

// copy from the template file if:
// a. if I could not find the file
// b. the file is there but has zero size
found = FindFirstFile (newNotebookName.c_str (), & findData);
// sprintf (buffer, "Got result %d when doing findfirst on\n%s",
// found, newNotebookName);
// MessageBox (0, buffer, "title", MB_ICONSTOP | MB_TASKMODAL);
if (INVALID_HANDLE_VALUE == found || findData.nFileSizeLow == 0)
{
// copy the standard template spreadsheet file from the
// startup directory
FILE *preExisting = NULL;
FILE *templateNotebook = NULL;
std::string path = g_StartupDir + "\\template.xls";
errno_t err = fopen_s ( & templateNotebook, path.c_str (), "rb");
if (err != 0)
{
g_buffer = "Could not open spreadsheet template file:\n\"" + path + "\".";
MessageBox (0, g_buffer.c_str (), "title", MB_ICONSTOP | MB_TASKMODAL);
return FALSE;
}

err = fopen_s ( & preExisting, newNotebookName.c_str (), "wb");
if (err == 0) // copy the template to the new file
{
int numRead = 0;
char tempBuffer [4096];
while (numRead = fread (tempBuffer, sizeof (char), sizeof (tempBuffer), templateNotebook))
{
fwrite (tempBuffer, sizeof (char), numRead, preExisting);
}
fclose (templateNotebook);
fclose (preExisting);
}
else
{
g_buffer = "Could not open new spreadsheet file:\n\"";
g_buffer += newNotebookName + ".\n"
"If this file is open by another application then\n"
"please close the file and restart the data transfer.";
MessageBox (0, g_buffer.c_str (), "title", MB_ICONSTOP | MB_TASKMODAL);
fclose (templateNotebook);
return FALSE; // kill the run
}
}

// make sure that we release the file handle if valid
if (found != INVALID_HANDLE_VALUE)
FindClose (found);

return TRUE;
}




Lynn