Is it possible to have a compound excel report and use a excel template to style the report and setup the print settings but only use sheetnumber 1? I looked through the forums and online but havent seen anyone do what I am trying to do. Can someone please point me in the right direction?

Below is an example of what I found when using an template with an excel compound report but its putting the report on multiple tabs which I do not want. I want everything on sheetnumber 1.

  
FILEDEF TEMPLATE2 DISK TEMPLATE2.MHT
-RUN
TABLE FILE whatever
.
.
ON TABLE HOLD AS TEMPLATE2 FORMAT EXL2K TEMPLATE 'TEMPLATE1' SHEETNUMBER 1
END
-RUN
FILEDEF TEMPLATE3 DISK TEMPLATE3.MHT
-RUN
TABLE FILE whatever
.
.
ON TABLE HOLD AS TEMPLATE3 FORMAT EXL2K TEMPLATE 'TEMPLATE2' SHEETNUMBER 2
END
-RUN
FILEDEF TEMPLATE4 DISK TEMPLATE4.MHT
-RUN
TABLE FILE whatever
.
.
ON TABLE HOLD AS TEMPLATE4 FORMAT EXL2K TEMPLATE 'TEMPLATE3' SHEETNUMBER 3
END
-RUN
TABLE FILE whatever
.
.
ON TABLE PCHOLD FORMAT EXL2K TEMPLATE 'TEMPLATE4' SHEETNUMBER 4
END
-RUN

Below is what I am trying to do but can't get it to work.

  
FILEDEF TEMPLATE2 DISK TEMPLATE2.MHT
-RUN
TABLE FILE whatever
.
.
ON TABLE HOLD AS TEMPLATE2 FORMAT EXL2K TEMPLATE 'TEMPLATE1' SHEETNUMBER 1
END
-RUN
FILEDEF TEMPLATE3 DISK TEMPLATE3.MHT
-RUN
TABLE FILE whatever
.
.
ON TABLE HOLD AS TEMPLATE3 FORMAT EXL2K TEMPLATE 'TEMPLATE2' SHEETNUMBER 1
END
-RUN
FILEDEF TEMPLATE4 DISK TEMPLATE4.MHT
-RUN
TABLE FILE whatever
.
.
ON TABLE HOLD AS TEMPLATE4 FORMAT EXL2K TEMPLATE 'TEMPLATE3' SHEETNUMBER 1
END
-RUN
TABLE FILE whatever
.
.
ON TABLE PCHOLD FORMAT EXL2K TEMPLATE 'TEMPLATE4' SHEETNUMBER 1
END
-RUN

Thanks,
prodrigu

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

This won't work, you would be writing over SHEETNUMBER 1 with each iteration.

prodrigu, PB is correct, you are just overwritting everything in sheet 1.

You could always have an auto open macro move the data from sheet 2, 3, 4, etc. to sheet 1.

Also, if this is just data, just append the data in a hold file and then in the last step dump the data into the spreadsheet.

jgelona,

Hate to ask but is it possible to show me an example?

Thanks,
prodrigu

If this is just data, do something like this:

TABLE FILE FILE1
PRINT fld1 fld2 fld3...
WHERE ....
ON TABLE HOLD
END
-*
FILEDEF HOLD DISK hold.ftm (LRECL XX RECFM V APPEND
-*
TABLE FILE FILE2
PRINT fld1 fld2 fld3...
WHERE ....
ON TABLE SAVE AS HOLD
END
-*
TABLE FILE FILE3
PRINT fld1 fld2 fld3...
WHERE ....
ON TABLE SAVE AS HOLD
END
...
TABLE FILE HOLD
PRINT *
ON TABLE PCHOLD FORMAT EXL2K TEMPLATE abc SHEETNUMBER 1
END
-RUN

In this example, all of the fld1, fld2, fld3 need to be the same format.

Do the first TABLE and follow it by a '? FILEDEF'. This will show you the LRECL for your HOLD file. This example also assumes you are doing HOLDFORMAT=ALPHA. If you are doing HOLDFORMAT=BINARY (default), use SAVB instead of SAVE.