QAxObject excel Sort Column

Allllex

Follow this example and other google example, i manage to perform the basic operation about an excel sheet. However, i cannot find a method to perform a "Sort Column" action.

In VB , it's something like

Range("a1:a10").Sort Key1:=Range("a1")

Does anyone know how I can write a SortColumn() via QAxObject ?

hskoglund

Hi, it's (almost :-) the same when using QAxObject, try something like this:

auto randomName = []
{
    QString s,c;
    do
    {
        c = QString("abcdehijkmoyprsu").mid(rand() % 20,1);
        s += c;
    } while (!c.isEmpty() || s.isEmpty());
    return s;
};

auto excel     = new QAxObject("Excel.Application");
auto workbooks = excel->querySubObject("Workbooks");
auto workbook  = workbooks->querySubObject("Add");
auto sheets    = workbook->querySubObject("Worksheets");
auto sheet     = sheets->querySubObject("Item(int)",1);

qDebug() << "Fill column with random chars";
for (int r = 1; (r <= 10); ++r)
    sheet->querySubObject("Cells(int,int)",r,1)->setProperty("Value",randomName());

qDebug() << "Before sorting";
for (int r = 1; (r <= 10); ++r)
    qDebug() << sheet->querySubObject("Cells(int,int)",r,1)->dynamicCall("Value()");

// do the sort
sheet->querySubObject("Range(a1:a10)")->dynamicCall("Sort(Key1:=QAxObject*)",sheet->querySubObject("Range(a1)")->asVariant());

qDebug() << "After sorting";
for (int r = 1; (r <= 10); ++r)
    qDebug() << sheet->querySubObject("Cells(int,int)",r,1)->dynamicCall("Value()");

excel->dynamicCall("Quit()");

Allllex

Thanks a lot!!! This is the syntax I'm looking for!

sheet->querySubObject("Range(a1:a10)")->dynamicCall("Sort(Key1:=QAxObject*)",sheet->querySubObject("Range(a1)")->asVariant());

I wonder for other similar operation, we still have to look to MS's VBA documentation. And then translate/mirror to QT, am i right? Is there any qt doc about the possible or common parameters/candidates for members like

dynamicCall(const char *, QList<QVariant> &) : QVariant

JonB

@Allllex
I haven't used QAxObject, so I hope I'm not speaking out of turn, but, yes, for calling VBA-type stuff from Qt or other frameworks (which I have done) I do expect to have to refer to VBA docs. There are so many possibilities, it's not really up to the calling framework to document what you can do, in my experience you have to find VBA docs/examples and adapt to your particular wrapper functions.

Allllex

@JonB

Seems you confirm what i expect.
I will look up VBA docs. Thanks!