QAxObject and Find function in Excel

[Oda412]

Good day!
I'm trying to find in Excel the cell wich contains text "{z8}" and then get it's row number:

#include <QAxObject>
#include <QGuiApplication>
#include <QApplication>
#include <QQmlApplicationEngine>
#include <QQmlContext>

int main(int argc, char *argv[])
{
    QApplication a(argc, argv);

    QAxObject* mExcel=new QAxObject("Excel.Application");
    mExcel->setProperty("Visible", false);
    QAxObject *workbooks = mExcel->querySubObject( "Workbooks" );
    QAxObject *workbook = workbooks->querySubObject( "Open(const QString&)", QString("C:\\Users\\Olga\\Desktop\\test.xlsx") );
    QAxObject *mSheets = workbook->querySubObject( "Worksheets" );
    QAxObject *StatSheet = mSheets->querySubObject( "Item(const QVariant&)", QVariant("Sheet1") );
    QAxObject* cells = StatSheet->querySubObject("Cells()");

    QVariant What="{z8}";
    QVariant After="ActiveCell";
    QVariant LookIn=-4123;
    QVariant LookAt=2; //xlPart
    QVariant SearchOrder=1; //xlByRows
    QVariant SearchDirection=1;
    QVariant MatchCase=false;
    QVariant SearchFormat=false;

    QVariantList list_ = { What, After, LookIn, LookAt, SearchOrder, SearchDirection,MatchCase,SearchFormat};
    QAxObject* cell=cells->querySubObject("Find(const QVariant&,const QVariant&,const QVariant&,const QVariant&,const QVariant&,const QVariant&,const QVariant&,const QVariant&)",list_);
    cell->dynamicCall("Activate()");
    int cr= cell-> property("Row").toInt();
    qDebug() << "The row number of cell is : " << cr;

    workbook->dynamicCall("Close()");
    workbook->dynamicCall("Quit()");
    return 0;
}

But there is an error: "QAxBase: Error calling IDispatch member Find: Exception thrown by server Code : -2147352571"

Could you help me, please?

Best regards,
Olga

[JonB]

@Oda412
Excel OLE Automation is not good at providing any helpful error messages. I would guess there is something wrong in the parameters you pass (assuming the method signature is correct). But we do not know what they mean/should be. For example, LookIn=-4123 or SearchFormat=false might be wrong.

Have you tried/tested this? I think if you "record macro" and do the find you get to see the generated VBA for this?

[Oda412]

@JonB yes, I've recorded a macro in VBA.
The working macro code in VBA is:

Sub macro1()

' == selecting all cells in the worksheet ==
    
   Cells.Select
    
' == find function ==
    
    Cells.Find( _
    What:="{z8}", _
    After:=ActiveCell, _
    LookIn:=xlFormulas, _
    LookAt:=xlPart, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, _
    MatchCase:=False, _
    SearchFormat:=False).Activate
        
' == displaying the row number ==
    
    MsgBox ActiveCell.Row

End Sub

[JonB]

@Oda412 said in QAxObject and Find function in Excel:

After:=ActiveCell

You may be optimistic thinking that would map to your

QVariant After="ActiveCell";

That is a string, ActiveCell should be a VBA object I think.

Try simplifying your case. Try whatever it is to pass a "null/empty" variant for that one to let it default to searching from the top, while you get it going.

[Oda412]

@JonB thank you for your help!
The solution is follows:

#include "widget.h"
#include <QAxObject>
#include <QApplication>

int main(int argc, char *argv[])
{
    QApplication a(argc, argv);

    QAxObject* mExcel=new QAxObject("Excel.Application");
    mExcel->setProperty("Visible", false);
    QAxObject *workbooks = mExcel->querySubObject( "Workbooks" );
    QAxObject *workbook = workbooks->querySubObject( "Open(const QString&)", QString("C:\\Users\\Olga\\Desktop\\test.xlsx") );
    QAxObject *mSheets = workbook->querySubObject( "Worksheets" );
    QAxObject *StatSheet = mSheets->querySubObject( "Item(const QVariant&)", QVariant("Sheet1") );
    QAxObject* cells = StatSheet->querySubObject("Cells()");
    QVariant What="{z8}";

    QAxObject* cell=cells->querySubObject("Find(const QVariant&)",What);
    cell->dynamicCall("Activate()");
    int cr= cell-> property("Row").toInt();
    qDebug() << "Number of row is " << cr;

    workbook->dynamicCall("Close()");
    workbook->dynamicCall("Quit()");
    return 0;
}

Best regards,
Olga