Automation and XLL add-ins

Discussion in 'C++' started by Steven Miller, Oct 15, 2010.

  1. 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
     
    Steven Miller, Oct 15, 2010
    #1
    1. Advertising

  2. 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
     
    Victor Bazarov, Oct 15, 2010
    #2
    1. Advertising

  3. On Oct 15, 9:21 pm, Victor Bazarov <> 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++ - 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 ;)
     
    Steven Miller, Oct 15, 2010
    #3
  4. Steven Miller

    Öö Tiib Guest

    On 15 okt, 22:34, Steven Miller <> wrote:
    > On Oct 15, 9:21 pm, Victor Bazarov <> 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++ - 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.
     
    Öö Tiib, Oct 15, 2010
    #4
  5. On Oct 15, 9:52 pm, Victor Bazarov <> wrote:
    > On 10/15/2010 3:34 PM, Steven Miller wrote:
    >
    >
    >
    >
    >
    > > On Oct 15, 9:21 pm, Victor Bazarov<>  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
    >
    > > 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
    > --
    > I do not respond to top-posted replies, please don't ask


    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.
     
    Steven Miller, Oct 15, 2010
    #5
  6. Steven Miller

    Lynn McGuire Guest

    > 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
     
    Lynn McGuire, Oct 15, 2010
    #6
  7. On Oct 15, 2:27 pm, Steven Miller <> wrote:
    > On Oct 15, 9:52 pm, Victor Bazarov <> wrote:
    >
    >
    >
    > > On 10/15/2010 3:34 PM, Steven Miller wrote:

    >
    > > > On Oct 15, 9:21 pm, Victor Bazarov<>  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

    >
    > > > 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
    > > --
    > > I do not respond to top-posted replies, please don't ask

    >
    > 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.
     
    Joshua Maurice, Oct 15, 2010
    #7
  8. Steven Miller

    Lynn McGuire Guest

    > 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, Oct 15, 2010
    #8
  9. Steven Miller

    Lynn McGuire Guest

    > 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, Oct 15, 2010
    #9
  10. Steven Miller

    Lynn McGuire Guest

    > 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
     
    Lynn McGuire, Oct 15, 2010
    #10
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Fistandantilus

    Add-ins newsgroup

    Fistandantilus, Sep 15, 2003, in forum: ASP .Net
    Replies:
    2
    Views:
    415
    Dino Chiesa [MSFT]
    Sep 17, 2003
  2. Alex Vinokur
    Replies:
    4
    Views:
    493
    Ron Natalie
    Sep 24, 2003
  3. Acken
    Replies:
    4
    Views:
    2,383
    Acken
    Dec 24, 2005
  4. apondu
    Replies:
    0
    Views:
    631
    apondu
    Jul 19, 2007
  5. Nicopilami

    VS2008 Express - add ins ?

    Nicopilami, Apr 11, 2008, in forum: ASP .Net
    Replies:
    2
    Views:
    337
    Nicopilami
    Apr 11, 2008
Loading...

Share This Page