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
-
Hi
What version of Excel?
Seems to be from Office 2013 and later
https://msdn.microsoft.com/en-us/library/office/ff194464.aspxI would test with some VBA to see if it does like it.
-
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_QtThere 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)
-
@CBenussi
Did you try
QAxObject * workbooks = excel.querySubObject("WorkBooks");
workbooks->dynamicCall("Add");
QAxObject * workbook = excel.querySubObject("ActiveWorkBook");
QAxObject * worksheets = workbook->querySubObject("WorkSheets"); -
@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:
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.
-
@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/tryUpdate: CAnnot find a way to load the screenshot, sorry.
Use external site. like postimg.org or any other.
-
@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