Hi,
I have gone through a lot of posts about Excel output, macros and templates but have not been able to zoom in on a solution to this particular issue.

Overview
I am running a simple focexec from Report Caster to produce an excel output which is bursted and emailed to recipients outside of my company.
Running Webfocus 7.1.7 on Windows 7 Using Microsoft Office 2010

Problem: Business Customer requires the Excel output to have several Page Setup options overridden, specifically:

Page: Orientation to Landscape
Margins: customized values in margins and Center on Page horizontally
Header/Footer: add a Customized Footer
Sheet:: Print Titles: Rows to repeat at top $4:$4 and Print: Gridlines

Looking for an easy way to get control of the Excel Page Setup options without using macros.

Details:
We have used macros/templates/.MHT files successfully to accomplish this, however, many recipients of the Excel output report problems due to the embedded macros AND with Office 2010 we can no longer save the macros in the .MHT format as well.

I have attempted using templates without the macros with very limited success. On whatever sheet (SHEET1) I tell Webfocus to place the data into, ALL the Page Setup options get overridden, and on the other 2 sheets (SHEET2, SHEET3) only the margin and footer options remain intact, dropping the Landscape , the gridlines, the repeating title rows. I have placed some minimal data into each of the 3 sheets as well as I understand that must be done when using templates.

Some additional notes:
Focexec and data are MVS mainframe accessed through EDASERVE (We are not using Developers Studio to build the focexecs)
report output set to: ON TABLE PCHOLD FORMAT EXL2K
OR when using templates: ON TABLE PCHOLD FORMAT EXL2K FORMULA TEMPLATE 'MYTEMPL1' SHEETNUMBER 1
no problems accessing the templates, just keeping the Page Setup options is the issue

Of course I can get the Page Setup options the way I want them if I manually edit the Report Caster Excel output and in the "@page" section and the xml section, add the following into the output:
mso-page-orientation: landscape
mso-footer-data: "My highly customized footer &R Page &P of &N";
margin: .85in .75in .85in .75in;
mso-header-margin: .5in;
mso-footer-margin: .5in;
add lines to the xml section , , and =Sheet1!$4:$4

Any suggestions short of editing the final output are greatly appreciated.
Thanks,

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

FYI

If you add this code to your output, you can turn on the page numbers.

DEFINE FILE...
Page_Number/A70 = '<style><!--@page {mso-footer-data:"&|CPage &|P of &|N";}--></style>' ;
END
TABLE FILE ...
HEADING or ON TABLE SUBHEAD
"<Page_Number.....

You can also set the print area with this technique.

I guess you could do some of the other things you want with a little investigation on how Excel stores it.

SET COMPOUND = OPEN NOBREAK

DEFINE FILE CAR
COL0/A25= 'Clearance/Type';
COL1/A25= 'Count';
COL2/A25= 'Average Cycle Time (Days)';

Page_Number/A70 = '<style><!--@page {mso-footer-data:"&|CPage &|P of &|N";}--></style>' ;
END
TABLE FILE CAR
HEADING
"<+0 <COL0<+0 <COL1<+0 <COL2"
PRINT SEATS
BY COUNTRY
BY CAR NOPRINT
ON COUNTRY RECAP
FLD0/A250= COUNTRY | '/ ' | CAR;
CLRTYP/A500='<td align=left  x:num x:fmla=''=A1''>0</td>';
ON COUNTRY SUBFOOT
"<FLD0<CLRTYP"
ON TABLE SUBHEAD
"<Page_Number SUMMARY - BY CLEARANCE - TYPE"
ON TABLE SUBFOOT
""
""
ON TABLE SET STYLE *
TYPE=TITLE, COLOR=NAVY, STYLE=BOLD+UNDERLINE, $
TYPE=REPORT, TITLETEXT='Testing', $
TYPE=TABHEADING,STYLE=BOLD,$
TYPE=SUBHEAD,STYLE=BOLD,$
TYPE=HEADING, LINE=1, STYLE=BOLD+UNDERLINE, COLOR=NAVY,$
TYPE=HEADING,HEADALIGN=BODY,$
TYPE=HEADING,OBJECT=TEXT,LINE=1,ITEM=1, STYLE=BOLD+UNDERLINE,
COLOR=NAVY,$
TYPE=HEADING,OBJECT=TEXT,LINE=1,ITEM=2, STYLE=BOLD+UNDERLINE,
COLOR=NAVY,$
TYPE=HEADING,OBJECT=TEXT,LINE=1,ITEM=3, STYLE=BOLD+UNDERLINE,
COLOR=NAVY,$
ENDSTYLE
ON TABLE PCHOLD FORMAT EXL2K
END

SET COMPOUND=CLOSE

-*******************************************************************************
-* Add an extra line with the Page Breaks

TABLE   FILE SYSTABLE
 PRINT  
        COMPUTE PgrBreaks/A400 = '<head><!--[if gte mso 9]><xml> <x:WorksheetOptions><x:Print><x:ValidPrinterInfo/>'
                               | '<x:Scale>63</x:Scale></x:Print></x:WorksheetOptions>'
                               | '<x:PageBreaks>'
                               | '<x:RowBreaks>'
                               | '<x:RowBreak><x:Row>5</x:Row><x:ColEnd>6</x:ColEnd></x:RowBreak>'
                               | '<x:RowBreak><x:Row>10</x:Row><x:ColEnd>6</x:ColEnd></x:RowBreak>'
                               | '</x:RowBreaks></x:PageBreaks></xml><![endif]--></head>' ; AS ''

 BY     NAME NOPRINT

 WHERE  RECORDLIMIT EQ 1

 ON     TABLE SET LINES 99999
ON TABLE PCHOLD FORMAT EXL2K
 ON     TABLE SET STYLE *
 TYPE=REPORT, FONT=ARIAL, SIZE=12, $
END

-RUN

@WAZ ---

Unravelling the guts:

<head>
<!--[if gte mso 9]>
  <xml>
    <x:WorksheetOptions>
      <x:Print>
        <x:ValidPrinterInfo/>
        <x:Scale>63</x:Scale>
      </x:Print>
    </x:WorksheetOptions>
    <x:PageBreaks>
      <x:RowBreaks>
        <x:RowBreak>
          <x:Row>5</x:Row>
          <x:ColEnd>6</x:ColEnd>
        </x:RowBreak>
        <x:RowBreak>
          <x:Row>10</x:Row>
          <x:ColEnd>6</x:ColEnd>
        </x:RowBreak>
      </x:RowBreaks>
    </x:PageBreaks>
  </xml>
<![endif]-->
</head>

!

I gather from your remark about "experimentation" that this is thoroughly undocumented.

It takes advantage of the way Excel reads the MHT file.

@Waz and j.gross....Thanks very much for the suggestions, I did try the first method using the Page_Number/A70 but the heading with the mso info just gets placed in the first row of the excel output and is basically treated as heading data and therefore does not treated as Excel Page Setup information, I will give that another try to make sure i am doing it correctly and will try the other technique as well...Best Regards

If you would like me to look at your code, send me a PM.