C++ Excel Automation - Checking if a named range is defined

Question

0.00/5 (No votes)

See more:

I have created Excel 2007 automation app with .NET 2012 MFC using wrapper classes. Things have generally gone OK thanks to information gleaned from articles on CodeProject and MSDN etc. but currently I have a problem which I've so far failed to solve, I want to confirm that a named range exists in a workbook. I understand that this can only be done by iterating through the list of names defined for the workbook and comparing each entry against the target value.

I have a solution the problem using C# using the following code:

C#

try
{
    Excel.Application objApp = new Excel.Application();
    Excel.Workbook objWB = objApp.Workbooks.Open(textBox1.Text);

    foreach (Excel.Name name in objWB.Names)
    {
        if (name.Name == "DR_ClosureTracking")
        {
            bFound = true;
            break;
        }
    }

}
catch (Exception theException)
{
    m_theException = theException;
}

But what I really want is the the equivalent in C++, but so far I only have the following:

C++

if ((lpDisp = pAppPtr->get_ActiveWorkbook()) != NULL)
{
    pWorkbook->AttachDispatch(lpDisp);

    if ((lpDisp = pWorkbook->get_Names()) != NULL)
    {
        pNames->AttachDispatch(lpDisp);

        for (i=0; i<pNames->get_Count(); i++)
        {
        }
    }
}

Based on my understanding, the crux of my problem is that I don't know how the iterate through the list of values bounded by the for loop. Based on the C# solution for the same workbook, I know that pNames->get_Count() returns the correct value. I have got the CName.h header file from the type library and I assume that this class is in some way used to interpret the data from the list of defined names but don't know how the get there from pNames.

I have searched long and hard for a solution to this but so far to no avail.

Any suggestions ?

Posted 11-Aug-13 5:35am

Tim-PH

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tim-PH · Answer 1 · 2013-08-12T12:03:00

After finding the following page
http://msdn.microsoft.com/en-us/library/office/bb211879%28v=office.12%29.aspx[^]
I came up with the solution below:

C++

// int nRetVal = 0;
int CExcelOLE_Func::FindName(const char * pszName, CName * pName)
{
    ...
    ...
    if (m_pWorkbook != NULL)
    {
        if ((lpDisp = m_pWorkbook->get_Names()) != NULL)
        {
            pNames->AttachDispatch(lpDisp);
    
            i = 1;
            while ((i <= pNames->get_Count()) && (nRetVal == 0))
            {
                if ((lpDisp = pNames->Item(COleVariant((short)i), varOption, varOption)) != NULL)
                {
                    pLocalName->AttachDispatch(lpDisp);
    
                    if (pLocalName->get_Name() == pszName)
                    {
                        nRetVal = 1;
                    }
                }
                i++;
            }
        }
    
    }

    ....
    ....
    return nRetVal;
}

I made a error previously by indexing the CNames collection from base 0 instead of base 1 (an easy mistake if come from a C/C++ background!). I'm interested in named ranges and I been able to obtain the starting row associated with the range using:

C#

if (pExcelFunc->FindName("DR_ClosureTracking", pName))
{
    CRange range;

    if ((lpDisp = pName->get_RefersToRange()) != NULL)
    {
        range.AttachDispatch(lpDisp);

        nBaseRow = range.get_Row();
    }
}