WebFOCUS Excel Templates

Generating Data/Accessing Data

WebFOCUS Excel Templates

By Anthony Alsford

Problem

We are all encountering problems now that many companies are upgrading their Office application solution to Microsoft Office 2007. The main reason for these problems is that Microsoft have decided to move away from support of macros and formulae etc. in a "single page web archive" or .mht document.

One result of this is that many companies are deciding to retain at least one machine (or VM Ware image) with MS Office 2003 installed such that templates can still be "tweaked" when necessary. This is an unnecessary overhead for many companies and an alternative approach is required.

The good news, as always, is that you can work around this issue by utilising scripting of MS Excel.

How?

Solution

I have formulated a working example against a .mht file from MS Excel 2007 - which I can assure you is quite empty of formulae and macro code, unfortunately.

The basis of the work around comes from the ability to automate almost everything in MS Excel by using a scripting language like VB Script. The downside of this is that you effectively have a post processing step to action before you achieve your end result. I’ll live with that if it will allow me to keep my MS Office upgrade schedule and allow me to plan my WebFOCUS upgrade separately.

Take a basic WebFOCUS procedure that would utilise a template and populate the first three worksheets within a workbook. Note that I keep all my template files in a folder called “Templates” which is not normally with my APP PATH. MyTemplate.mht exists within this folder and was created in MS Excel 2007 with three blank sheets.

SET BYDISPLAY = ON
SET PAGE = NOLEAD
APP PREPENDPATH TEMPLATES
FILEDEF TEMPMHT1 DISK tempmht1.mht
FILEDEF TEMPMHT2 DISK tempmht2.mht
TABLE FILE CAR SUM RCOST DCOST BY COUNTRY BY CAR BY MODEL
ON TABLE HOLD AS TEMPMHT1 FORMAT EXL2K TEMPLATE MYTEMPLATE SHEETNUMBER 1
ON TABLE SET STYLE *
  SIZE=9, $
ENDSTYLE
END
 TABLE FILE CAR SUM RCOST DCOST BY COUNTRY BY CAR BY MODEL
ON TABLE HOLD AS TEMPMHT2 FORMAT EXL2K TEMPLATE TEMPMHT1 SHEETNUMBER 2
ON TABLE SET STYLE *
  SIZE=9, $
ENDSTYLE
END
 TABLE FILE CAR SUM RCOST DCOST BY COUNTRY BY CAR BY MODEL
ON TABLE PCHOLD AS FINALXLS FORMAT EXL2K TEMPLATE TEMPMHT2 SHEETNUMBER 3
ON TABLE SET STYLE *
  SIZE=9, $
ENDSTYLE
END
-RUN

This will produce an XHTML file able to be read by MS Excel and each sheet will have the same report introduced to it. The final PCHOLD will render this report to the end user.

Now take a little step further and HOLD the resultant file in a temporary directory so that it can be subsequently processed by a VB Script module. You could use a pre-created VB Script, but it is an easy process to create it on the fly within WebFOCUS.

Firstly we will FILEDEF two additional files, one to contain the temporary output and one for our VB Script file. So our procedure now looks like this -

SET BYDISPLAY = ON
SET PAGE = NOLEAD
APP PREPENDPATH TEMPLATES
FILEDEF TEMPMHT1 DISK tempmht1.mht
FILEDEF TEMPMHT2 DISK tempmht2.mht
FILEDEF FINALXLS DISK c:\temp\tempxls.xls
FILEDEF PROCFILE DISK c:\ibi\apps\baseapp\procfile.vbs
TABLE FILE CAR SUM RCOST DCOST BY COUNTRY BY CAR BY MODEL
ON TABLE HOLD AS TEMPMHT1 FORMAT EXL2K TEMPLATE MYTEMPLATE SHEETNUMBER 1
ON TABLE SET STYLE *
  SIZE=9, $
ENDSTYLE
END
 TABLE FILE CAR SUM RCOST DCOST BY COUNTRY BY CAR BY MODEL
ON TABLE HOLD AS TEMPMHT2 FORMAT EXL2K TEMPLATE TEMPMHT1 SHEETNUMBER 2
ON TABLE SET STYLE *
  SIZE=9, $
ENDSTYLE
END
 TABLE FILE CAR SUM RCOST DCOST BY COUNTRY BY CAR BY MODEL
