Hello,

I am facing a problem in adding a new sheet (earlier there were 2 sheets and there was no problem) to the excel template for a report in MRE.This has been made dynamic as :

-SET &FILECTR = &FILECTR + 1 ;
-SET &HFILE='&EXCELTEMP.EVAL' | '&FILECTR.EVAL' ;
-SET &NFILE = &NFILE + 1 ;
-SET &HFILE_NAME='COMPXLSH' | '&NFILE.EVAL' ;
-SET &HFILE5 = '&EXCELTEMP.EVAL' | '&NFILE.EVAL' | '.mht';

APP FI &HFILE_NAME DISK &HFILE5.EVAL


-INCLUDE app/exec_summ_with_comm_new_sht ------ > SHEETNUMBER 2

-RUN

-SET &FILECTR = &FILECTR + 1 ;
-SET &HFILE='&EXCELTEMP.EVAL' | '&FILECTR.EVAL' ;
-SET &NFILE = &NFILE + 1 ;
-SET &HFILE_NAME='COMPXLSH' | '&NFILE.EVAL' ;
-SET &HFILE5 = '&EXCELTEMP.EVAL' | '&NFILE.EVAL' | '.mht';

APP FI &HFILE_NAME DISK &HFILE5.EVAL

-INCLUDE app/excfltrpg ------ > SHEETNUMBER 3

-RUN

-SET &FILECTR = &FILECTR + 1 ;
-SET &HFILE='&EXCELTEMP.EVAL' | '&FILECTR.EVAL' ;


-RUN

TABLE FILE FINAL
PRINT
*
ON TABLE PCHOLD FORMAT EXL2K TEMPLATE '&HFILE.EVAL' SHEETNUMBER 1
END


When I run by giving -SET ECHO = ALL ; , I see the following -

ON TABLE HOLD AS COMPXLSH2 FORMAT EXL2K TEMPLATE 'execsum1' SHEETNUMBER 2

ON TABLE HOLD AS COMPXLSH3 FORMAT EXL2K TEMPLATE 'execsum2' SHEETNUMBER 3

ON TABLE PCHOLD FORMAT EXL2K TEMPLATE 'execsum3' SHEETNUMBER 1


Everything seems to be correct, but I am getting the below error :

(FOC3289) TEMPLATE FILE: Error opening file
(FOC009) INCOMPLETE REQUEST STATEMENT
BYPASSING TO END OF COMMAND


Please guide.

Thanks !

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

quote:

ON TABLE HOLD AS COMPXLSH2 FORMAT EXL2K TEMPLATE 'execsum1' SHEETNUMBER 2

ON TABLE HOLD AS COMPXLSH3 FORMAT EXL2K TEMPLATE 'execsum2' SHEETNUMBER 3

ON TABLE PCHOLD FORMAT EXL2K TEMPLATE 'execsum3' SHEETNUMBER 1

When creating a compound template file the template for the second report must be the output from the first report
and the template for the third report must be the output from the second report.

additionally you must filedef the files with the .mht extension or WebFOCUS will not be able to find them
AND the original template must include at least the number of worksheets as set by the maximum SHEETNUMBER value

Your code follows these basic rules except for the HOLD and PCHOLD statements

They should be

1st hold
ON TABLE HOLD AS &HFILE_NAME.EVAL FORMAT EXL2K TEMPLATE 'execsum1' SHEETNUMBER 3

2nd hold
ON TABLE HOLD AS &HFILE_NAME.EVAL FORMAT EXL2K TEMPLATE '&HFILE_NAME.EVAL' SHEETNUMBER 3

3rd hold
ON TABLE PCHOLD FORMAT EXL2K TEMPLATE '&HFILE_NAME.EVAL' SHEETNUMBER 1

Alternativly change

-SET &HFILE_NAME='COMPXLSH' | '&NFILE.EVAL' ;

to

-SET &HFILE_NAME='execsum' | '&NFILE.EVAL' ;

Thanks for your reply !

