QAxBase: Error calling IDispatch member Worksheets: Unknown error

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