Issue writing reports to Excel 2013 - Com Handle Errors

[nkellyddi]

Hi All,

Using an application to output data to excel (OE 11.5) and a client has recently changed their version of MS Office and Excel to 2013 and we are now seeing com handle errors.

This works no problem on Excel 2007 and 2010 and this is running the same code across a number of different clients.

Has anyone experienced issues with outputting to Excel 2013?

Kind regards,
Niall

 

[joey.jeremiah]

Office object model is not 100% backwards compatible (if that is the problem).

Besides OLE/ActiveX is a very old legacy technology with tons of issues - It's extremely slow (it could take minutes to create even moderate sized documents). Uses alot of resources i.e. memory, cpu etc. Doesn't scale well for creating lots of document on the server. Only runs on Windows and so on. Because of these and other reasons Microsoft introduced OpenXML.

You might want to try the free docxfactory project.

Disclosure: I wrote the project.

 

[Rob Fitzpatrick]

Using an application to output data to excel (OE 11.5) and a client has recently changed their version of MS Office and Excel to 2013 and we are now seeing com handle errors.

It would be helpful to see the COM errors you are seeing and also to know what your code is attempting to do.

I know we ran into one issue with Word in our Office 2013 testing. I think it was related to a template property's default value (read only maybe?) changing from false to true in Word 2013. I wasn't involved directly but my understanding is that it was a simple fix.

 

[nkellyddi]

/* Now set up a an Excel sheet. */
CREATE "Excel.Application":U chRepExApp.
chRepExApp:VISIBLE = FALSE.
ASSIGN
chRepWorkBook = chRepExApp:Workbooks:ADD()
chRepWorkSheet = chRepExApp:Sheets:ITEM(iSheet).
/* Add Extra Sheets */
DO iSheet = 1 TO 9:
IF iSheet >= 3 THEN
chRepExApp:Sheets:ADD(,chRepExApp:Sheets:ITEM(iSheet),1,).
chRepExApp:Sheets:ITEM(iSheet):COLUMNS:range("AZ":U):numberformat = "@":U.
END.
ASSIGN iSheet = 1.
chRepWorkSheet:COLUMNS:range("AZ":U):numberformat = "@":U.
ASSIGN cExcelRep = cRepFile + ".xls":U.
/* Set the screen updating to no */
chRepExApp:ScreenUpdating = NO.

 

[Rob Fitzpatrick]

Thanks. Please use [ CODE ] [ /CODE ] tags (without the spaces) to enclose ABL code. It makes it much more readable. Like this:

create "excel.application":u chrepexapp.

chrepexapp:visible = false.
assign
  chrepworkbook = chrepexapp:workbooks:add()
  chrepworksheet = chrepexapp:sheets:item(isheet).

/* add extra sheets */
do isheet = 1 to 9:
  if isheet >= 3 then
  chrepexapp:sheets:add(,chrepexapp:sheets:item(isheet),1,).
  chrepexapp:sheets:item(isheet):columns:range("a:dz":u):numberformat = "@":u.
end.

assign isheet = 1.
chrepworksheet:columns:range("a:dz":u):numberformat = "@":u.
assign cexcelrep = crepfile + ".xls":u.

/* set the screen updating to no */
chrepexapp:screenupdating = no.