Excel Automation-Range.FindNext() not working

Question

1.00/5 (1 vote)

See more:

Hi All,

I am trying to search a string in MSExcel sheet using following source code in VC++,MFC.

First occurrence of string is found using Range.Find() but for next occurrence Range.FindNext() is giving error:

error C2664: 'FindNext' : cannot convert parameter 1 from 'class MSExcel::Range' to 'const struct tagVARIANT &'

Referring this for 'FindNext' http://msdn.microsoft.com/en-us/library/office/ff196143.aspx[^]

C++

//////Source code////////

// Convenient values declared as ColeVariants.
COleVariant vTrue((short)TRUE),vFalse((short)FALSE);
COleVariant VOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);

MSExcel::_Application objApp;
MSExcel::_Workbook objBook;
MSExcel::Workbooks objBooks;
MSExcel::Worksheets objSheets;
MSExcel::_Worksheet objSheet;
MSExcel::Range objRange = NULL;
MSExcel::Range  objFoundRng = NULL;

int nI = 0;  
INT nFound = 0;

// Instantiate Excel and open an existing workbook.
objApp.CreateDispatch("Excel.Application");
objBooks = objApp.GetWorkbooks();


objBook = objBooks.Open(m_straryFileList.GetAt(nI),
VOptional, VOptional, VOptional, VOptional,
VOptional, VOptional, VOptional, VOptional,
VOptional, VOptional, VOptional, VOptional,
VOptional, VOptional);

objSheets = objBook.GetWorksheets();
objSheet = objSheets.GetItem(COleVariant((short)1));

//Get the range object for which you wish to retrieve the

objRange = objSheet.GetUsedRange();


//find for first match
objFoundRng = objRange.Find(COleVariant(strSearchStr), VOptional,
VOptional, xlWhole, VOptional,
xlNext, xlIgnoreCase, VOptional, VOptional);

if(objFoundRng != NULL){
    nFound++;
    //store address where first found
    CString strAddrs  = _T("");
    CString strFirstFound = objFoundRng.GetAddress(vTrue, vTrue,
    (long)1, VOptional, VOptional);
    
    //  FindNext until the first found cell is found again
    
    while (strFirstFound != strAddrs){            
        objFoundRng = objFoundRng.FindNext(objFoundRng);
        if(objFoundRng != NULL){
            nFound++;
            strAddrs = objFoundRng.GetAddress(vTrue, vTrue, (long)1, 
            VOptional, VOptional);
        }
    }   
} 



//Close the workbook without saving changes
//and quit Microsoft Excel.
objBook.Close(COleVariant((short)FALSE), VOptional, VOptional);

objApp.Quit();

Am doing anything wrong?

Any other way to do the same?

Posted 3-Aug-13 0:21am

Member 8022123

Updated 3-Aug-13 1:13am

André Kraak

v3

Add a Solution

2 solutions

Solution 1

Rather than seraching MS Excel using Find and/or FindNext function, use ADO.NET ;) It's much. much quickest...
Data Access Using ADO.NET in C++ [^]
More
Visual Studio samples[^]
C++ app automates Excel (CppAutomateExcel)[^]

Posted 4-Aug-13 9:48am

Maciej Los

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)

Member 8022123 · Accepted Answer · 2013-08-07T18:04:00

Just remove

C++

FindNext()

,
Instead used

C++

Find()

twice :)

//find for word in a range
objFoundRng = objRange.Find(COleVariant(strSearchStr), VOptional,
    xlValues, xlPart, xlByRows,
    xlNext, vFalse, VOptional, VOptional);

//find for next occurence
if(objFoundRng != NULL){
    CString strFirstFound = objFoundRng.GetAddress(vTrue, vTrue,
        (long)1, VOptional, VOptional);

    CString strNextFound = _T("");
    while(strFirstFound != strNextFound){
        objFoundRng = objFoundRng.Find(COleVariant(strSearchStr), VOptional,
            xlValues, xlPart, xlByRows,
            xlNext, vFalse, VOptional, VOptional);

        strNextFound = objFoundRng.GetAddress(vTrue, vTrue,
            (long)1, VOptional, VOptional);

    }
}