Re: Automation and XLL add-ins
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 open an existing sheet in a notebook:
int ConnectToNewSheet (std::string newSheetName, int createNewSheet, std::string baseSheetName, int deleteExistingSheet)
{
int sheetDeleted = FALSE;
int sheetOpenAlready = false;
IDispatch * pExcelSheets = NULL;
// get the names of the currently open sheets and see if this one is open already
// otherwise add the sheet and rename it
VARIANT result1;
VariantInit ( & result1);
OLEMethod (DISPATCH_PROPERTYGET, & result1, pExcelWorkbook, L"Sheets",
"Getting the pointer to the Sheets collection in the active spreadsheet (ConnectToNewSheet)", 0);
if (result1.vt == VT_DISPATCH)
{
pExcelSheets = result1.pdispVal;
VARIANT result2;
VariantInit ( & result2);
OLEMethod (DISPATCH_PROPERTYGET, & result2, pExcelSheets, L"Count",
"Get the number of sheets in the spreadsheet (ConnectToNewSheet)", 0);
int numberOfSheets = 0;
if (result2.vt == VT_I4)
numberOfSheets = result2.intVal;
if (numberOfSheets > 0)
{
for (int i = 0; i < numberOfSheets && ! sheetOpenAlready; i++)
{
VARIANT result3;
VariantInit ( & result3);
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, & result3, pExcelSheets, L"Item",
"Set the index of the sheet of the active spreadsheet (ConnectToNewSheet)", 1, itemNumber);
if (result3.vt == VT_DISPATCH)
{
IDispatch *pDisp = result3.pdispVal;
VARIANT result4;
VariantInit ( & result4);
OLEMethod (DISPATCH_PROPERTYGET, & result4, pDisp, L"Name",
"Get the name of the indexed sheet of the spreadsheet (ConnectToNewSheet)", 0);
if (result4.vt == VT_BSTR)
{
// this will be the sheet name
std::string sheetName = _bstr_t (result4.bstrVal);
if (sheetName == newSheetName)
{
sheetOpenAlready = true;
// if there is a current excel sheet then release it
if (pExcelSheet)
{
pExcelSheet -> Release ();
pExcelSheet = NULL;
}
// copy the dispatch pointer to the workbook pointer
pExcelSheet = pDisp;
VARIANT result5;
VariantInit ( & result5);
OLEMethod (DISPATCH_PROPERTYGET, & result5, pDisp, L"Activate",
"Activate the sheet of the spreadsheet (ConnectToNewSheet)", 0);
int res = result5.vt;
if (deleteExistingSheet)
{
sheetOpenAlready = false;
pExcelSheet = NULL;
// gotta turn off the display alerts or will get a message from excel asking if it can delete
VARIANT result6;
VariantInit ( & result6);
VARIANT displayAlerts;
VariantInit ( & displayAlerts);
displayAlerts.vt = VT_BOOL;
displayAlerts.boolVal = false;
OLEMethod (DISPATCH_PROPERTYPUT, & result6, pExcelApplication, L"DisplayAlerts",
"Turn display alerts off so we can delete a sheet in silence (ConnectToNewSheet)",
1, displayAlerts);
int res6 = result6.vt;
VARIANT result7;
VariantInit ( & result7);
OLEMethod (DISPATCH_PROPERTYGET, & result7, pDisp, L"Delete",
"Delete the current sheet in the spreadsheet (ConnectToNewSheet)", 0);
int res7 = result7.vt;
// gotta turn off the display alerts or will get a message from excel asking if it can delete
VARIANT result8;
VariantInit ( & result8);
VARIANT displayAlerts8;
VariantInit ( & displayAlerts8);
displayAlerts8.vt = VT_BOOL;
displayAlerts8.boolVal = true;
OLEMethod (DISPATCH_PROPERTYPUT, & result8, pExcelApplication, L"DisplayAlerts",
"Turn display alerts back on after deleting a sheet in the spreadsheet (ConnectToNewSheet)",
1, displayAlerts8);
int res8 = result8.vt;
pDisp -> Release ();
// get out of this for loop since we are finished with it
break;
}
}
else
pDisp -> Release ();
}
}
}
}
}
else
return false;
// we need to create the sheet file if not done already
if ( ! sheetOpenAlready && pExcelSheets && createNewSheet)
{
pExcelSheets = result1.pdispVal;
VARIANT result2;
VariantInit ( & result2);
OLEMethod (DISPATCH_PROPERTYGET, & result2, pExcelSheets, L"Count",
"Getting the number of sheets in the active spreadsheet (ConnectToNewSheet)", 0);
int numberOfSheets = 0;
if (result2.vt == VT_I4)
numberOfSheets = result2.intVal;
if (numberOfSheets > 0)
{
for (int i = 0; i < numberOfSheets && ! sheetOpenAlready; i++)
{
VARIANT result3;
VariantInit ( & result3);
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, & result3, pExcelSheets, L"Item",
"Set the index of the active sheet of the spreadsheet (ConnectToNewSheet)", 1, itemNumber);
if (result3.vt == VT_DISPATCH)
{
IDispatch *pDisp = result3.pdispVal;
VARIANT result4;
VariantInit ( & result4);
OLEMethod (DISPATCH_PROPERTYGET, & result4, pDisp, L"Name",
"Get the name of the indexed sheet of the active spreadsheet (ConnectToNewSheet)", 0);
if (result4.vt == VT_BSTR)
{
// this will be the sheet name
std::string sheetName = _bstr_t (result4.bstrVal);
if (sheetName == baseSheetName)
{
sheetOpenAlready = true;
// if there is a current excel sheet then release it
if (pExcelSheet)
{
pExcelSheet -> Release ();
pExcelSheet = NULL;
}
// copy the dispatch pointer to the workbook pointer
pExcelSheet = pDisp;
VARIANT result5;
VariantInit ( & result5);
OLEMethod (DISPATCH_PROPERTYGET, & result5, pDisp, L"Activate",
"Set the spreadsheet back to the sheet in the active spreadsheet (ConnectToNewSheet)", 0);
int res = result5.vt;
}
else
pDisp -> Release ();
}
}
}
}
// now create the new sheet, hopefully in the proper place
VARIANT result3;
VariantInit ( & result3);
OLEMethod (DISPATCH_METHOD, & result3, pExcelSheets, L"Add",
"Add a new sheet to the active spreadsheet (ConnectToNewSheet)", 0);
// copy the dispatch pointer to the sheet pointer
if (result3.vt == VT_DISPATCH)
{
// if there is a current excel sheet then release it
if (pExcelSheet)
{
pExcelSheet -> Release ();
pExcelSheet = NULL;
}
pExcelSheet = result3.pdispVal;
// now name the sheet to the new name
VARIANT result4;
VariantInit ( & result4);
VARIANT fnameNew;
fnameNew.vt = VT_BSTR;
_bstr_t sheetNameNewBstr = _bstr_t (newSheetName.c_str ());
fnameNew.bstrVal = sheetNameNewBstr;
OLEMethod (DISPATCH_PROPERTYPUT, & result4, pExcelSheet, L"Name",
"Set the name of the new sheet in the active spreadsheet (ConnectToNewSheet)", 1, fnameNew);
int res = result4.vt;
}
else
return false;
}
if (pExcelSheets)
pExcelSheets -> Release ();
return true;
}
Lynn