We have WF8 and I did get the EXL07 macro-enabled template to work with adding 2 data tabs of data obtained in WF. When I add a third, it won't open. I can do tab 1 and 2 or 1 and 3; but not 1 and 2 and 3.
Any advice?
Here is an abbreviated version of my code:

-SET &TPLATE = 'MDE_RPM_Draft01.xltm';
-SET &NEWTEMP = 'LEVY1MPB';
-SET &SHEETALLOC = &NEWTEMP | '.xlsm';
FILEDEF &NEWTEMP DISK &SHEETALLOC
-*
? FILEDEF
-RUN

JOIN CLEAR *
TABLE FILE table1
SUM
 field1 field2 field3 etc
BY  DAT_YER 
BY  Key
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SET BYDISPLAY ON
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS LEVY1MPB FORMAT EXL07 TEMPLATE 'MDE_RPM_Draft01.xltm' SHEETNUMBER 14
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     SUMMARY='Levy',
$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=8,
     COLOR='BLACK',
     STYLE=NORMAL,
$
ENDSTYLE
END
-RUN
-SET &LASTTEMP = &NEWTEMP | '.xlsm';
-SET &NEWTEMP = 'LEVY2MPB';
-SET &SHEETALLOC = &NEWTEMP | '.xlsm';
FILEDEF &NEWTEMP DISK &SHEETALLOC
? FILEDEF
-RUN

TABLE FILE table1
SUM
 field1 field2 field3 etc
BY  DAT_YER 
BY  Key
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SET BYDISPLAY ON
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS &NEWTEMP FORMAT EXL07 TEMPLATE 'LEVY1MPB.xlsm' SHEETNUMBER 16
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     SUMMARY='Levy',
     TITLETEXT='Levy2mpb',
$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=8,
     COLOR='BLACK',
     STYLE=NORMAL,
$
ENDSTYLE
END
-RUN
-SET &LASTTEMP = &NEWTEMP | '.xlsm';
-SET &NEWTEMP = 'LEVY3MPB';
-SET &SHEETALLOC = &NEWTEMP | '.xlsm';
FILEDEF &NEWTEMP DISK &SHEETALLOC
? FILEDEF
-RUN

TABLE FILE table3
SUM
 field1 field2 field3 etc
BY  DAT_YER 
BY  Key
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SET BYDISPLAY ON
ON TABLE SET ASNAMES ON
ON TABLE PCHOLD AS &NEWTEMP FORMAT EXL07 TEMPLATE 'LEVY2MPB.xlsm' SHEETNUMBER 18
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     SUMMARY='Levy',
     TITLETEXT='Levy3mpb',
$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=8,
     COLOR='BLACK',
     STYLE=NORMAL,
$

END
-RUN

Thanks in advance for any wisdom!

This message has been edited. Last edited by: mpbMDE,

First, I assume you have at least 18 sheets in your template.

Second, we had to name our templates with .xlmt suffix ("mt" is for macro enabled template). That would make the first HOLD look like this:

ON TABLE HOLD AS LEVY1MPB FORMAT XLSX TEMPLATE 'MDE_RPM_Draft01.xlmt' SHEETNUMBER 14

Also, we don't have to do FILEDEF for the succeeding temp files, i.e. LEVY1MPB.xlsm

My question to you is, how much data do you have.

We are on the same version as you and I have found that there is a data threshold where performance goes into the toilet versus using EXL2K templates (the problem there is that one must have a copy of excel 2003 to maintain the .mht files that 2003 builds). For us, the threshold was around 10K rows total for all sheets.

I have an open problem report with IBI and they have been able to reproduce the problem. The problem is putting data into multiple sheets with macro-enable templates. In my case, we got these errors (we are Linux on the Client and Reporting Server):

(FOC1522) ntjprerr: Java heap space. CPJAVA: Error in processing EXECUTE
(FOC1522)  command for Class ibi.jsexcel.JscomExcelUnzip

We have a report that uses EXL2K templates that I was trying to convert to EXL07. At IBI, it runs in about 20 seconds. With EXL07, it runs in about 20 minutes. Same data, same program. The ONLY difference is output format. Using EXL07 a simple compound document using the OPEN on the first sheet and CLOSE on the last one, the job runs in about 25 seconds.

You don't say what your environment it, but you may be experiencing the same problem.

That looks like you need to increase your Java heap space. You can up that in the server web-console in the Properties of each "Java Service".

Ours is initially at 512MB, but we don't have Excel workbooks with as many sheets as you do.

