Producing Reports, etc. using HTML and Internet Explorer

by David Halls

Introduction

Microsoft Word and Excel are frequently used as report engines for Visual Basic programmes. A web browser is more likely to be on a client's computer, so reports produced in HTML for printing out by the browser have some appeal. This example uses VBScript within an HTML page for production of an award certificate. This is designed to run with Internet Explorer as a browser. The same principles could be applied to run the programme as a Visual Basic executable programme or could be rewritten in Javascript to run on any browser.

Production of the template

First we need a template for our report (or invoice, mailing letter). The example here is a simple award certificate for a sports event. There are four items which are needed to personalise the certificate:-

1. The recipient's name
2. The prize they obtained
3. The event
4. The date of the event

This data could be incorporated using the Document Object Model. However, there is a simple alternative. In this, we put in bookmarks for all the changeable entries, then read each line of the template HTML file, replacing the bookmarks with the variables and saving the HTML to a new file. As the browser ignores carriage returns and white spaces in the code, we can make things easier for ourselves. We can arrange for the bookmarks to be at the beginning of a line. We then need to read each line in turn and check to see whether its first characters contain a bookmark. An example will help. The bookmarks are XX1-XX4

<p>This is to certify</p>
<p>that</p>
<p>
XX1
</p>
<p>has gained</p>
<p>
XX2
</p>
<p>in the event</p>
<p>
XX3
</p>
<p>on</p>
<p>
XX4
</p>

Entering the data

The data could come from a database, but in this example, the data is to be added manually into textboxes in an HTML page.

Four textboxes are needed arranged in a table, with a button to allow the report to be produced.

Each textbox is given a name as ID and the button when clicked, calls a subroutine called produceaward, which is described below.

Producing the Report from the Template

In VBScript, the FileSystemObject is used to read from the template file and write to a new file. After each line is read, bookmarks are checked for and replaced when found. After the file is produced, all objects are closed and the HTML file opened in a new window.

'Define the files used
templatefilename = "C:\html progs\award.htm"
awardfilename = "C:\html progs\finalaward.htm"

sub produceaward
Dim filesys, readstream, writestream, readtext, bookmarkno
'Open the FileSystemObject
Set filesys= CreateObject("Scripting.FileSystemObject")
'Open the template for reading
Set readstream = filesys.OpenTextFile(templatefilename,1)
'Open a final file to hold the compiled file. 
'Allow it to overwrite any existing file. 
Set writestream = filesys.CreateTextFile(awardfilename, true,false)
'Loop though each line. 
'If the first two characters are XX, read the third character
'and replace with the relevant entry from the form. 
do while readstream.AtEndofStream = false
readtext = readstream.ReadLine
if left(readtext,2) = "XX" then
   bookmarkno = mid(readtext,3,1)
   select case(bookmarkno)
   case 1
   		readtext = document.forms(0).awardname.value
   case 2
   		readtext = document.forms(0).awardprize.value
   case 3
   		readtext = document.forms(0).awardevent.value
   case 4
   		readtext = document.forms(0).awarddate.value
end select
end if 
writestream.WriteLine(readtext)
loop
'close all objects
set filesys = nothing
set readstream = nothing
set writestream = nothing
'open the completed file in a new window with a menubar to allow printing
newwindow = window.open(awardfilename,"Certificate","width=650,height=900,menubar=yes")
end sub 

Some problems

Internet Explorer does not like ActiveX Objects in HTML scripts being let loose on your computer, so it will come up with a warning when the button is pressed. You may not wish to lower the security level on your browser, but there is a simple solution. Save the HTML production file with the ending ".hta", which Microsoft has designated for HTML applications designed only to run on a host computer. The programme then runs in a simpler browser window with no annoying warning.

Printing with Internet Explorer under default conditions, produces a heading of page number and a footer of URL and date. You may not wish this to appear on your reports. These can be removed under Page Setup in Internet Explorer.