Could you please tell me how to filedef the files with .mht extn and where to do that ?

Also you mentioned SHEETNUMBER 3 twice.Why is it so ?

quote:

Also you mentioned SHEETNUMBER 3 twice.Why is it so ?

That's a typo, it should be SHEETNUMBER 2 for the second one.

APP FI &HFILE_NAME DISK &HFILE5.EVAL

Should be fine as an alternative to filedef but you must have -RUN immediately after it.

If you want to use filedef then it would be

FILEDEF &HFILE_NAME.EVAL DISK &HFILE5.EVAL
-RUN

Hii,

I've tried all this but still getting the same error,after each sheet execution.I've included both APP FI and FILEDEF.

Any clue ?

Just a shot in the dark here, but you do have at least three sheets in your template file don't you?

T

Could you please tell me how to check that ? I am not sure how to check the number of sheets in a template file.

Also how can I change the sheet name ?

Thanks.

quote:

I am not sure how to check the number of sheets in a template file.

Lalita you just need to open it in Excel and count the number of tabs at the bottom.

Also make sure that the first two sheets have some content. Even a single space in a cell of the first two sheets is enough.

The problem is I am not able to execute the report for EXL2K format. I getting the below error after the execution of each sheet related code.

(FOC3289) TEMPLATE FILE: Error opening file
(FOC009) INCOMPLETE REQUEST STATEMENT
BYPASSING TO END OF COMMAND

How can I count the sheets now ?

I am confused. Am I missing on anything ?

Thanks.

Lalita,

The file you need to check is your original template file which has the .mht extention.

Looking at your original post this should be called execsum1.mht this file must exist in a folder in your APP PATH

If this file does not exist then your code will never work.

The file must be a valid excel web archive document with the .mht extension
It must be created in Excel 2003 (Excel 2007 can not be used)
The file must in your case contain 3 work sheets.
The first two work sheets must contain some data, even a single space in cell 'A1' of each sheet is sufficient.

If you have a valid .mht template file then change the name of the template in the first PCHOLD statement to use this name
Also as I posted earlier, looking at your code the template name for the second and third PCHOLD statements should be
'&HFILE_NAME.EVAL'

Hi JG,

Thanks a Ton for pointing to this !

I found the .mht file named as 'execsum1.mht'.And there was code only for 2 sheets.It is so complex. I am not sure if it is a system generated file or it was manually created. I don't know Javascripting or XML.I tried adding things for sheet 3 as per my understanding. But it did not work.

Could you please let me know the process of creating this .mht in APP PATH ?

Like if you have any template kind of thing ..

Also while debugging the report fex is there a way to see the errors in this .mht ?

Lalita,

Just copy the file 'execsum1.mht' to the same location as your .fex files.

The macros in this file are VBA not Javascript or XML and what they do is very specific to what the person
who wrote them intended.
It will very definately have been created by an individual not automatically generated.

Errors in the macro will be intercepted by the macro compiler and relate to the macro code and the content
of the worksheets that it is trying to manipulate.

The best thing from a WebFOCUS point of view is to use format HTML as your output format until the report content
is correct you can then direct it to the EXL2K TEMPLATE format.

Helping you with the macro it's self is really not possible because it is very specific to your requirements.

Ideally you need to find the person who originally created it within your organization and talk to them.

Hello JG,

Finally I am able to generate xl report with multiple sheets but with a different method,thats by using SET COMPOUND = 'OPEN' and 'CLOSE' and ON TABLE PCHOLD FORMAT EXL2K and TITLETEXT.

I couldn't find anyone who could help me on the mht thing.

Thanks a lot for your replies.

Lalita, No problem.

Thing to remember is the difference between a compound report and using templates.

Compound is for creating Excel workbooks with multiple WebFOCUS outputs on either a single tab and/or multiple tabs.
In this case all formatting or lack of it is controlled by WebFOCUS.

You only need a template when you actually want to apply a pre-existing template and/or you want a macro
to do something in the workbook for you.