Converting Reports to HTML Pages

In this chapter, Alexander will begin developing the first of the WSH VBScripts for the Order/Inventory reporting Web project. The purpose of this VBScript is to create an HTML page based on the contents of the current day's consolidated summary report. He will accomplish this task by opening the text-based version of the report file, reading it, and using its contents to create an output file that includes embedded HTML tags. The output file will then be saved as an HTML file.

Preparing to Create the HTML Conversion Script

Creating the HTML version of the consolidated summary report will not use any new VBScript or WSH functions or methods. It will use the same FileSystem Object I/O methods that you have seen in other chapters. The main difference is that the output file created by this chapter will include embedded HTML tags required to create a Web page. Therefore, you will use the FileSystemObject object's WriteLine() method extensively, as demonstrated below.

Dim FsoObj
Set FsoObj = CreateObject("Scripting.FileSystemObject")
Set strReportFile = FsoObj.OpenTextFile("D:TempTest.HTML", 2, "True")
strReportFile.WriteLine("

") strReportFile.WriteLine("") strReportFile.WriteLine("Test HTML Page") strReportFile.WriteLine("") strReportFile.WriteLine("

") strReportFile.WriteLine("

") strReportFile.WriteLine("") strReportFile.Close()

When executed, this example creates an HTML file called Test.html that has the following content:

Test HTML Page

In order to create the HTML version of the consolidated summary report, the VBScript will have to make substantial use of parsing functions in order to identify and extract individual report elements. In previous chapters, this was accomplished using VBScript functions. However, as you will see, the more complicated parsing requirements required by this script will also require the use of the RegExp object and its properties.

Note

The RegExp object and its properties were introduced and demonstrated in Chapter 7, "VBScript Objects."

The last scripting element to be used in this chapter's script is the VBScript Ubound() object. This function retrieves the upper bound element for the specified array and will be used in conjunction with the RegExp object to process a portion of the current day's consolidated summary report.

Note

The VBScript Ubound() function was previously introduced in Chapter 5, "Arrays."

Creating the HTML Conversion Script

Alexander plans to provide all of the same basic functionality features that Molly provided in her VBScripts in her last project, including support for the Windows registry, a debugging mode, and event logging. The following sections detail the components of the HTML conversion script and provide an overview of their design and purpose.

The Initialization Section

The script's Initialization Section, shown below, begins by requiring strict variable interpretation. Next it defines all of the variables used globally by the script. It also defines variables that will be used to represent the WshShell and FileSystemObject objects, as well as an array called astrErrors that will be used to store the contents of each line of the report. Next a collection of constants is defined. Finally, a variable called intReturnCode is set equal to zero. This variable represents the return code value that the script will pass back to the script that executed it (the scheduling script). Setting the variable equal to zero in the Initialization Section ensures that unless the variable's value is explicitly changed during the execution of the script, a zero return code will be returned.

Option Explicit

Dim intReturnCode, strOutputFile, strReportFile, strSourceLine
Dim strSourceFile, strConsolTxtRpt, i, strFileNameString
Dim strEventLog, strDebug
Dim WshShl, FsoObj
Dim astrErrors

Set WshShl = WScript.CreateObject("WScript.Shell")
Set FsoObj = CreateObject("Scripting.FileSystemObject")

Const cForReading = 1
Const cForWriting = 2
Const cForAppending = 8
Const cTitleBarMsg = "HTML Report Conversion Script"

intReturnCode = 0

The Main Processing Section

Unlike the other scripts presented in this book, the Main Processing Section of this VBScript is very involved. It is made up of a combination of procedure calls, loops, and conditional tests that control the processing of the text version of the summary report and the creation of the new HTML version.

The Main Processing Section begins by calling the GetRegistrySettings() subroutine, which retrieves the script's configuration settings from the Windows registry, followed by the AssembleFileNames() subroutine, which builds two strings representing the names of the current day's report.

GetRegistrySettings()
AssembleFileNames()

Next, the current consolidated summary report file is opened for reading and an HTML file is created, as shown below.

Set strSourceFile = FsoObj.OpenTextFile(strConsolTxtRpt, cForReading)
Set strReportFile = FsoObj.OpenTextFile(strOutputFile, cForWriting , "True")

The next set of statements writes a message to the Windows application event log if event logging has been enabled for the script.

If strEventLog = "Enabled" Then
 WriteToEventLog("HTML Report Conversion Script now executing.")
End If

Likewise, the next several statements execute only if the script is being manually run in debug mode.

If strDebug = "Enabled" Then
 MsgBox "Beginning report development.", , cTitleBarMsg
End If

The next three statements set up a For…Next loop that skips through the headings of the daily consolidated summary report, as represented by strSourceFile.

For i = 0 to 8
 strSourceFile.SkipLine
Next

The next several statements call subroutines that write collections of HTML tags to the file.

WriteHeader()
BeginTableDefinition()
WriteTableHeader("Errors:")
WriteErrorsColHeadings()

Next, a Do…Until loop is used, as shown below, to iterate through the text version of the report until the first blank line is found (the end of the Errors: section of the report is reached). Upon each iteration of the loop, a line from the report is read and assigned to a variable called strSourceLine. The length of the string assigned to strSourceLine is then checked to see if it's zero (blank). If it is, the loop terminates; otherwise, contents of the string are loaded into an array using the Split() function and the WriteErrorsData() subroutine is called. This subroutine uses the contents of the array to write a line of data to the HTML page.

Do Until strSourceFile.AtEndOfStream
 strSourceLine = strSourceFile.ReadLine()
 If Len(strSourceLine) = 0 Then
 Exit Do
 Else
 astrErrors = Split(strSourceLine, " ", 5)
 WriteErrorsData()
 End If
Loop

Note

Note the use of the Split() function in the previous set of statements. It specifies a third parameter with a value of 5. This parameter limits the size of the array created by the Split() function to five elements. If you compare this statement to the output displayed on the HTML page created by this script, you will find that this matches up against the five columns of data displayed in the report, with the fifth column displaying a string representing all the remaining data from a line of the report after the fifth word (that is, the description).

Next, the EndTableDefinition() subroutine is called. This subroutine writes an HTML tag that marks the end of the Errors: table ().

EndTableDefinition()

The rest of the statements in the Main Processing Section write the remaining sections of the HTML file by repeating the same basic series of steps that you have seen thus far, making adjustments as necessary to specify appropriate end-of-section markers and to print the proper report headings.

BeginTableDefinition()

WriteTableHeader("Sales Summary:")
WriteTableSubHeader("Government:")
WriteSalesAndReturnsColHeadings()

For i = 0 to 7
 strSourceFile.SkipLine
Next

Do Until strSourceFile.AtEndOfStream
 strSourceLine = strSourceFile.ReadLine()
 If Len(strSourceLine) <> 0 Then
 If Instr(1, strSourceLine, "Other Customers:") Then
 Exit Do
 End If
 astrErrors = Split(strSourceLine, " ", 3)
 WriteSalesAndReturnsData()
 End If
Loop

WriteTableSubHeader("Other Customers:")
WriteSalesAndReturnsColHeadings()

For i = 0 to 2
 strSourceFile.SkipLine
Next

Do Until strSourceFile.AtEndOfStream
 strSourceLine = strSourceFile.ReadLine()
 If Len(strSourceLine) <> 0 Then
 If Instr(1, strSourceLine, "----------") Then
 Exit Do
 End If
 astrErrors = Split(strSourceLine, " ", 3)
 WriteSalesAndReturnsData()
 End If
Loop

EndTableDefinition()

BeginTableDefinition()

WriteTableHeader("Returns Summary:")
WriteTableSubHeader("Government:")
WriteSalesAndReturnsColHeadings()

For i = 0 to 6
 strSourceFile.SkipLine
Next

Do Until strSourceFile.AtEndOfStream
 strSourceLine = strSourceFile.ReadLine()
 If Len(strSourceLine) <> 0 Then
 If Instr(1, strSourceLine, "Other Customers:") Then
 Exit Do
 End If
 astrErrors = Split(strSourceLine, " ", 3)
 WriteSalesAndReturnsData()
 End If
Loop

WriteTableSubHeader("Other Customers:")
WriteSalesAndReturnsColHeadings()

For i = 0 to 2
 strSourceFile.SkipLine
Next

Do Until strSourceFile.AtEndOfStream
 strSourceLine = strSourceFile.ReadLine()
 If Len(strSourceLine) <> 0 Then
 If Instr(1, strSourceLine, "----------") Then
 Exit Do
 End If
 astrErrors = Split(strSourceLine, " ", 3)
 WriteSalesAndReturnsData()
 End If
Loop

EndTableDefinition()

BeginTableDefinition()

WriteTableHeader("Daily Production Summary:")
WriteProductionColHeadings()

For i = 0 to 4
 strSourceFile.SkipLine
Next

The last section of the daily consolidated summary report to be processed by the script is the Daily Production Summary section. Like before, a line of data from each row within this section is read into strSourceLine. However, rather than using the Split() function to parse out the contents of the report line, the ParseProductionData() subroutine is executed. The reason for this change is that the descriptive information inside this last section of the report is located in the middle of each row and does not have a predictable length. Therefore, it cannot simply be split into an array and be written out to the HTML page from there. More complicated parsing logic is required. Alexander developed the ParseProductionData() subroutine to parse out the data located in each row of this section of the report using the RegExp object and its methods and properties.

Do Until strSourceFile.AtEndOfStream
 strSourceLine = strSourceFile.ReadLine()
 ParseProductionData(strSourceLine)
 WriteProductionData()
Loop

strSourceFile.Close()

WriteFooter()

If strDebug = "Enabled" Then
 MsgBox "Report development completed.", , cTitleBarMsg
End If

If strEventLog = "Enabled" Then
 WriteToEventLog ("HTML Report Conversion Script finished executing.")
End If

The last statement in the Main Processing Section calls the TerminateScript() subroutine and passes it the value stored in intReturnCode. This function uses the WScript object's Quit() method to terminate the script's execution and to pass the calling script a return code indicating whether this script experienced an error.

TerminateScript(intReturnCode)

The GetRegistrySettings() Subroutine

As you have seen in previous scripts, the GetRegistrySettings() subroutine, shown below, is responsible for retrieving the script's configuration settings from values stored in the Windows registry. In the event that an error occurs in retrieving any of the script's configuration settings, a message is posted to the Windows application event log and a value of 4 (representing a return code) is passed to the TerminateScript() subroutine.

Sub GetRegistrySettings()

 On Error Resume Next

 strEventLog = _
 WshShl.RegRead("HKLMSoftwareIntuitVBScriptsWebRptingEventLogging")
 If Err <> 0 Then
 If strEventLog = "Enabled" Then
 WriteToEventLog ("HTML Report Conversion Script - Using default " & _
 "for strEventLog. RC = 4")
 TerminateScript(4)
 End If
 End If

 strDebug = WshShl.RegRead("HKLMSoftwareIntuitVBScriptsWebRptingDebug")
 If Err <> 0 Then
 If strEventLog = "Enabled" Then
 WriteToEventLog ("HTML Report Conversion Script - Using default " & _
 "for strDebug. RC = 4")
 TerminateScript(4)
 End If
 End If

 strOutputFile = _
 WshShl.RegRead("HKLMSoftwareIntuitVBScriptsWebRptingHTMLFolder")
 If Err <> 0 Then
 If strEventLog = "Enabled" Then
 WriteToEventLog ("HTML Report Conversion Script - Using default " & _
 "for strOutputFile. RC = 4")
 TerminateScript(4)
 End If
 End If

 strConsolTxtRpt = _
 WshShl.RegRead("HKLMSoftwareIntuitVBScriptsWebRptingConSolRptLoc")
 If Err <> 0 Then
 If strEventLog = "Enabled" Then
 WriteToEventLog ("HTML Report Conversion Script - Using default " & _
 "for strConsolTxtRpt. RC = 4")
 TerminateScript(4)
 End If
 End If

 If strDebug = "Enabled" Then
 MsgBox "Registry settings initialized: " & vbCrLf & vbCrLf & _
 "strEventLog" & vbTab & "=" & vbTab & strEventLog & vbCrLf & _
 "strDebug" & vbTab & vbTab & "=" & vbTab & strDebug & vbCrLf & _
 "strOutputFile" & vbTab & "=" & vbTab & strOutputFile & vbCrLf & _
 "strConsolTxtRpt" & vbTab & "=" & vbTab & strConsolTxtRpt & _
 vbCrLf, ,cTitleBarMsg
 End If

End Sub

The AssembleFileNames() Subroutine

The AssembleFileNames() subroutine, shown below, is responsible for determining the name of the current day's consolidated summary report, as well as for naming the new HTML version of the report.

Sub AssembleFileNames()

 strFileNameString = Replace(Date(), "/", "-")

 strConsolTxtRpt = strConsolTxtRpt & "" & strFileNameString & _
 "_ConsolSumRpt.txt"

 If strDebug = "Enabled" Then
 MsgBox "strConsolTxtRpt = " & strConsolTxtRpt, , cTitleBarMsg
 End If

 strOutputFile = strOutputFile & "" & strFileNameString & _
 "_ConsolSumRpt.html"

 If strDebug = "Enabled" Then
 MsgBox "strOutputFile = " & strOutputFile, , cTitleBarMsg
 End If

End Sub

The WriteHeader() Subroutine

The WriteHeader() subroutine, shown below, is responsible for writing a collection of HTML tags at the beginning of the HTML page. These HTML tags define basic page elements and specify the font type and size, as well as the manner in which the border of each table in the script is to be formatted.

Sub WriteHeader()
 strReportFile.WriteLine("

") strReportFile.WriteLine("") strReportFile.WriteLine("HTML Conversion Script") strReportFile.WriteLine("

Errors:
Date	Time	Svr	Code	Description
03/15/03	12:15:44	Sr1	001	Unable to access card reader on device wkstn442
03/15/03	14:00:14	Sr1	001	No inventory for part # 58694 - unable to fill order 39312
03/15/03	16:16:46	Sr1	003	Unable to print summary rpt on master printer (no paper)
03/15/03	12:15:44	Sr2	001	Unable to access card reader on device wkstn442
03/15/03	14:00:14	Sr2	001	No inventory for part # 58694 - unable to fill order 39312
03/15/03	16:16:46	Sr2	003	Unable to print summary rpt on master printer (no paper)

Sales Summary:
Government:
Part #	Qty	Description
58694	19	Cordless temp reader
45643	3	200hp magnetic pump
17443	15	20 lb box of pump clips
10344	35	48 ounce solvent bottle
19365	2	3 speed electric drill
Other Customers:
Part #	Qty	Description
58694	19	Cordless temp reader
45643	3	200hp magnetic pump
17443	15	20 lb box of pump clips
10344	35	48 ounce solvent bottle
19365	2	3 speed electric drill

Returns Summary:
Government:
Part #	Qty	Description
58694	2	Cordless temp reader
17443	7	20 lb box of pump clips
10344	4	48 ounce solvent bottle
45643	1	200hp magnetic pump
19365	1	3 speed electric drill
Other Customers:
Part #	Qty	Description
58694	2	Cordless temp reader
17443	7	20 lb box of pump clips
10344	4	48 ounce solvent bottle
45643	1	200hp magnetic pump
19365	1	3 speed electric drill

Figure 30.1 shows how the daily consolidated summary report will look when viewed as an HTML page displayed on the Order/Inventory Reporting Web site.

Figure 30.1: Viewing the HTML version of the consolidated summary report

 

Summary

In this chapter, you observed as Alexander created a text-to-HTML report conversion script. In creating this script, you learned how to embed HTML tags within VBScript-generated output files in order to automate the presentation of order/inventory data on the Order/Inventory Web site. This chapter also presented you with the opportunity to work with the RegExp object and its properties and methods.

 

Building the Report Archive Page

Daily Production Summary:
Part #	Qty	Description	In Stock
58694	20	Cordless temp reader	50
45643	4	200hp magnetic pump	20
19365	10	3 speed electric drill	20
17443	40	20 lb box of pump clips	200
10344	200	48 ounce solvent bottle	500
99887	1	48 ounce joint compound	12
33443	3	5 speed hydro drill	5
12211	3	3 speed water pump	5