Automation and XLL add-ins

[Steven Miller]

Hi,

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?

Thanks

 

[Victor Bazarov]

I'm developing an add-in in C++ using the Excel 2010 XLL SDK, [...]

MS Excel SDK is off-topic here. Try the newsgroup dedicated to Excel
programming. I would search for one with "microsoft" and "excel" in the
name.

V

 

[Steven Miller]

I'm developing an add-in in C++ using the Excel 2010 XLL SDK, [...]

MS Excel SDK is off-topic here.  Try the newsgroup dedicated to Excel
programming.  I would search for one with "microsoft" and "excel" in the
name.

V

Hey Victor,

Thanks for your input.

Well, I'm asking a question about programming in C++ - that it so just
happens to be for final implementation in Excel I thought was
irrelevant. There's nothing Excel-specific in my question (it's all
automation in C++, which I guessed was C++-specific), so I figured the
Excel-guys wouldn't be able to help me; the C+ guys would - but I
will certainly give them a try

If anyone should happen to stumble upon this question and happen to
know a possible answer, I'd still love for you to hear from you, in
case the Excel guys are as clueless as me

 

[Öö Tiib]

I'm developing an add-in in C++ using the Excel 2010 XLL SDK, [...]

MS Excel SDK is off-topic here.  Try the newsgroup dedicated to Excel
programming.  I would search for one with "microsoft" and "excel" in the
name.

V

Hey Victor,

Thanks for your input.

Well, I'm asking a question about programming in C++ - that it so just
happens to be for final implementation in Excel I thought was
irrelevant. There's nothing Excel-specific in my question (it's all
automation in C++, which I guessed was C++-specific), so I figured the
Excel-guys wouldn't be able to help me; the C+ guys would - but I
will certainly give them a try

If anyone should happen to stumble upon this question and happen to
know a possible answer, I'd still love for you to hear from you, in
case the Excel guys are as clueless as me

This is perhaps unlikely. The link you posted has "Start Visual C++
6.0" as first step. There haven't been any legacy maintenance works
during past 5 years involving MSVC 6.0, so the information in the
article is likely terribly outdated. The whole "Automation" there is
perhaps something MS Visual Basic 6.0 specific that even MS itself
does not support anymore.

 

[Steven Miller]

On 10/15/2010 2:20 PM, Steven Miller wrote:
I'm developing an add-in in C++ using the Excel 2010 XLL SDK, [...]
MS Excel SDK is off-topic here.  Try the newsgroup dedicated to Excel
programming.  I would search for one with "microsoft" and "excel" in the
name.
V

Hey Victor,

Thanks for your input.

Well, I'm asking a question about programming in C++

No, you're not.  You're asking a question about putting data in the
worksheet.  What language you use for it is pretty much irrelevant.

 > - that it so just

happens to be for final implementation in Excel I thought was
irrelevant. There's nothing Excel-specific in my question

Oh...  I thought there was, when I read "I wish to put the data onto the
sheet on which the UDF was entered - not a new sheet in a new workbook".
  Sheet is not specific to Excel?  Workbook is not specific to Excel?

 > (it's all

automation in C++, which I guessed was C++-specific),

C++ language does not define "automation".  It must be specific to
Windows, then.  Still off-topic.  And, BTW, "automation" is most likely
the same in Visual Basic or in Delphi, which makes it not
language-specific and as such, again, off-topic here.

 > so I figured the

Excel-guys wouldn't be able to help me; the C+ guys would - but I
will certainly give them a try

How did you "figure"?  Read the archives of this forum, read the FAQ, to
see what topics are discussed here.  Don't assume, please.

[..]

V

Wow, talk about a hostile approach to posting. Trolls like you really
shouldn't give others advise on how to behave. What a despicable human
being you are.

 

[Lynn McGuire]

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 = 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

 

[Joshua Maurice]

On 10/15/2010 3:34 PM, Steven Miller wrote:

On 10/15/2010 2:20 PM, Steven Miller wrote:
I'm developing an add-in in C++ using the Excel 2010 XLL SDK, [...]
MS Excel SDK is off-topic here.  Try the newsgroup dedicated to Excel
programming.  I would search for one with "microsoft" and "excel" in the
name.
V
--
I do not respond to top-posted replies, please don't ask
Hey Victor,
Thanks for your input.
Well, I'm asking a question about programming in C++

No, you're not.  You're asking a question about putting data in the
worksheet.  What language you use for it is pretty much irrelevant.

 > - that it so just

Oh...  I thought there was, when I read "I wish to put the data onto the
sheet on which the UDF was entered - not a new sheet in a new workbook"..
  Sheet is not specific to Excel?  Workbook is not specific to Excel?

 > (it's all

C++ language does not define "automation".  It must be specific to
Windows, then.  Still off-topic.  And, BTW, "automation" is most likely
the same in Visual Basic or in Delphi, which makes it not
language-specific and as such, again, off-topic here.

 > so I figured the

How did you "figure"?  Read the archives of this forum, read the FAQ, to
see what topics are discussed here.  Don't assume, please.

[..]

V

Wow, talk about a hostile approach to posting. Trolls like you really
shouldn't give others advise on how to behave. What a despicable human
being you are.

Slightly hostile, perhaps. It's usenet culture. It values accuracy
over politeness.

But anyway, the rest of the people are probably right that this more
an Excel question than a C++ question. You want to know how to modify
cells of an existing spreadsheet instead of creating a new one. That's
definitely something which requires very Excel-specific knowledge,
which basically puts it outside the scope of this newsgroup.

 

[Lynn McGuire]

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

 

[Lynn McGuire]

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

 

[Lynn McGuire]

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 != '$')
selection += absoluteReference ;
}
}
pDisp -> Release ();
}

return selection;
}


Lynn