ON TABLE HOLD AS FINALXLS FORMAT EXL2K TEMPLATE TEMPMHT2 SHEETNUMBER 3
ON TABLE SET STYLE *
  SIZE=9, $
ENDSTYLE
END
-RUN

Only three changes have been made and I have highlighted them in red to show where they are.

So now we are ready to create our VB Script file “on the fly”. How? Using Dialogue Manager of course!

-WRITE PROCFILE ' VB Script file to post process Excel output from WebFOCUS
-WRITE PROCFILE
-WRITE PROCFILE Dim objFSO, objExcel, objWBook, objSheet2
-WRITE PROCFILE Set objFSO   = CreateObject("Scripting.FileSystemObject")
-WRITE PROCFILE Set objExcel = CreateObject("Excel.Application")
-WRITE PROCFILE ' objExcel.Visible = True
-WRITE PROCFILE
-WRITE PROCFILE process_template
-WRITE PROCFILE
-WRITE PROCFILE sub process_template()
-WRITE PROCFILE   process_workbook
-WRITE PROCFILE   delete_vbs
-WRITE PROCFILE   objExcel.Quit
-WRITE PROCFILE   Set objWBook = Nothing
-WRITE PROCFILE   Set objExcel = Nothing
-* Do not write out the message unless you intend to run this manually
-WRITE PROCFILE '  msgbox "The process has completed", 0, "Process of Excel Template"
-WRITE PROCFILE end sub
-WRITE PROCFILE
-WRITE PROCFILE sub process_workbook()
-WRITE PROCFILE   Set objWBook = objExcel.Workbooks.Open("c:\temp\tempxls.xls")
-* In this section you can process your VBA code as if it were within the Excel file
-WRITE PROCFILE   Set objSheet2 = objWBook.WorkSheets("Sheet2")
-WRITE PROCFILE   objSheet2.Activate
-WRITE PROCFILE   objSheet2.UsedRange.Select
-WRITE PROCFILE   objExcel.Selection.Font.Bold = TRUE
-WRITE PROCFILE   objExcel.Selection.Font.Size = 8
-WRITE PROCFILE   objExcel.Selection.Font.ColorIndex = 3
-WRITE PROCFILE   objExcel.ActiveWorkbook.SaveAs "c:\temp\finalxls.xls", Fileformat=xlNormal
-WRITE PROCFILE   objExcel.ActiveWorkbook.Close
-WRITE PROCFILE end sub
-WRITE PROCFILE
-WRITE PROCFILE sub delete_vbs()
-WRITE PROCFILE '  objFSO.DeleteFile "c:\temp\tempxls.xls"
-WRITE PROCFILE '  objFSO.DeleteFile "c:\ibi\apps\baseapp\procfile.vbs"
-WRITE PROCFILE end sub

If you were to create this procedure and run it, you should have two files produced, The first would be your temporary MS Excel output – tempxls.xls, held in the Temp folder on the C: drive of the WF Reporting Server, and the second would be a file named “procfile.vbs” contained within the baseapp folder of your server's APPROOT.

If you double click on the VB Script file, it will attempt to create an instance of a MS Excel application – just as if you opened MS Excel yourself except that this will be “hidden” from you as it will be run by the “system”.

NOTE: If you run this in your server then you will need to have MS Excel installed on the server.

When the instance of MS Excel has been created, the script will open the file tempxls.xls, activate sheet 2, select the “used range” within this sheet and change the font size and colour of the contents. The file will then be saved as the filename Finalxls.xls in MS Excel internal format. This will actually save it as MS Excel 2003 compatible so it would be possible for you to add formulae to various cells using the VB Script before saving the final output.

That was an easy solution to achieve, and so is adding the VBA code, providing you set the scene correctly.

If I wanted to import some VBA into a module within MS Excel, then there is a method that I can use by right clicking the VBA Project within MS Excel and choosing "Insert/Module" from the context menu that pops up. I can achieve the same thing within VB Script using just two lines of code and a few extra declarations. Of course, my VBA code would have to exist in a file to begin with, but that is not a problem because I could easily develop the VBA within MS Excel and then Export it to a file using standard MS Excel menu options.

For this, I will be using a very simple code set that selects the first column and then cuts and inserts that column to become column 3. I created this code in the VBA editor of MS Excel and then exported it to a file called "MoveColumns.bas".

