Focal Point - Creating Excel Macros

Creating Excel Macros
By John Gray

As we all know, it isn't possible to include macros or VBA scripts into versions of WebFOCUS prior to 7.1. Previous versions can deliver the EXL2K format output, but the output formatting capabilities are limited. To deliver stunning formats, or to do something as mundane as setting a print range the formatting needs to be performed by a macro or template.

This feature is available in WebFOCUS 7.1 and is called templates, but I suspect that it will be quite a while before 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 onward.

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

WebFOCUS does not actually create a .xls worksheet document as its output. What you get is a file in XHTML document format that Excel understands and treats as a .xls. The extended HTML document generated by WebFOCUS can be read by versions of Microsoft Excel from 2000 up, and because we know all about the document's 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 may generate a security warning. The reason for this is that when you save the macro as a .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, for the purposes of Web delivery this is the temp directory used by the browser.

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:

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

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).

As an example of a VB Script, the following sets the print area and then sets up the print options in Excel to fit the output on a single page in landscape print format.

(This is not suitable for pivot tables, as there are special things that need to be done to handle printing them. Also note I'm a Brit, so page size is A4 – you might want to change that.)

Sub Auto_Open()

    Dim x As Long, lastCell As Range

           x = ActiveSheet.UsedRange.Columns.Count

    Set lastCell = Cells.SpecialCells(xlCellTypeLastCell)

    ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), lastCell).Address

    With ActiveSheet.PageSetup

        .LeftHeader = ""

        .CenterHeader = ""

        .RightHeader = ""

        .LeftFooter = ""

        .CenterFooter = ""

        .RightFooter = ""

        .LeftMargin = Application.InchesToPoints(0.75)

        .RightMargin = Application.InchesToPoints(0.75)

        .TopMargin = Application.InchesToPoints(1)

        .BottomMargin = Application.InchesToPoints(1)

        .HeaderMargin = Application.InchesToPoints(0.5)

        .FooterMargin = Application.InchesToPoints(0.5)

        .PrintTitleRows = ""

        .PrintTitleColumns = ""

        .PrintHeadings = False

        .PrintGridlines = False

        .PrintComments = xlPrintNoComments

        .CenterHorizontally = False

        .CenterVertically = False

        .Orientation = xlLandscape

        .Draft = False

        .PaperSize = xlPaperA4

        .FirstPageNumber = xlAutomatic

        .Order = xlDownThenOver

        .BlackAndWhite = False

        .Zoom = False

        .FitToPagesWide = 1

        .FitToPagesTall = 1

        .PrintErrors = xlPrintErrorsDisplayed

    End With

End Sub

Depending on your Excel options settings for Web Options, this will either generate a subfolder 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 two files, editdata.mso and filelist.xml. Rename the files so 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, but if you have multiple macros for different reports, it is required.

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

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

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.

-*

-* Remember, your file and browser settings dictate how a file is opened, 

-* additionally your Web server must pass the correct mime information. 

-* For IIS this is standard but for a Tomcat stand alone you may have to add the

-* mime settings to web.xml

-*

-* 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 needs to be able to locate the file and so the edatemp directory will

-* not work.

-* (this does mean having a permanent working directory which will require 

-* maintenance so we delete the file first if it already exists)

-*

!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 a HTMLFORM to display your new Macro enabled WebFocus report.

-* You may 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 PivotTables, but step 4 requires a slight modification:

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 PivotTables 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]-->

The examples shown above are written for Web browser delivery. The technique works equally well, however, 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.

Using the techniques above you can do anything in WebFOCUS that you can do in Microsoft Excel directly.

7.1 makes it a lot easier, but there's probably more than a few years left in the old dog yet.