QAxBase: Error calling IDispatch member Worksheets: Unknown error

CBenussi

I am trying to access the elements of an Excel file (*xlsx extension) by using the class QAxObject, but my code crashes giving the above error when I call:

    QAxObject* excel = new QAxObject("Excel.Application");
    excel->setProperty("Visible", false);
    QAxObject* workbooks = excel->querySubObject("WorkBooks");
    QAxObject* workbook = workbooks->querySubObject("Open (const QString&)", QString("C:\\Qt\\ex2.xlsx"));
    QAxObject* sheet = workbook->querySubObject("Worksheets");

I am using Qt 5.7 on Windows 64 bit with MinGw. Any suggestion of what could be the problem? It seems like Worksheets is not a subobject of workbooks, but everyone is using that keyword to access the sheets of excel.

Thanks in advance

mrjj

Hi
What version of Excel?
Seems to be from Office 2013 and later
https://msdn.microsoft.com/en-us/library/office/ff194464.aspx

I would test with some VBA to see if it does like it.

CBenussi

Hi,

It's excel 2013

mrjj

@CBenussi

Hi
This fails ( for me, even i added file at path)
QAxObject* workbook = workbooks->querySubObject("Open (const QString&)", QString("C:\Qt\ex2.xlsx"));

so workbooks is NULL (hence you crash)

Did you use this sample ?
https://wiki.qt.io/Using_ActiveX_Object_in_Qt

There is also
https://forum.qt.io/topic/1840/working-with-excel-sheet

CBenussi

Hi,

The workbook opening does not fail, indeed if I give him an inexistent file I receive an error telling me that file does not exists. SO the file is surely found, and if I remove the last line (quering for the workbooks) I have no errors.

I used both the working examples given in your links, trying also:

    QAxObject *excel;
    QAxObject *wbooks;
    QAxObject *book;
    QAxObject *sheets;
    QAxObject *cell;
    excel = new QAxObject("Excel.Application");
    excel->setProperty("Visible", false);
    wbooks = excel->querySubObject("Workbooks");
    book = wbooks->querySubObject("Open (const QString&)", "C:\\Qt\\ex2.xlsx" );
    sheets = book->querySubObject("Sheets");

But the error is the same (Sheets instead of Worksheets)

mrjj

@CBenussi
Did you try
QAxObject * workbooks = excel.querySubObject("WorkBooks");
workbooks->dynamicCall("Add");
QAxObject * workbook = excel.querySubObject("ActiveWorkBook");
QAxObject * worksheets = workbook->querySubObject("WorkSheets");

CBenussi

Hi,

I just tried, same error...

mrjj

@CBenussi
Hi ok.
I cant find any info on why it should not work.
I dont have office at home so can't check if I can reproduce it.

Have you tried the same in VBA?

Update:
This works at work with office 2010.
it lists the sheets
1 "Sheet1"
2 "Sheet2"
3 "Sheet3"

  QAxObject* excel = new QAxObject("Excel.Application");
  excel->setProperty("Visible", true);
  QAxObject* workbooks = excel->querySubObject("WorkBooks");
  QAxObject* workbook = workbooks->querySubObject("Open (const QString&)", QString("e:\\ex2.xlsx"));
  if (!workbook) {
    qDebug() << "workbook NULL";
    return;
  }

  QAxObject* worksheets = workbook->querySubObject("WorkSheets");

  if (!worksheets) {
    qDebug() << "sheet NULL";
    return;
  }

  int intCount = worksheets->property("Count").toInt();
  for (int i = 1; i <= intCount; i++) {
    int intVal;
    QAxObject* worksheet = workbook->querySubObject("Worksheets(int)", i);
    qDebug() << i << worksheet->property("Name").toString();
  }
}

CBenussi

Not working, but I think I understood what is the problem (it works with VBA though). When I open Excel from Qt programmatically, a window shows asking for Microsoft Office activation, then it is probably that which is blocking the access to the sheets:

prompt

I need to deactivate it in some way

Update: CAnnot find a way to load the screenshot, sorry. The window is titled: "this copy of microsoft office is not activated 2013", to go on I just need to click on "close" and everything is fine.

mrjj

@CBenussi
Hi
Yes that could block automation as you can ask it to be hidden and in that way could
use non valid Excel if always hidden.
I tried it with 2 other pcs and both gave me list so it can work.

Just register it and it will shut up. :)
Alternativ u can get 2016 for 30 days
https://products.office.com/en-us/try

Update: CAnnot find a way to load the screenshot, sorry.

Use external site. like postimg.org or any other.

mrjj

@CBenussi
Yep you should fix that with a valid key OR
if it was a trial, you can rearm it for 30 days
https://technet.microsoft.com/en-us/library/dn385362.aspx