Also, it helps to clean up those templates if possible. Excel tends to keep undo information and people can do some horrendous duplicating of information (8 times the same 1200 dpi company logo's, for example). Comments are also a no-no apparently and we've seen issues with image-based "buttons" (standard buttons are okay). Oh, and make sure there are no references to files on people's local disks that they copied information from!

I suggested IBI to use unzipped versions of a workbook (which is basically a bunch of XML files zipped together) for internal processing so that most of the zipping and unzipping can be skipped. But apparently they're working on a rather different implementation to replace the current one, where that's no longer applicable.

I guess it's just another case of Microsoft making our lives harder

Thank you for advice. I made the changes reccommended and still have the same issue. [I actually DO want to open the excel file at the end.]

The execution runs fine, however when the third worksheet is included, several tries are made to attempt to open the file, getting messages as follows:
popup box1 "The file you are trying to open, 'filename', is in a different format than specified by the file extension. Verify that the file is not corrup[ted and is from a trusted source before opening the file. Do you want to open the file now?"
So I click 'Yes'.
New popup2 "Excel found unreadable content in 'filename'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes"
So I click 'Yes'.
An attempt is made to open the file.
It attempts to open the file and reverts back to popup1, then popup2, then popup1 then the file opens with a popup indicating thea "Excel was able to open the file by repairing or removing unreadable content.
The removed parts are listed [Several Cell information from /xl/worksheets/sheetX.xml part]
The workbook opens but theoriginal template tags are all empty and the headings are gone from my data tabs.
Here is the log from the repaired sheet:
"
error066440_07.xml

Errors were detected in file 'http://localhost:8080/foccache/ip000057010/t101932331/LEVY3MPB.xls'

Removed Part: /xl/vbaProject.bin part. (Visual Basic for Applications (VBA))Removed Records: Named range from /xl/workbook.xml part (Workbook)Removed Records: Worksheet properties from /xl/workbook.xml part (Workbook)Removed Records: Cell information from /xl/worksheets/sheet2.xml partRemoved Records: Cell information from /xl/worksheets/sheet3.xml partRemoved Records: Cell information from /xl/worksheets/sheet4.xml partRemoved Records: Cell information from /xl/worksheets/sheet14.xml partRemoved Records: Cell information from /xl/worksheets/sheet16.xml partRemoved Records: Cell information from /xl/worksheets/sheet18.xml partRepaired Records: Cell information from /xl/worksheets/sheet2.xml partRepaired Records: Column information from /xl/worksheets/sheet2.xml partRepaired Records: Cell information from /xl/worksheets/sheet3.xml partRepaired Records: Column information from /xl/worksheets/sheet3.xml partRepaired Records: Cell information from /xl/worksheets/sheet4.xml partRepaired Records: Cell information from /xl/worksheets/sheet14.xml partRepaired Records: Cell information from /xl/worksheets/sheet16.xml partRepaired Records: Cell information from /xl/worksheets/sheet18.xml part"
There are 19 tabs, but there will be a few more when I am finished.
Please let me know what you learn from your case. I, too, opened a case, but finally closed it. Nobody from IBI ever called to speak with me and they didn't understand the issue. The referred me to some documentation that I had already used and no further help was forthcoming.

Thanks Wep5622!
I did do some serious cleanup in the Template. You are spot on about that!
I will research increasing the Java heap space.
Our configuration has taken away that flexibility for users.
Thanks again!

Wep, the Java Heap size error was bogus. IBI told me the default Java Heap was 1GB (I never did find that in the documentation). We set it as high as 2GB and it made no difference. Traces found that actual heap being used was about 3MB. I agree with you in that IBI should not zip anything until all the processing is done. What they appear to do, is: report1 then create zip1, report2 then add to zip1 creating zip2; report 3 then add to zip2 creating zip3, etc. Seems to me it would be faster to do report 1, report 2, report 3, etc. then zip all reports at once.

Like I said, IBI has reproduced the problem and it has happened on a few other reports that I want to convert so I don't have to keep Excel 2003 on my machine. Our templates are basically formatting and adding things that are not easily done with WebFOCUS. For example we have some reports where we include Filtered SubTotals (Sum, Average, Count, Count Distinct) and are pretty small.

mbpMDE, that error that starts with "The file you are trying to open,..." is a result of a security feature that MS calls extension hardening. This came in Excel 2007. If you try to open a file, say a .xls file, and Excel determines it is not that format, you get that error. WebFOCUS does not generate true .xls files which is why you can get that error. There are several threads on this site on how to fix that. It requires a registry change.

Thank you jgelona! I will research that.
mpb