Attribute VB_Name = "Module1"
Sub move_columns()
  Columns("A:A").Select
  Selection.Cut
  Columns("D:D").Select
  Selection.Insert Shift:=xlToRight
End Sub

I can insert this code into my MS Excel workbook at the same time that I change the font size and colour in my previous example, and the following code will show you where I inserted the additional VB Script to achieve this. Note that the WebFOCUS code remains unchanged and I have therefore not reproduced at this point -

-WRITE PROCFILE ' VB Script file to post process Excel output from WebFOCUS
-WRITE PROCFILE
-WRITE PROCFILE Dim objFSO, objExcel, objWBook, objSheet2, objVBComp, CM
-WRITE PROCFILE Set objFSO    = CreateObject("Scripting.FileSystemObject")
-WRITE PROCFILE Set objExcel  = CreateObject("Excel.Application")
-WRITE PROCFILE ' objExcel.Visible = True
-WRITE PROCFILE
-WRITE PROCFILE process_template
-WRITE PROCFILE
-WRITE PROCFILE sub process_template()
-WRITE PROCFILE   process_workbook
-WRITE PROCFILE   delete_vbs
-WRITE PROCFILE   objExcel.Quit
-WRITE PROCFILE   Set objWBook = Nothing
-WRITE PROCFILE   Set objExcel = Nothing
-* Do not write out the message unless you intend to run this manually
-WRITE PROCFILE '  msgbox "The process has completed", 0, "Process of Excel Template"
-WRITE PROCFILE end sub
-WRITE PROCFILE
-WRITE PROCFILE sub process_workbook()
-WRITE PROCFILE   Set objWBook  = objExcel.Workbooks.Open("c:\temp\tempxls.xls")
-* These two lines actually import a VBA code file and, because the output extension is .xls
-* the VBA will remain in the the resultant file when saved.
-WRITE PROCFILE   Set objVBComp = objWBook.VBProject.VBComponents
-WRITE PROCFILE   Set CM = objVBComp.Import("c:\ibi\apps\ExcelTemplates\MoveColumns.bas")
-* In this section you can process your VBA code as if it were within the Excel file
-WRITE PROCFILE   Set objSheet2 = objWBook.WorkSheets("Sheet2")
-WRITE PROCFILE   objSheet2.Activate
-WRITE PROCFILE   objSheet2.UsedRange.Select
-WRITE PROCFILE   objExcel.Selection.Font.Bold = TRUE
-WRITE PROCFILE   objExcel.Selection.Font.Size = 8
-WRITE PROCFILE   objExcel.Selection.Font.ColorIndex = 3
-* Now run the imported VBA code to move column A before the fourth column
-WRITE PROCFILE   objWBook.Application.Run "move_columns"
-WRITE PROCFILE   objExcel.ActiveWorkbook.SaveAs "c:\temp\finalxls.xls", Fileformat=xlNormal
-WRITE PROCFILE   objExcel.ActiveWorkbook.Close
-WRITE PROCFILE end sub
-WRITE PROCFILE
-WRITE PROCFILE sub delete_vbs()
-WRITE PROCFILE '  objFSO.DeleteFile "c:\temp\tempxls.xls"
-WRITE PROCFILE '  objFSO.DeleteFile "c:\ibi\apps\baseapp\procfile.vbs"
-WRITE PROCFILE end sub

One additional item that you will need to enable is the trusted state of VBA Project code by accessing Macro Security and checking the box to “Trust access to the VBA project object model”. To find this option in MS Excel 2007, you need to click the MS Office icon on the ribbon toolbar and click “Excel Options” at the bottom of the context menu. Now click the “Trust Center” menu option followed by the “Trust Center Settings” button.

This is not an all encompassing solution to every situation that developers may come across, but it provides the basic leverage to process and create Data rich MS Excel files whilst moving forward with your Enterprises need to upgrade MS Office.

----------------------------------------------------------------------------------------------

Anthony has been developing in WebFOCUS (formerly FOCUS) since 1986 and has worked in both the technical support and professional services departments for the London office of Information Builders. As Anthony has previously been working as a contractor in the UK market since 1994, he now works for Information Builders in the United Kingdom in the Professional Services Department.

Primary links

Search

WebFOCUS Excel Templates

WebFOCUS Excel Templates

Connect with us