QAxBase: Error calling IDispatch member Worksheets: Unknown error



  • 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


  • Qt Champions 2016

    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.



  • Hi,

    It's excel 2013


  • Qt Champions 2016

    @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



  • 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)


  • Qt Champions 2016

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



  • Hi,

    I just tried, same error...


  • Qt Champions 2016

    @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();
      }
    }
    
    


  • 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.


  • Qt Champions 2016

    @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.


  • Qt Champions 2016

    @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


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.