Is it possible, in v5.26, to create a report and then have it automatically print to one page? I have a report that will get sent to some high ranking executives that will be viewed in Excel and then that will need to be printed on one page. Basically, using WebFocus, can I do the Page set-up like in MS Excel?
This is very important for me to figure out. Any help would be much appreciated. Thanks

Unfortunately, there is no way to set your Excel page/print settings in a WebFOCUS procedure. The alternative is to launch the procedure from the spreadsheet using a WebQuery. There is complete documentation on this technique in the WF Creating Reports manual - Choosing a Display Format chapter. Also, note that in the post 5.3.2 release (we're tentatively calling 5.4) there will be a new Excel Template feature that will allow WebFOCUS to populate an Excel template. The template can have all of the page/print settings giving yo the functionality you require. Hope this helps.

Anyone know if this really happened in 5.4...and how to Populate An Excel template??

You Can actually create Excel Macro's in in versions of WebFocus prior to 7.1.

I submitted a article on how to do it to Focal Point In April 2005, but the chose not to publish it. However the relevent extract that you need is as follows.

I've posted the whole thing withing code tags so that it displays ok.

 
MACROS

Now as we all know this isn’t possible with the current versions of WebFocus (5.3x) or with previous versions 
that could deliver the EXL2K format output, to deliver stunning formats, as to control the output in Excel 2000, 
XP or 2003 the formatting needs to be performed by a macro or template.

This feature will be available in WebFocus 7.1 (AKA 5.4) and is called templates, but I suspect that it will be 
quite a while after it is finally released (end Q2/Q3) that every WebFocus site has upgraded. I am certainly 
aware of some sites still running 4.3.x.

There is a way however of getting this functionality now, with any version of WebFocus that can deliver output 
in EXL2K, EXL2K FORMULA and EXL2K PIVOT format. This output can be read by any version of 
Microsoft Excel from 2000 onwards. 

A caveat to this is that macro versions are not always backwards compatible, so any macro’s that you create 
should be created with the lowest common version of Microsoft Excel in use by your clients.

As mentioned above, the extended HTML document generated by WebFocus can be read by versions of 
Microsoft EXCEL from 2000 up and because we know all of this about the documents internal structure we can 
use “good old Focus” technique to intercept the WebFocus generated output and manipulate it.

The only thing that is different when you open the output either via a web browser or directly in Excel is that it 
will generate a security warning. The reason for this is that when you save the macro as an htm file it internally 
registers that it should be looking for the component files in either a subdirectory, under the directory where the 
Excel document is stored, or in the same directory, depending on the options you have configured in Excel. 
I believe that this only happens when the Macro is an Auto_Open Macro.
This is not an issue; all you have to do is save the document as a real .XLS file and the warning does not re-
occur because it re-writes the references.

How you do it

 This method works for FORMAT EXL2K, EXL2K FORMULA and EXL2K PIVOT from WebFocus and 
relies on the fact that  WebFocus generates a file in XHTML format although there are slight differences in the 
way that you need to treat PIVOT tables.

 First we will address EXL2K and EXL2K FORMULA

1.	Generate your output from WebFocus in EXL2K format and save the output file.

2.	Add your VBScript to perform whatever processing you require, and delete the content of the report and 
save as a Web Page (.htm NOT a Web Archive .mht).

3.	Depending on your EXCEL options settings for Web Options this will either generate a sub-folder called 
xx_files in the folder that you saved the worksheet to or generate files directly in the same folder as the xls.
(where xx is the name of your spreadsheet).
If the files are generated in a subfolder you have 2 files, editdata.mso and filelist.xml. Rename the files so 
that they are meaningful for your report.
If the files are created directly in the same folder as the .htm file they will be called xx_editdata.mso and 
xx_filelist.xml. Renaming these is a matter of choice.

4.	Edit the renamed filelist.xml and delete the line  starting "<o:MainFile HRef=".
Change editdata.mso and filelist.xml to match your renamed files.

5.	Copy the renamed files to a location on your WebFocus server that can be accessed by the http server
for example \apps\baseapp.

6.	Modify your report that generates the Excel output to reprocess it and incorporate the VBScript.

Here is an example of how to do it.

USE
c:\ibi\apps\ibisamp\car.foc AS CAR
END
FILEDEF CARXHT DISK .\CARXHT.XHT
-RUN
TABLE FILE CAR
SUM DEALER_COST RETAIL_COST
BY COUNTRY
BY MODEL
ON TABLE HOLD AS CARXHT FORMAT EXL2K
END
-RUN

-*  I create a dynamic master using –write but there is no reason why you cannot use a static master
-* located on your APP PATH

FILEDEF CARXHTM DISK .\CARXHT.MAS
FILEDEF CARXHT DISK .\CARXHT.XHT (LRECL 100
-RUN
-*
-* write your dummy master to allow Focus to read the .XHT file as input
-*
-WRITE CARXHTM FILE=CARXHT          ,SUFFIX=FIX
-WRITE CARXHTM SEGNAME=CARXHT       ,SEGTYPE=S0
-WRITE CARXHTM FIELDNAME   =DATALINE       ,E01         ,A100     ,A100     ,$
-* 
-* Allocate a new output file with the extension .XLS NOT .XHT. This is to allow your web browser
-* to automatically open the file in EXCEL. The reason for this is that the display is not performed
-* via WebFocus mime handling
-*
-* Very important the file allocation MUST use (APPEND and must be to a location accessible by the HTTP 
-*server
-* The reason for this is that the browser must be able to locate the file and the edatemp directory will not work.
-* (this does mean having a permanent working directory which will require maintenance so we do a delete 
-*  first)
-*
!IF EXIST C:\ibi\apps\baseapp\NEWXHT.XLS (DEL C:\ibi\apps\baseapp\NEWXHT.XLS /F)
FILEDEF NEWXHT DISK C:\ibi\apps\baseapp\NEWXHT.XLS (APPEND
-RUN
-*
-* Write the required new header information for your output file
-* The <link href entries should be changed to match the location/name that you copied the XML and MSO 
-* files to.
-* The <o:LocationOfComponents entry should mach the directory where you copied the files
-* remember to change localhost to your WebFocus server name
-*
-WRITE NEWXHT <html xmlns:o="urn:schemas-microsoft-com:office:office"
-WRITE NEWXHT xmlns:x="urn:schemas-microsoft-com:office:excel"
-WRITE NEWXHT xmlns="http://www.w3.org/TR/REC-html40">
-WRITE NEWXHT <head>
-WRITE NEWXHT <meta http-equiv=Content-Type content="text/html; charset=us-ascii">
-WRITE NEWXHT <meta name=ProgId content=Excel.Sheet>
-WRITE NEWXHT <meta name=Generator content="Microsoft Excel 10">
-WRITE NEWXHT <link rel=File-List href="http://localhost/approot/baseapp/NEWXHT.xml"/>
-WRITE NEWXHT <link rel=Edit-Time-Data href="http://localhost/approot/baseapp/NEWXHT.mso"/>
-WRITE NEWXHT <link rel=OLE-Object-Data href="http://localhost/approot/baseapp/oledata.mso"/>
-WRITE NEWXHT <!--[if gte mso 9]><xml>
-WRITE NEWXHT  <o:OfficeDocumentSettings>
-WRITE NEWXHT   <o:RelyOnVML/>
-WRITE NEWXHT  </o:OfficeDocumentSettings>
-WRITE NEWXHT </xml><![endif]-->
-*
-* Table the .XHT output that you created using your dummy master and extract only those lines
-* including and after the <style> line.
-* Issue an ON TABLE SAVE to append the output to the new header that you have written.
-*
DEFINE FILE CARXHT
WANTED/A1= IF EDIT(DATALINE,'9999999') EQ '<style>' OR '<STYLE>' THEN 'Y'
            ELSE IF LAST WANTED EQ 'Y' THEN 'Y' ELSE 'N';
END
TABLEF FILE CARXHT
PRINT DATALINE
WHERE WANTED EQ 'Y';
ON TABLE SAVE AS NEWXHT FORMAT ALPHA
END
-RUN
-*
-* Use an HTMLFORM to display your new Macro enabled WebFocus report.
-* You will receive a message saying that some of the files in this webpage aren't in the expected location
-* just click yes
-* This will resolve the macro's from the server and saving as XLS format will make it permanent.
-*
-HTMLFORM BEGIN
<html>
<head>
<title>EXCEL2K with MACRO </title>
</head>
<frameset rows="*">
  <frame name="EXCEL2K" src="http://Loaclhost/approot/baseapp/NEWXHT.XLS" scrolling="auto" >
  <noframes>
  <body>
  <p>This page uses frames, but your browser doesn't support them.</p>
  </body>
  </noframes>
</frameset>
</html>
-HTMLFORM END

For EXL2K PIVOT the process is quite similar but there is slightly more work to do

Steps 1 to 3 are the same when working with Pivot tables however step 4 requires a slight modification

4.	Edit the renamed filelist.xml and delete the line  starting "<o:MainFile HRef=".
Add a new line 
<o:File HRef="x$.XML"/> 
Where x$ is the name of the cache file generated by WebFocus. If you intend to use the GSAR technique to 
allow long filenames for pivot tables then you should use that name.
Change editdata.mso and filelist.xml to match your renamed files as for EXL2K format

The other major difference is in the header information that you need to write. 

The changed portion of the code should become:-

-WRITE NEWXHT <html xmlns:v="urn:schemas-microsoft-com:vml"
-WRITE NEWXHT xmlns:o="urn:schemas-microsoft-com:office:office"
-WRITE NEWXHT xmlns:x="urn:schemas-microsoft-com:office:excel"
-WRITE NEWXHT xmlns="http://www.w3.org/TR/REC-html40">
-WRITE NEWXHT 
-WRITE NEWXHT <head>
-WRITE NEWXHT <meta http-equiv=Content-Type content="text/html; charset=utf-8">
-WRITE NEWXHT <meta name=ProgId content=Excel.Sheet>
-WRITE NEWXHT <meta name=Generator content="Microsoft Excel 11">
-WRITE NEWXHT <link rel=File-List href=" http://localhost/approot/baseapp/NEWXHT.xml">
-WRITE NEWXHT <link rel=Edit-Time-Data href=" http://localhost/approot/baseapp/NEWXHT.mso">
-WRITE NEWXHT <link rel=OLE-Object-Data href=" http://localhost/approot/baseapp/NEWXHT.mso">
-WRITE NEWXHT <!--[if !mso]>
-WRITE NEWXHT <style>
-WRITE NEWXHT v\:* {behavior:url(#default#VML);}
-WRITE NEWXHT o\:* {behavior:url(#default#VML);}
-WRITE NEWXHT x\:* {behavior:url(#default#VML);}
-WRITE NEWXHT .shape {behavior:url(#default#VML);}
-WRITE NEWXHT </style>
-WRITE NEWXHT <![endif]--><!--[if gte mso 9]><xml>
-WRITE NEWXHT  <o:DocumentProperties>
-WRITE NEWXHT   <o:LastAuthor>WebFocus Server</o:LastAuthor>
-WRITE NEWXHT  </o:DocumentProperties>
-WRITE NEWXHT  <o:OfficeDocumentSettings>
-WRITE NEWXHT   <o:RelyOnVML/>
-WRITE NEWXHT   <o:AllowPNG/>
-WRITE NEWXHT   <o:DoNotOrganizeInFolder/>
-WRITE NEWXHT  </o:OfficeDocumentSettings>
-WRITE NEWXHT </xml><![endif]-->

That’s basically it, with the exception of accommodating those long filenames.
The examples shown above are written for web browser delivery. The technique however works equally 
well if you want to serve up the enhanced Excel documents over the network. All you have to do is 
substitute the network path for the URLs.

JG

This message has been edited. Last edited by: <JG>,

Thanks JG.

applause, JG.

Hi John -- We would be very happy to post your article to the Focus on Developers site.

We'll begin by taking the content referenced in this thread...and then we'll contact you offline to discuss edits, etc.

We're always on the look-out for articles from all FP members so keep sending your suggestions.

Thanks everyone!

Maryellen

This message has been edited. Last edited by: <Maryellen>,