QAxObjects issue with Office 365?

[Pauly]

I'm using QAxObjects to interact with excel, here is the code:

QAxObject* pApp = new QAxObject("Excel.Application", 0);
pDoc = pApp->querySubObject("Workbooks");
pContent = pDoc->querySubObject("Open(QString)", QDir::toNativeSeparators(path));
pVBProject=pContent->querySubObject("VBproject");
pVBComponent=pVBProject->querySubObject("VBComponents");

int macroCount = pVBComponent->property("Count").toInt();
for (int i=1; i<=macroCount; i++) {

    qDebug()<<QString("Module #%1 of #%2").arg(i).arg(pVBComponent->property("Count").toInt()));
    
    QAxObject* module = pVBComponent->querySubObject("Item(int)", i);
    if(module==nullptr) continue;

    qDebug()<<QString("Module \"%1\"").arg(module->property("Name").toString());

    QAxObject* codeModule = module->querySubObject("CodeModule");
    if(codeModule==nullptr) continue;
    int lineCount=codeModule->property("CountOfLines").toInt(); //<----pVBComponent->property("Count").toInt() will return 0 if placed after this point, only happen for Office 365, what is wrong?

    qDebug()<<QString("%1 lines").arg(lineCount);
}

Everything works as expected in Office 2016, but not in Office 365, where I get:

Module #1 of #7
Module "Module1"
0 lines
Module #2 of #0
Module #3 of #0
Module #4 of #0
Module #5 of #0
Module #6 of #0
Module #7 of #0

After some poking, pVBComponent is actually lost (?) in the lineCount call as indicated above in the first loop. This only happens with Office 365. What is wrong? Thanks.

Also there is an issue with SaveAs in Office 365:

pContent->dynamicCall("SaveAs(QString)", path);

this will always fails, but in Office 2016 and other office version it will work fine. Are these all related?

[JonB]

@Pauly
I see your problem. I don't know the answer, and don't know that you'll get an answer here.

I will say that when I have used Automation from other products which have a interface like QAxObject I found it very useful to design/test my code from MS VBA/within Excel etc., rather than from the product, and then translate the VBA to the product's syntax when ready. If you feel like doing that here, you should check whether the native VBA code in Excel shows the same behaviour as you are witnessing, then it's not a QAxObject issue.

[Pauly]

@JonB I tried this in VBA in Office 365 and it works:

count=ActiveWorkbook.VBProject.VBComponents(1).CodeModule.CountOfLines

So this is likely a Qt issue? And does it make sense if it works with all other Office version but not Office 365? Something special about Office 365?

[JonB]

@Pauly
I don't know, but I guess it does point more towards a Qt issue than VBA. It does look a bit like that pVBComponent is going walkies, somehow. As for difference between 365 vs 2016, who knows, the code/requirements may be different.

Dunno, try copying your setting of pVBProject & pVBComponent to inside the loop each time round. Yes, you shouldn't have to do that, but does it make any difference? Maybe do some debugging to see which precise variable it is that "goes walkies", though not sure what you're going to do with information. You're look for a "workaround" which simply works for you.