Processing and Consolidating Report Data

In this chapter, you will learn how to develop a VBScript that reads and processes both of the summary report files collected from the SERV0001 and SERV0002 Windows 2000 servers. You will then learn how to create the consolidated summary report in either of two formats, text and Microsoft Word. Finally, you will learn how to develop a process that is capable of notifying selected operations staff members when the consolidated report is available.

Parsing String Contents

The script developed in this chapter will make heavy use of a number of VBScript functions that relate to parsing and manipulating the contents of strings. These functions will be used when reading and processing the two summary reports in order to identify and manipulate the data that is processed. You'll need to use these functions again later in the script when writing the consolidated summary report.

Indexing String Contents

One of the VBScript functions that will be used to develop this chapter's script is the Split() function. This function takes a string argument and builds a single dimension array made up of substrings extracted from the original string. This function will be used to process a registry value whose contents represent a list of usernames to whom network messages are to be sent. By splitting up this list of names into an array, you can then use a For…Next loop to iteratively process each username stored in the array. The Split() function has the following syntax:

Split(Expression [, Delimiter [, Count [, Compare]]])

Expression represents the string to be processed. Delimiter specifies an optional character that identifies substring boundaries. If omitted, the space character is assumed. Count is an optional parameter that can be used to limit the number of substrings retrieved, and Compare is an optional parameter that specifies the type of comparison to perform (specify a value of 0 to perform a binary comparison and a value of 1 to perform a text comparison).

For example, the following statements demonstrate how to use the Split() function. First, a string that stores a list of names, each separated by a space, is defined. Then the Split() function is used to create a single dimension array called astrList that stores each of these three names as array elements.

strList = "Mike Nick Mark"
astrList = Split(strList)

Removing Leading and Trailing Spaces

Another VBScript function that will be used in the script developed in this chapter is the Trim() function. This function retrieves a copy of a string without leading or trailing spaces and has the following syntax:

Trim(string)

String specifies the string to be processed by the function. The following example shows a statement that builds a string padded with a number of blank spaces. The statement is followed by a MsgBox() statement that uses the Trim() function to remove the extraneous blank spaces before it displays the contents of the string.

strList = " Mike Nick Mark "
MsgBox Trim(strList)

Converting a String to an Integer

Another VBScript function that will be useful in developing this chapter's VBScript is the CInt() function. This function retrieves an expression converted to an integer subtype. The syntax for this function is shown below.

CInt(Expression)

Expression represents a string expression. This function is useful when extracting numeric values from text strings, as demonstrated below.

strList = "There are 10 units on hand"
intCount = Cint(Mid(strList, 11,2))

In this example, a string is defined and then parsed using the Mid() function to extract the number of units on hand. While VBScript usually does a good job of converting values from one variant subtype to another, such as when you specify a mathematical operation, there are also times when it does not make the subtype adjustment correctly. Using the Cint() function, as shown above, you can explicitly convert a value to an integer subtype in order to ensure its correct interpretation.

Determining a String s Length

Another VBScript function that will be useful in developing this chapter's VBScript is the Len() function. This function retrieves the number of characters in a string or variable. The syntax for this function is listed below.

Len(string | variable)

String represents a string whose length is to be calculated. In this chapter's VBScript, the Len() function will be used repeatedly to determine whether or not a blank line has been encountered within a report file (that is, a zero length string). For example, the following If statement demonstrates how to determine whether a variable has been assigned any data. If it has not been assigned data, its length will be equal to zero. In this example, an action is taken only when there is some data to process.

If Len(strText) > 0 Then
 ...
End If

Other VBScript Functions

This chapter will take advantage of a number of other VBScript functions when processing the summary reports and creating the consolidated summary report. These functions, listed below, have already been reviewed in earlier chapters.

  • Mid(). Retrieves a subset of characters from a string
  • Instr(). Retrieves the character position of the first occurrence of one string inside another
  • Left(). Retrieves a subset of characters from the left side of a string
  • Right(). Retrieves a subset of characters from the right side of a string


Working with the Word Object Model

This chapter's script will read two summary reports collected from the SERV0001 and SERV0002 Windows 2000 servers at Intuit. It will then process and combine the data found in these two reports to create a new consolidated summary report. This report will be available in two different formats, the standard text format presented in earlier examples and an optional Microsoft Word format. The Word version of the report may make it more convenient for many people at Intuit to view the report, which will be formatted using different fonts and selective character highlighting to make it more visually appealing than its plain text counterpart.

In order to write the Word version of the consolidated report, you will need to learn how to reference and work with the Word object model. At the top of the Word object model is the Application object. When Word is started, an instance of the Application object is automatically created. You can use properties and methods belonging to this object to access lower-level objects and collections in the Word object model. You can then use the properties and methods provided by these objects and collections to automate the creation of reports using Word. The following statement demonstrates how to instantiate Word from within a VBScript:

Set objMsWord = WScript.CreateObject("Word.Application")

This statement assigns a reference to the Word Application object in the form of a variable named objMsWord. In order to create the Word version of the consolidated summary report, you will need to learn how to work with the following Word objects:

  • Documents. A collection representing all the currently opened instances of Word documents. This collection provides properties and methods required to create, open, save, and close files. The Documents collection is accessed using the Application object's Documents property.
  • Document. An individual instance of a Word document. The Document object provides properties and methods required to create, open, save, and close files.
  • Selection. Represents an instance of the currently open Windows pane. The Selection object is accessed using the Application object's Selection property. The Selection object is used when performing an action on a Word document, such as typing in text.
  • Font. Provides access to properties that can be used to format the appearance of text within documents. The Font object is accessed using the Selection object's Font property.

Another useful Application object property is ActiveDocument, which retrieves a reference to the currently active Word document. The following example demonstrates how to use these objects and their properties and methods to create, write to, and save a Word file. The documentation for each statement used within the script is provided by comments embedded within the script itself.

'Define a variable to be used to store a reference to the Application object
Dim objWordDoc
'Instantiate Word and define the Application object reference
Set objWordDoc = WScript.CreateObject("Word.Application")

'Use the Documents collection's Add method to open a new empty Word document
objWordDoc.Documents.Add()

'Use the Font object's Name, Size and Bold properties to format text output
objWordDoc.Selection.Font.Name = "Arial"
objWordDoc.Selection.Font.Size = 12
objWordDoc.Selection.Font.Bold = True

'Use the Selection object's Typetext() method to write a line of text
objWordDoc.Selection.Typetext("Report Header")

'Use the Selection object's TypeParagraph() method to insert two line feeds
objWordDoc.Selection.TypeParagraph
objWordDoc.Selection.TypeParagraph

'Use the Font object's Size and Bold property to format text output
objWordDoc.Selection.Font.Size = 10
objWordDoc.Selection.Font.Bold = False

'Use the Selection object's Typetext() and TypeParagraph() methods to write
'additional text
objWordDoc.Selection.Typetext("Line 1 of the report.")
objWordDoc.Selection.TypeParagraph
objWordDoc.Selection.Typetext("Line 2 of the report.")

'Use the Applications object's ActiveDocument property to reference the current
'document and then use the Document object's SaveAs() method to save the Word

'file
objWordDoc.ActiveDocument.SaveAs("c:TempTextFile.doc")

'Use the document object's Close() method to close the Word document
objWordDoc.ActiveDocument.Close()

'Terminate the currently active instance of Word
objWordDoc.Quit()
  Note

Because of the size and complexity of the Word object model, there is not enough room in this book to cover it in any greater depth. To learn more about Word's object model, check out http://msdn.Microsoft.com/office.


Developing a Network Messaging Procedure

One of the goals that Molly has for this script is to equip it with the ability to notify selected operations staff, in the form of a pop-up dialog box message, when the consolidated summary report is available. She thought about trying to display a pop-up message using the MsgBox() function on the Windows 2000 Professional workstation where the script executes. She discovered that this wouldn't work, because the script that creates the consolidation report runs in the background using the WScript.exe execution host. Therefore, even if she tries to use the MsgBox() function to display an interactive pop-up dialog box, the pop-up dialog box would not be displayed. Instead, the script would stop processing while it waited for the user to respond to the pop-up dialog box, which would never come, since the pop-up dialog box was not displayed.

After doing a little research, Molly came across the Windows Net Send command. This command provides the ability to send a text message over a network to a specified user or computer. The syntax of the Net Send command is shown below.

net send (name | * | /domain[:name] | /users) message

Name represents the name of a user or computer to whom the message is to be sent. In order for the receiving user or computer to receive and display the popup message, the Windows messenger service must be running on his computer. In addition, the user must be logged on to his computer at the time that the message is sent. The asterisk (*) character can be used to send the message to all users within the domain or workgroup to which the sending computer is a member. The /domain:name parameter can be used to send the message to all names defined in the Windows domain. The /users parameter provides the ability to send the message to all users with an active network connection to the sending computer. Message represents the message text that is to be displayed in the popup dialog box.

In order to use the Net Send command from within a script, you need to use the WshShell object's Run() method, as demonstrated below.

Set WshShl = WScript.CreateObject("WScript.Shell")
strUser = "Jford"
strMsgTest = "This is a test!"
WshShl.Run "Net Send " & strUser & " " & strMsgTest

When executed, this example displays the pop-up dialog box shown in Figure 24.1 on the computer where the user whose username is Jford is currently logged on.

click to expand
Figure 24.1: Examining the contents of a network message created using the Net Send command


Creating the Consolidation Report Script

The consolidated summary report creation script performs a number of different tasks. It reads and stores the contents of both summary reports into two separate arrays. Then it spins through both arrays, parsing out data to be used to create the consolidated summary report. This process involves numerous VBScript string-related functions. In addition to creating the standard text report, the script has the ability to create a Word version. Other script activities include retrieving configuration settings from the Windows registry, supporting a debug mode, logging application event messages, and sending network notification messages.

The Initialization Section

The script's Initialization Section, shown below, defines variables used globally throughout the script. In addition, it defines three dynamic arrays, which will be used to store the content of reports while they are being processed. A collection of constants and instances of the WshShell and FileSystemObject objects are also defined here.

Option Explicit

Dim strEventLog, strDebug, strSvrList, strArchive, strConsolFolder
Dim strRpt1, strRpt2, strConSolRptName, strRptFormat, strNetworkNotification

ReDim astrServ0001Array(0)
ReDim astrServ0002Array(0)
ReDim astrProductionArray(0)

Dim FsoObj, WshShl

Const cTitleBarMsg = "Consolidated Summary Report Creator"
Const cForReading = 1
Const cForWriting = 2
Const cForAppending = 8

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

The Main Processing Section

The script's Main Processing Section, shown below, consists of a series of procedure calls. The SetDefaultSettings() and GetRegistrySettings() subroutines are called to set up the script's configuration settings, as has been demonstrated in previous scripts. Messages are written to the Windows application event log by the WriteToEventLog() subroutine if event logging is enabled when the script starts executing. The IdentifyRptsToProcess() subroutine creates a variable that specifies the name of the current day's summary reports. The ReadSummaryReport() subroutine is then called twice and passed the name of a Windows 2000 server. This subroutine reads and then stores the contents of each summary report in an array.

The CreateConsolidatedTextReport() subroutine is then called in order to write the text version of the consolidated summary report. If appropriate, the CreateConsolidatedWordReport() subroutine is then executed in order to create a Word version of the report. If Network Notification is enabled, the NotifyOperationsStaff() subroutine is executed next. Finally, an optional message is written if the event logging is enabled and the script's execution is terminated by calling the TerminateScript() subroutine.

SetDefaultSettings()
GetRegistrySettings()

If strEventLog = "Enabled" Then
 WriteToEventLog("Consolidated Summary Report Creator executing.")
End If

IdentifyRptsToProcess()

ReadSummaryReport(Left(strSvrList, 8))
ReadSummaryReport(Right(strSvrList, 8))

CreateConsolidatedTextReport()

If strRptFormat = "Word" Then
 CreatConsolidatedWordReport()
End If

If strNetworkNotification = "Enabled" Then
 NotifyOperationsStaff()
End If

If strEventLog = "Enabled" Then
 WriteToEventLog("Consolidated Summary Report Creator finished.")
End If

TerminateScript()

The SetDefaultSettings() Subroutine

As demonstrated in previous scripts, the SetDefaultSettings() subroutine, shown below, establishes default configuration settings for the script.

Sub SetDefaultSettings()

 strEventLog = "Enabled"
 strDebug = "Disabled"
 strSvrList = "SERV0001 SERV0002"
 strArchive = "d:Order_InventroryLogFiles"
 strRptFormat = "Text"
 strNetworkNotification = "Enabled"
 strConsolFolder = "d:Order_InventoryConsolidatedRpts"

 If strDebug = "Enabled" Then
 MsgBox "Registry settings retrieved: " & vbCrLf & vbCrLf & _
 "strEventLog" & vbTab & "=" & vbTab & strEventLog & vbCrLf & _
 "strDebug" & vbTab & vbTab & "=" & vbTab & strDebug & vbCrLf & _
 "strSvrList" & vbTab & vbTab & "=" & vbTab & strSvrList & vbCrLf & _
 "strArchive" & vbTab & "=" & vbTab & strArchive & vbCrLf & _
 "strRptFormat" & vbTab & "=" & vbTab & strRptFormat & vbCrLf & _
 "strNetworkNotification" & vbTab & "=" & vbTab & strNetworkNotification & _
 vbCrLf & _
 "strConsolFolder" & vbTab & "=" & vbTab & strConsolFolder, , cTitleBarMsg
 End If

End Sub

The GetRegistrySettings() Subroutine

The GetRegistrySettings() subroutine, shown below, retrieves configuration settings from the Windows registry and logs messages in the Windows application event log if errors occur.

Sub GetRegistrySettings()
 On Error Resume Next

 strEventLog = _
 WshShl.RegRead("HKLMSoftwareIntuitVBScriptsMstSumRptsEventLogging")
 If Err <> 0 Then
 If strEventLog = "Enabled" Then
 WriteToEventLog ("Summary Report Collection script - Using default " & _
 "for strEventLog.")
 Err.Number = 0
 End If
 End If

 strDebug = WshShl.RegRead("HKLMSoftwareIntuitVBScriptsMstSumRptsDebugMode")
 If Err <> 0 Then
 If strEventLog = "Enabled" Then
 WriteToEventLog ("Summary Report Collection script - Using " & _
 "default for strDebug.")
 Err.Number = 0
 End If
 End If

 strSvrList = _
 WshShl.RegRead("HKLMSoftwareIntuitVBScriptsMstSumRptsWin2000Svrs")
 If Err <> 0 Then
 If strEventLog = "Enabled" Then
 WriteToEventLog ("Summary Report Collection script - Using " & _
 "default for strSvrList.")
 Err.Number = 0
 End If
 End If

 strArchive = _
 WshShl.RegRead("HKLMSoftwareIntuitVBScriptsMstSumRptsRptArchive")
 If Err <> 0 Then
 If strEventLog = "Enabled" Then
 WriteToEventLog ("Summary Report Collection script - Using " & _
 "default for strArchive.")

 Err.Number = 0
 End If
 End If

 strRptFormat = _
 WshShl.RegRead("HKLMSoftwareIntuitVBScriptsMstSumRptsRptFormat")
 If Err <> 0 Then
 If strEventLog = "Enabled" Then
 WriteToEventLog ("Summary Report Collection script - Using " & _
 "default for strRptFormat.")
 Err.Number = 0
 End If
 End If

 strNetworkNotification =
WshShl.RegRead("HKLMSoftwareIntuitVBScriptsMstSumRptsNetworkNotification")
 If Err <> 0 Then
 If strEventLog = "Enabled" Then
 WriteToEventLog ("Summary Report Collection script - Using default for
strNetworkNotification.")
 Err.Number = 0
 End If
 End If

 strConsolFolder = _
 WshShl.RegRead("HKLMSoftwareIntuitVBScriptsMstSumRptsConsolFolder")
 If Err <> 0 Then
 If strEventLog = "Enabled" Then
 WriteToEventLog ("Summary Report Collection script - Using " & _
 "default for strConsolFolder.")
 Err.Number = 0
 End If
 End If

 If strDebug = "Enabled" Then
 MsgBox "Registry settings retrieved: " & vbCrLf & vbCrLf & _
 "strEventLog" & vbTab & "=" & vbTab & strEventLog & vbCrLf & _
 "strDebug" & vbTab & vbTab & "=" & vbTab & strDebug & vbCrLf & _
 "strSvrList" & vbTab & vbTab & "=" & vbTab & strSvrList & vbCrLf & _
 "strArchive" & vbTab & "=" & vbTab & strArchive & vbCrLf & _
 "strRptFormat" & vbTab & "=" & vbTab & strRptFormat & vbCrLf & _
 "strNetworkNotification" & vbTab & "=" & vbTab & strNetworkNotification & _
 vbCrLf & _
 "strConsolFolder" & vbTab & "=" & vbTab & strConsolFolder, , cTitleBarMsg
 End If

End Sub

The IdentifyRptsToProcess() Subroutine

The logic presented in the IdentifyRptsToProcess() subroutine has already been demonstrated numerous times in other VBScripts shown in this book. This subroutine is responsible for setting the value assigned to a variable that identifies the name of the current day's summary reports.

Sub IdentifyRptsToProcess()

 Dim strFileNameString

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

 strConSolRptName = strConsolFolder & "" & strFileNameString & _
 "_ConsolSumRpt.txt"

 strFileNameString = strFileNameString & "_SumRpt.txt"

 strRpt1 = strArchive & "" & Left(strSvrList, 8) & "_" & strFileNameString
 strRpt2 = strArchive & "" & Right(strSvrList, 8) & "_" & strFileNameString

 If strDebug = "Enabled" Then
 MsgBox "1st summary report to process = " & strRpt1 & vbCrLf & _
 "2nd summary report to process = " & strRpt2, , cTitleBarMsg
 End If

End Sub

The ReadSummaryReport() Subroutine

The ReadSummaryReport() subroutine, shown below, uses the FileSystem Object object's FileExists(), OpenTextFile(), ReadLine(), and Close() methods and a Do…While loop to process the appropriate summary report. The report to be processed is identified by an argument passed to the subroutine. The subroutine stores the contents of each report in an array called either astrServ0001Array or astrServ0002Array.

Sub ReadSummaryReport(strServerName)

 If strDebug = "Enabled" Then
 MsgBox "Server = " & strServerName, , cTitleBarMsg
 End If

 Dim strSourFile

 If strServerName = "SERV0001" then
 strSourFile = strRpt1
 Else
 strSourFile = strRpt2
 End If

 Dim FileRef, strRptLine

 Dim intArrayCounter, IntErrLevel
 intArrayCounter = 0

 If (FsoObj.FileExists(strSourFile)) Then
 Set FileRef = FsoObj.OpenTextFile(strSourFile, cForReading)
 Do Until FileRef.AtEndOfStream

 strRptLine = FileRef.ReadLine()

 If strServerName = "SERV0001" Then
 If Instr(1, intArrayCounter, "Date") <> 1 Then
 If Instr(1, intArrayCounter, "Part") <> 1 Then
 ReDim Preserve astrServ0001Array(intArrayCounter)
 astrServ0001Array(intArrayCounter) = strRptLine
 End If
 End If
 Else
 If Instr(1, intArrayCounter, "Date") <> 1 Then
 If Instr(1, intArrayCounter, "Part") <> 1 Then
 ReDim Preserve astrServ0002Array(intArrayCounter)
 astrServ0002Array(intArrayCounter) = strRptLine
 End If
 End If
 End If

 intArrayCounter = intArrayCounter + 1

 Loop
 FileRef.Close()

 Else
 WriteToEventLog("Consolidated Summary Report Creator - unable to open " & _
 strSourFile)
 TerminateScript()
 End If

End Sub

The CreateConsolidatedTextReport() Subroutine

The CreateConsolidatedTextReport(), shown below, copies each summary report into an array and then creates the consolidated summary report by processing the contents of both arrays. It uses a variety of VBScript parsing functions to test, extract, and manipulate the contents of each line in each summary report before adding its data to the consolidated summary report.

Sub CreateConsolidatedTextReport()

 Dim intArrayCounter, OutPutFile, strMessage, strLocator
 Dim intQtyOne, intQtyTwo, intTotalQty, intSpacing, strMatch, strMatchlist
 Dim intInStockOne, intInStockTwo, intTotalInStock, intSpaces, intCounter2
 intArrayCounter = 0

 strLocator = "False"

 Set OutPutFile = FsoObj.OpenTextFile(strConSolRptName, 2, "True")

 If strDebug = "Enabled" Then
 MsgBox "Now creating to the Consolidated Summary Report"
 End If


 'Begin creating the consolidated summary report
 OutPutFile.WriteLine
"*******************************************************************************"
OutPutFile.WriteBlankLines(1)
OutPutFile.WriteLine "Master Consolidated Summary report for " & Date()
OutPutFile.WriteBlankLines(1)
OutPutFile.WriteLine
"*******************************************************************************"
 OutPutFile.WriteBlankLines(1)

 OutPutFile.WriteLine "Errors:"
 OutPutFile.WriteBlankLines(1)
 OutPutFile.WriteLine "Date Time Svr Code Description"

 'Process the Errors: section for the first server
 For Each intArrayCounter In astrServ0001Array

 If Instr(1, intArrayCounter, "Errors:") = 1 Then
 strLocator = "True"
 End If

 If strLocator = "True" Then
 If Instr(1, intArrayCounter, "Errors:") <> 1 Then
 If Instr(1, intArrayCounter, "Date Time Code Description") <> 1
Then
 If Instr(1, intArrayCounter, "-----") <> 1 Then
 If Len(intArrayCounter) > 0 Then
 intArrayCounter = Mid(intArrayCounter, 1, 17) & " Sr1 " & _
 Mid(intArrayCounter, 19)

 OutPutFile.WriteLine intArrayCounter
 End If
 Else
 Exit For
 End If
 End If
 End If
 End If

 Next

 intArrayCounter = 0
 strLocator = "False"

 'Process the Errors: section for the second server
 For Each intArrayCounter In astrServ0002Array

 If Instr(1, intArrayCounter, "Errors:") = 1 Then
 strLocator = "True"
 End If

 If strLocator = "True" Then
 If Instr(1, intArrayCounter, "Errors:") <> 1 Then
 If Instr(1, intArrayCounter, "Date Time Code Description") <> 1
Then
 If Instr(1, intArrayCounter, "-----") <> 1 Then
 If Len(intArrayCounter) > 0 Then
 intArrayCounter = Mid(intArrayCounter, 1, 17) & " Sr2 " & _
 Mid(intArrayCounter, 19)
 OutPutFile.WriteLine intArrayCounter
 End If
 Else
 Exit For
 End If
 End If
 End If
 End If
 Next

 OutPutFile.WriteBlankLines(1)
 OutPutFile.WriteLine "-------------------------------------------------" & _
 "------------------------------"
 OutPutFile.WriteBlankLines(1)

 OutPutFile.WriteLine "Sales summary:"
 OutPutFile.WriteBlankLines(1)
 OutPutFile.WriteLine "Government:"
 OutPutFile.WriteBlankLines(1)
 OutPutFile.WriteLine "Part # Qty Description"
 OutPutFile.WriteBlankLines(1)

 intArrayCounter = 0
 strLocator = "False"

 'Process the Sales summary: section for the first server
 For Each intArrayCounter In astrServ0001Array

 If Instr(1, intArrayCounter, "Sales summary") = 1 Then
 strLocator = "True"
 End If

 If strLocator = "True" Then
 If Instr(1, intArrayCounter, "Sales summary:") <> 1 Then
 If Instr(1, intArrayCounter, "Part # Qty Description") <> 1 Then
 If Instr(1, intArrayCounter, "-----") <> 1 Then
 If Len(intArrayCounter) > 0 Then
 intArrayCounter = Mid(intArrayCounter, 1, 17) & _
 Mid(intArrayCounter, 19)
 OutPutFile.WriteLine intArrayCounter
 End If
 Else
 Exit For
 End If
 End If
 End If
 End If

 Next

 OutPutFile.WriteBlankLines(1)
 OutPutFile.WriteLine "Other Customers:"
 OutPutFile.WriteBlankLines(1)
 OutPutFile.WriteLine "Part # Qty Description"
 OutPutFile.WriteBlankLines(1)

 intArrayCounter = 0
 strLocator = "False"

 'Process the Sales summary: section for the second server
 For Each intArrayCounter In astrServ0002Array

 If Instr(1, intArrayCounter, "Sales summary:") = 1 Then
 strLocator = "True"
 End If

 If strLocator = "True" Then
 If Instr(1, intArrayCounter, "Sales summary:") <> 1 Then
 If Instr(1, intArrayCounter, "Part # Qty Description") <> 1 Then
 If Instr(1, intArrayCounter, "-----") <> 1 Then
 If Len(intArrayCounter) > 0 Then
 intArrayCounter = Mid(intArrayCounter, 1, 17) & _
 Mid(intArrayCounter, 19)
 OutPutFile.WriteLine intArrayCounter
 End If
 Else
 Exit For
 End If
 End If
 End If
 End If

 Next
 OutPutFile.WriteBlankLines(1)
 OutPutFile.WriteLine "--------------------------------------------------" & _
 "-----------------------------"
 OutPutFile.WriteBlankLines(1)

 OutPutFile.WriteLine "Returns summary:"
 OutPutFile.WriteBlankLines(1)
 OutPutFile.WriteLine "Government:"
 OutPutFile.WriteBlankLines(1)
 OutPutFile.WriteLine "Part # Qty Description"
 OutPutFile.WriteBlankLines(1)

 intArrayCounter = 0
 strLocator = "False"

 'Process the Return summary: section for the first server
 For Each intArrayCounter In astrServ0001Array

 If Instr(1, intArrayCounter, "Return summary") = 1 Then
 strLocator = "True"
 End If

 If strLocator = "True" Then
 If Instr(1, intArrayCounter, "Return summary:") <> 1 Then
 If Instr(1, intArrayCounter, "Part # Qty Description") <> 1 Then
 If Instr(1, intArrayCounter, "-----") <> 1 Then
 If Len(intArrayCounter) > 0 Then
 intArrayCounter = Mid(intArrayCounter, 1, 17) & _
 Mid(intArrayCounter, 19)
 OutPutFile.WriteLine intArrayCounter
 End If
 Else
 Exit For
 End If
 End If
 End If
 End If

 Next
 OutPutFile.WriteBlankLines(1)
 OutPutFile.WriteLine "Other Customers:"
 OutPutFile.WriteBlankLines(1)
 OutPutFile.WriteLine "Part # Qty Description"
 OutPutFile.WriteBlankLines(1)

 intArrayCounter = 0
 strLocator = "False"

 'Process the Return summary: section for the second server
 For Each intArrayCounter In astrServ0002Array

 If Instr(1, intArrayCounter, "Return summary:") = 1 Then
 strLocator = "True"
 End If

 If strLocator = "True" Then
 If Instr(1, intArrayCounter, "Return summary:") <> 1 Then
 If Instr(1, intArrayCounter, "Part # Qty Description") <> 1 Then
 If Instr(1, intArrayCounter, "-----") <> 1 Then
 If Len(intArrayCounter) > 0 Then
 intArrayCounter = Mid(intArrayCounter, 1, 17) & _
 Mid(intArrayCounter, 19)
 OutPutFile.WriteLine intArrayCounter
 End If
 Else
 Exit For
 End If
 End If
 End If
 End If
 Next

 OutPutFile.WriteBlankLines(1)
 OutPutFile.WriteLine "--------------------------------------------------" & _
 "-----------------------------"
 OutPutFile.WriteBlankLines(1)

 OutPutFile.WriteLine "Daily Production Summary:"
 OutPutFile.WriteBlankLines(1)
 OutPutFile.WriteLine "Part # Qty Description In Stock"
 OutPutFile.WriteBlankLines(1)

 intArrayCounter = 0
 strLocator = "False"
 intCounter2 = 0

 'Process the Daily Production Summary section for the first server
 For Each intArrayCounter In astrServ0001Array

 If Instr(1, intArrayCounter, "Daily Production Summary") = 1 Then
 strLocator = "True"
 End If

 If strLocator = "True" Then

 If Instr(1, intArrayCounter, "Daily Production Summary") <> 1 Then
 If Instr(1, intArrayCounter, "Part # Qty Description In" & _
 "Stock") <> 1 Then
 If Len(intArrayCounter) > 0 Then

 ReDim Preserve astrProductionArray(intCounter2)
 astrProductionArray(intCounter2) = intArrayCounter
 intCounter2 = intCounter2 + 1

 End If
 End If
 End If
 End If

 Next

 intCounter2 = 0
 intArrayCounter = 0
 strLocator = "False"

 'Process the Daily Production Summary section for the first server
 For Each intArrayCounter In astrServ0002Array

 If Instr(1, intArrayCounter, "Daily Production Summary") = 1 Then
 strLocator = "True"
 End If

 If strLocator = "True" Then
 If Instr(1, intArrayCounter, "Daily Production Summary") <> 1 Then
 If Instr(1, intArrayCounter, "Part # Qty Description In" & _
 "Stock") <> 1 Then
 If Len(intArrayCounter) > 0 Then

 intArrayCounter = Mid(intArrayCounter, 1, 17) & _
 Mid(intArrayCounter, 19)

 'Spin though astrProductionArray and determine if there are any
 'matching entries to process
 intCounter2 = 0
 strMatch = "False"
 For Each intCounter2 In astrProductionArray
 If Mid(intArrayCounter, 1, 5) = Mid(intCounter2, 1, 5) Then

 strMatch = "True"
 strMatchlist = strMatchList & " " & _
 Mid(intArrayCounter, 1, 5)

 'Extract qty for both entries, add these values together and
 'write a single entry
 intQtyOne = Mid(intArrayCounter, 9, 5)
 intQtyOne = CInt(Trim(intQtyOne))
 intQtyTwo = Mid(intCounter2, 9, 5)
 intQtyTwo = CInt(Trim(intQtyTwo))
 intTotalQty = intQtyOne + intQtyTwo
 intSpacing = Len(intTotalQty)
 intSpacing = 5 - intSpacing

 'Extract In Stock value for both entries, add these values
 'together and write a single entry
 intInStockOne = Mid(intArrayCounter, 39, 3)
 intInStockOne = CInt(Trim(intInStockOne))
 intInStockTwo = Mid(intCounter2, 40, 3)
 intInStockTwo = CInt(Trim(intInStockTwo))
 intTotalInStock = intInStockOne + intInStockTwo
 intSpaces = Len(intTotalInStock)
 intSpaces = 4 - intSpaces

 OutPutFile.WriteLine Mid(intArrayCounter, 1, 5) & " " & _
 intTotalQty & Space(intSpacing) & _
 Mid(intArrayCounter, 14, 25) & Space(intSpaces) & _
 intTotalInStock
 End If
 Next
 If strmatch <> "True" Then
 OutPutFile.Writeline intArrayCounter
 End If

 End If
 End If
 End If
 End If

 Next

 'Process non-duplicate production inventory data on the second server
 For Each intArrayCounter In astrProductionArray
 If Instr(1, strMatchList, Mid(intArrayCounter, 1 ,5)) = 0 Then
 OutPutFile.WriteLine intArrayCounter
 End If
 Next

 If strDebug = "Enabled" Then
 MsgBox "Done writing to the Summary Report"
 End If

 OutPutFile.Close()

End Sub

The CreateConsolidatedWordReport() Subroutine

The CreateConsolidatedWordReport() subroutine, shown below, creates a Word version of the consolidated summary report. To simplify the creation of this report, Molly decided that rather than recreating the report from scratch, she would set up a Do…Until loop and use it to copy the contents of the text version of the report into an array. This subroutine would then process the array using a For Each…Next loop and the methods and properties of the Word object model. To make the Word version of the consolidated summary report easier to read, Molly modified the Font object's Name, Size, and Bold properties each time the subroutine wrote a report header. She accomplished this by setting up a series of If statements that use the Instr() function to identify headers as the For Each…Next loop iterated through each line of the text version of the consolidated summary report.

Sub CreatConsolidatedWordReport()

 Dim objWordDoc, strSourFile, FileRef, strRptLine, intWordCounter
 Dim strFileNameString

 ReDim astrWordVersionArray(0)

 Set objWordDoc = WScript.CreateObject("Word.Application")

 Set FileRef = FsoObj.OpenTextFile(strConSolRptName, cForReading)

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

 strConSolRptName = strConsolFolder & "" & strFileNameString & _
 "_ConsolSumRpt.doc"

 intWordCounter = 0

 If strDebug = "Enabled" Then
 MsgBox "Writing the Word version of the consolidated summary report."
 End If

 'Read the entire report into an array
 Do Until FileRef.AtEndOfStream

 strRptLine = FileRef.ReadLine()

 ReDim Preserve astrWordVersionArray(intWordCounter)
 astrWordVersionArray(intWordCounter) = strRptLine

 intWordCounter = intWordCounter + 1

 Loop

 FileRef.Close()

 'Start creating the Word document
 objWordDoc.Documents.Add()

 objWordDoc.Selection.Font.Name = "Courier"
 objWordDoc.Selection.Font.Size = 8
 objWordDoc.Selection.Font.Bold = False

 'Spin through the array, format and write the Word version of the report
 For Each intWordCounter in astrWordVersionArray

 'Change Font properties for selected report headings
 If Instr(1,intWordCounter, "Master Consolidated Summary") Then

 objWordDoc.Selection.Font.Name = "Arial"
 objWordDoc.Selection.Font.Size = 12
 objWordDoc.Selection.Font.Bold = True
 End If

 If Instr(1,intWordCounter, "Errors:") Then
 objWordDoc.Selection.Font.Name = "Arial"
 objWordDoc.Selection.Font.Size = 10
 objWordDoc.Selection.Font.Bold = True
 End If

 If Instr(1,intWordCounter, "Sales summary:") Then
 objWordDoc.Selection.Font.Name = "Arial"
 objWordDoc.Selection.Font.Size = 10
 objWordDoc.Selection.Font.Bold = True
 End If

 If Instr(1,intWordCounter, "Returns summary:") Then
 objWordDoc.Selection.Font.Name = "Arial"
 objWordDoc.Selection.Font.Size = 10
 objWordDoc.Selection.Font.Bold = True
 End If

 If Instr(1,intWordCounter, "Daily Production Summary") Then
 objWordDoc.Selection.Font.Name = "Arial"
 objWordDoc.Selection.Font.Size = 10
 objWordDoc.Selection.Font.Bold = True
 End If

 'Write a line of the report
 objWordDoc.Selection.Typetext(intWordCounter)

 'Add a paragraph marker (.e.g. linefeed)
 objWordDoc.Selection.TypeParagraph

 'Reset default Font properties
 objWordDoc.Selection.Font.Name = "Courier"
 objWordDoc.Selection.Font.Size = 8
 objWordDoc.Selection.Font.Bold = False

 Next

 'Save the Word file
 objWordDoc.ActiveDocument.SaveAs(strConSolRptName)

 'Close the document
 objWordDoc.ActiveDocument.Close()

 'Exit Word
 objWordDoc.Quit()

End Sub

The NotifyOperationsStaff() Subroutine

The NotifyOperationsStaff() subroutine, shown below, uses the VBScript Split() functions to create an array containing the names of selected operations staff members who should be sent a network message indicating that the consolidated summary report is now available. A For Each…Next loop is then set up to spin through the array and send a message to each username using the Net Send command.

Sub NotifyOperationsStaff()

 On Error Resume Next

 Dim strUserName, strNtkNotifyList

 Dim astrNotifyArray

 strNtkNotifyList = "MJLF001 ASCK001"

 strNtkNotifyList = _
 WshShl.RegRead("HKLMSoftwareIntuitVBScriptsMstSumRptsNtkNotifyList")
 If Err <> 0 Then
 If strEventLog = "Enabled" Then
 WriteToEventLog ("Summary Report Collection script - Using default " & _
 "for strNtkNotifyList.")
 Err.Number = 0
 End If

 End If

 astrNotifyArray = Split(strNtkNotifyList)

 For Each strUserName In astrNotifyArray
 WshShl.Run "Net Send " & strUserName & " " & "OrderInventory " & _
 "consolidated report now available."
 Next

End Sub

The WriteToEventLog() Subroutine

As is the case with previous scripts, the WriteToEventLog() subroutine writes informational messages to the Windows application event log using a string passed to it as an argument.

Sub WriteToEventLog(strMessage)

 WshShl.LogEvent 4, strMessage

End Sub

The TerminateScript() Subroutine

The TerminateScript() subroutine, shown below, uses the WScript object's Quit() method to terminate the script's execution.

Sub TerminateScript()

 WScript.Quit()

End Sub

The Fully Assembled Script

The fully assembled VBScript is shown below. Molly will execute it as a background task on the Windows 2000 Professional workstation located in the operations command center. Depending on its registry configuration settings, it will create a text version and possibly a Word version of the consolidated summary report and then notify selected operations staff members of its availability.

'*************************************************************************
'Script Name: Script 24.1.vbs
'Author: Jerry Ford
'Created: 04/13/03
'Description: This script reads and processes the daily summary reports from
'both Windows 2000 Servers where the OrderInventory system resides
'*************************************************************************

'Initialization Section

Option Explicit

Dim strEventLog, strDebug, strSvrList, strArchive, strConsolFolder
Dim strRpt1, strRpt2, strConSolRptName, strRptFormat, strNetworkNotification

ReDim astrServ0001Array(0)
ReDim astrServ0002Array(0)
ReDim astrProductionArray(0)

Dim FsoObj, WshShl

Const cTitleBarMsg = "Consolidated Summary Report Creator"
Const cForReading = 1
Const cForWriting = 2
Const cForAppending = 8

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


'Main Processing Section

SetDefaultSettings()
GetRegistrySettings()

 If strEventLog = "Enabled" Then
 WriteToEventLog("Consolidated Summary Report Creator executing.")
 End If

 IdentifyRptsToProcess()

 ReadSummaryReport(Left(strSvrList, 8))
 ReadSummaryReport(Right(strSvrList, 8))

 CreateConsolidatedTextReport()

 If strRptFormat = "Word" Then
 CreatConsolidatedWordReport()
 End If

 If strNetworkNotification = "Enabled" Then
 NotifyOperationsStaff()
 End If

 If strEventLog = "Enabled" Then
 WriteToEventLog("Consolidated Summary Report Creator finished.")
 End If

 TerminateScript()


 'Procedure Section

 Sub SetDefaultSettings()

 strEventLog = "Enabled"
 strDebug = "Disabled"
 strSvrList = "SERV0001 SERV0002"
 strArchive = "d:Order_InventroryLogFiles"
 strRptFormat = "Text"
 strNetworkNotification = "Enabled"
 strConsolFolder = "d:Order_InventoryConsolidatedRpts"
 If strDebug = "Enabled" Then
 MsgBox "Registry settings retrieved: " & vbCrLf & vbCrLf & _
 "strEventLog" & vbTab & "=" & vbTab & strEventLog & vbCrLf & _
 "strDebug" & vbTab & vbTab & "=" & vbTab & strDebug & vbCrLf & _
 "strSvrList" & vbTab & vbTab & "=" & vbTab & strSvrList & vbCrLf & _
 "strArchive" & vbTab & "=" & vbTab & strArchive & vbCrLf & _
 "strRptFormat" & vbTab & "=" & vbTab & strRptFormat & vbCrLf & _
 "strNetworkNotification" & vbTab & "=" & vbTab & strNetworkNotification & _
 vbCrLf & _
 "strConsolFolder" & vbTab & "=" & vbTab & strConsolFolder, , cTitleBarMsg
 End If

End Sub

Sub GetRegistrySettings()

 On Error Resume Next

 strEventLog = _
 WshShl.RegRead("HKLMSoftwareIntuitVBScriptsMstSumRptsEventLogging")
 If Err <> 0 Then
 If strEventLog = "Enabled" Then
 WriteToEventLog ("Summary Report Collection script - Using " & _
 "default for strEventLog.")
 Err.Number = 0
 End If
 End If

 strDebug = WshShl.RegRead("HKLMSoftwareIntuitVBScriptsMstSumRptsDebugMode")
 If Err <> 0 Then
 If strEventLog = "Enabled" Then
 WriteToEventLog ("Summary Report Collection script - Using " & _
 "default for strDebug.")
 Err.Number = 0
 End If
 End If

 strSvrList = _
 WshShl.RegRead("HKLMSoftwareIntuitVBScriptsMstSumRptsWin2000Svrs")
 If Err <> 0 Then
 If strEventLog = "Enabled" Then
 WriteToEventLog ("Summary Report Collection script - Using " & _
 "default for strSvrList.")
 Err.Number = 0
 End If
 End If

 strArchive = _
 WshShl.RegRead("HKLMSoftwareIntuitVBScriptsMstSumRptsRptArchive")
 If Err <> 0 Then
 If strEventLog = "Enabled" Then
 WriteToEventLog ("Summary Report Collection script - Using " & _
 "default for strArchive.")
 Err.Number = 0
 End If
 End If

 strRptFormat = _
 WshShl.RegRead("HKLMSoftwareIntuitVBScriptsMstSumRptsRptFormat")
 If Err <> 0 Then
 If strEventLog = "Enabled" Then
 WriteToEventLog ("Summary Report Collection script - Using " & _
 "default for strRptFormat.")
 Err.Number = 0
 End If
 End If

 strNetworkNotification =
WshShl.RegRead("HKLMSoftwareIntuitVBScriptsMstSumRptsNetworkNotification")
 If Err <> 0 Then
 If strEventLog = "Enabled" Then
 WriteToEventLog ("Summary Report Collection script - Using default for
strNetworkNotification.")
 Err.Number = 0
 End If
 End If
 strConsolFolder = _
 WshShl.RegRead("HKLMSoftwareIntuitVBScriptsMstSumRptsConsolFolder")
 If Err <> 0 Then
 If strEventLog = "Enabled" Then
 WriteToEventLog ("Summary Report Collection script - Using " & _
 "default for strConsolFolder.")
 Err.Number = 0
 End If
 End If

 If strDebug = "Enabled" Then
 MsgBox "Registry settings retrieved: " & vbCrLf & vbCrLf & _
 "strEventLog" & vbTab & "=" & vbTab & strEventLog & vbCrLf & _
 "strDebug" & vbTab & vbTab & "=" & vbTab & strDebug & vbCrLf & _
 "strSvrList" & vbTab & vbTab & "=" & vbTab & strSvrList & vbCrLf & _
 "strArchive" & vbTab & "=" & vbTab & strArchive & vbCrLf & _
 "strRptFormat" & vbTab & "=" & vbTab & strRptFormat & vbCrLf & _
 "strNetworkNotification" & vbTab & "=" & vbTab & strNetworkNotification & _
 vbCrLf & _
 "strConsolFolder" & vbTab & "=" & vbTab & strConsolFolder, , cTitleBarMsg
 End If

End Sub

Sub IdentifyRptsToProcess()

 Dim strFileNameString

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

 strConSolRptName = strConsolFolder & "" & strFileNameString & _
 "_ConsolSumRpt.txt"

 strFileNameString = strFileNameString & "_SumRpt.txt"

 strRpt1 = strArchive & "" & Left(strSvrList, 8) & "_" & strFileNameString
 strRpt2 = strArchive & "" & Right(strSvrList, 8) & "_" & strFileNameString
 If strDebug = "Enabled" Then
 MsgBox "1st summary report to process = " & strRpt1 & vbCrLf & _
 "2nd summary report to process = " & strRpt2, , cTitleBarMsg
 End If

End Sub

Sub ReadSummaryReport(strServerName)

 If strDebug = "Enabled" Then
 MsgBox "Server = " & strServerName, , cTitleBarMsg
 End If

 Dim strSourFile

 If strServerName = "SERV0001" then
 strSourFile = strRpt1
 Else
 strSourFile = strRpt2
 End If

 Dim FileRef, strRptLine

 Dim intArrayCounter, IntErrLevel
 intArrayCounter = 0

 If (FsoObj.FileExists(strSourFile)) Then
 Set FileRef = FsoObj.OpenTextFile(strSourFile, cForReading)
 Do Until FileRef.AtEndOfStream

 strRptLine = FileRef.ReadLine()

 If strServerName = "SERV0001" Then

 If Instr(1, intArrayCounter, "Date") <> 1 Then
 If Instr(1, intArrayCounter, "Part") <> 1 Then
 ReDim Preserve astrServ0001Array(intArrayCounter)
 astrServ0001Array(intArrayCounter) = strRptLine
 End If
 End If
 Else
 If Instr(1, intArrayCounter, "Date") <> 1 Then
 If Instr(1, intArrayCounter, "Part") <> 1 Then
 ReDim Preserve astrServ0002Array(intArrayCounter)
 astrServ0002Array(intArrayCounter) = strRptLine
 End If
 End If
 End If
 intArrayCounter = intArrayCounter + 1

 Loop
 FileRef.Close()
 Else
 WriteToEventLog("Consolidated Summary Report Creator - unable to open " & _
 strSourFile)
 TerminateScript()
 End If

End Sub

Sub CreateConsolidatedTextReport()

 Dim intArrayCounter, OutPutFile, strMessage, strLocator
 Dim intQtyOne, intQtyTwo, intTotalQty, intSpacing, strMatch, strMatchlist
 Dim intInStockOne, intInStockTwo, intTotalInStock, intSpaces, intCounter2
 intArrayCounter = 0
 strLocator = "False"

 Set OutPutFile = FsoObj.OpenTextFile(strConSolRptName, 2, "True")

 If strDebug = "Enabled" Then
 MsgBox "Now creating to the Consolidated Summary Report"
 End If

 'Begin creating the consolidated summary report
 OutPutFile.WriteLine
"*******************************************************************************"
 OutPutFile.WriteBlankLines(1)
 OutPutFile.WriteLine "Master Consolidated Summary report for " & Date()
 OutPutFile.WriteBlankLines(1)
 OutPutFile.WriteLine
"*******************************************************************************"
 OutPutFile.WriteBlankLines(1)

 OutPutFile.WriteLine "Errors:"
 OutPutFile.WriteBlankLines(1)
 OutPutFile.WriteLine "Date Time Svr Code Description"

 'Process the Errors: section for the first server
 For Each intArrayCounter In astrServ0001Array

 If Instr(1, intArrayCounter, "Errors:") = 1 Then
 strLocator = "True"
 End If

 If strLocator = "True" Then
 If Instr(1, intArrayCounter, "Errors:") <> 1 Then
 If Instr(1, intArrayCounter, "Date Time Code Description") <> 1 Then
 If Instr(1, intArrayCounter, "-----") <> 1 Then
 If Len(intArrayCounter) > 0 Then
 intArrayCounter = Mid(intArrayCounter, 1, 17) & " Sr1 " & _
 Mid(intArrayCounter, 19)
 OutPutFile.WriteLine intArrayCounter
 End If
 Else
 Exit For
 End If
 End If
 End If
 End If
 Next

 intArrayCounter = 0
 strLocator = "False"

 'Process the Errors: section for the second server
 For Each intArrayCounter In astrServ0002Array

 If Instr(1, intArrayCounter, "Errors:") = 1 Then
 strLocator = "True"
 End If

 If strLocator = "True" Then
 If Instr(1, intArrayCounter, "Errors:") <> 1 Then
 If Instr(1, intArrayCounter, "Date Time Code Description") <> 1
 Then
 If Instr(1, intArrayCounter, "-----") <> 1 Then
 If Len(intArrayCounter) > 0 Then
 intArrayCounter = Mid(intArrayCounter, 1, 17) & " Sr2 " & _
 Mid(intArrayCounter, 19)
 OutPutFile.WriteLine intArrayCounter
 End If
 Else
 Exit For
 End If
 End If
 End If
 End If

 Next

 OutPutFile.WriteBlankLines(1)
 OutPutFile.WriteLine "--------------------------------------------------" & _
 "-----------------------------"
 OutPutFile.WriteBlankLines(1)

 OutPutFile.WriteLine "Sales summary:"
 OutPutFile.WriteBlankLines(1)

 OutPutFile.WriteLine "Government:"
 OutPutFile.WriteBlankLines(1)
 OutPutFile.WriteLine "Part # Qty Description"
 OutPutFile.WriteBlankLines(1)

 intArrayCounter = 0
 strLocator = "False"

 'Process the Sales summary: section for the first server
 For Each intArrayCounter In astrServ0001Array

 If Instr(1, intArrayCounter, "Sales summary") = 1 Then
 strLocator = "True"
 End If

 If strLocator = "True" Then
 If Instr(1, intArrayCounter, "Sales summary:") <> 1 Then
 If Instr(1, intArrayCounter, "Part # Qty Description") <> 1 Then
 If Instr(1, intArrayCounter, "-----") <> 1 Then
 If Len(intArrayCounter) > 0 Then
 intArrayCounter = Mid(intArrayCounter, 1, 17) & _
 Mid(intArrayCounter, 19)
 OutPutFile.WriteLine intArrayCounter
 End If
 Else
 Exit For
 End If
 End If
 End If
 End If

 Next

 OutPutFile.WriteBlankLines(1)
 OutPutFile.WriteLine "Other Customers:"
 OutPutFile.WriteBlankLines(1)
 OutPutFile.WriteLine "Part # Qty Description"
 OutPutFile.WriteBlankLines(1)
 intArrayCounter = 0
 strLocator = "False"

 'Process the Sales summary: section for the second server
 For Each intArrayCounter In astrServ0002Array

 If Instr(1, intArrayCounter, "Sales summary:") = 1 Then
 strLocator = "True"
 End If

 If strLocator = "True" Then
 If Instr(1, intArrayCounter, "Sales summary:") <> 1 Then
 If Instr(1, intArrayCounter, "Part # Qty Description") <> 1 Then
 If Instr(1, intArrayCounter, "-----") <> 1 Then
 If Len(intArrayCounter) > 0 Then
 intArrayCounter = Mid(intArrayCounter, 1, 17) & _
 Mid(intArrayCounter, 19)
 OutPutFile.WriteLine intArrayCounter
 End If
 Else
 Exit For
 End If
 End If
 End If
 End If

 Next

 OutPutFile.WriteBlankLines(1)
 OutPutFile.WriteLine "--------------------------------------------------" & _
 "-----------------------------"

 OutPutFile.WriteBlankLines(1)

 OutPutFile.WriteLine "Returns summary:"
 OutPutFile.WriteBlankLines(1)
 OutPutFile.WriteLine "Government:"
 OutPutFile.WriteBlankLines(1)

 OutPutFile.WriteLine "Part # Qty Description"
 OutPutFile.WriteBlankLines(1)

 intArrayCounter = 0
 strLocator = "False"

 'Process the Return summary: section for the first server
 For Each intArrayCounter In astrServ0001Array

 If Instr(1, intArrayCounter, "Return summary") = 1 Then
 strLocator = "True"
 End If

 If strLocator = "True" Then
 If Instr(1, intArrayCounter, "Return summary:") <> 1 Then
 If Instr(1, intArrayCounter, "Part # Qty Description") <> 1 Then
 If Instr(1, intArrayCounter, "-----") <> 1 Then
 If Len(intArrayCounter) > 0 Then
 intArrayCounter = Mid(intArrayCounter, 1, 17) & _
 Mid(intArrayCounter, 19)
 OutPutFile.WriteLine intArrayCounter
 End If
 Else
 Exit For
 End If
 End If
 End If
 End If
Next

OutPutFile.WriteBlankLines(1)
OutPutFile.WriteLine "Other Customers:"
OutPutFile.WriteBlankLines(1)
OutPutFile.WriteLine "Part # Qty Description"
OutPutFile.WriteBlankLines(1)

intArrayCounter = 0
 strLocator = "False"

 'Process the Return summary: section for the second server
 For Each intArrayCounter In astrServ0002Array

 If Instr(1, intArrayCounter, "Return summary:") = 1 Then
 strLocator = "True"
 End If

 If strLocator = "True" Then
 If Instr(1, intArrayCounter, "Return summary:") <> 1 Then
 If Instr(1, intArrayCounter, "Part # Qty Description") <> 1 Then
 If Instr(1, intArrayCounter, "-----") <> 1 Then
 If Len(intArrayCounter) > 0 Then
 intArrayCounter = Mid(intArrayCounter, 1, 17) & _
 Mid(intArrayCounter, 19)
 OutPutFile.WriteLine intArrayCounter
 End If
 Else
 Exit For
 End If
 End If
 End If
 End If

Next

OutPutFile.WriteBlankLines(1)
OutPutFile.WriteLine "--------------------------------------------------" & _
 "-----------------------------"
OutPutFile.WriteBlankLines(1)

OutPutFile.WriteLine "Daily Production Summary:"
OutPutFile.WriteBlankLines(1)
OutPutFile.WriteLine "Part # Qty Description In Stock"
OutPutFile.WriteBlankLines(1)

intArrayCounter = 0

 strLocator = "False"
 intCounter2 = 0

 'Process the Daily Production Summary section for the first server
 For Each intArrayCounter In astrServ0001Array

 If Instr(1, intArrayCounter, "Daily Production Summary") = 1 Then
 strLocator = "True"
 End If

 If strLocator = "True" Then
 If Instr(1, intArrayCounter, "Daily Production Summary") <> 1 Then
 If Instr(1, intArrayCounter, "Part # Qty Description In" & _
 " Stock") <> 1 Then
 If Len(intArrayCounter) > 0 Then

 ReDim Preserve astrProductionArray(intCounter2)
 astrProductionArray(intCounter2) = intArrayCounter
 intCounter2 = intCounter2 + 1

 End If
 End If
 End If
 End If

 Next

 intCounter2 = 0
 intArrayCounter = 0
 strLocator = "False"

 'Process the Daily Production Summary section for the first server
 For Each intArrayCounter In astrServ0002Array

 If Instr(1, intArrayCounter, "Daily Production Summary") = 1 Then
 strLocator = "True"
 End If
 If strLocator = "True" Then
 If Instr(1, intArrayCounter, "Daily Production Summary") <> 1 Then
 If Instr(1, intArrayCounter, "Part # Qty Description In" & _
 " Stock") <> 1 Then
 If Len(intArrayCounter) > 0 Then

 intArrayCounter = Mid(intArrayCounter, 1, 17) & _
 Mid(intArrayCounter, 19)

 'Spin though astrProductionArray and determine if there are any
 'matching entries to process
 intCounter2 = 0
 strMatch = "False"
 For Each intCounter2 In astrProductionArray
 If Mid(intArrayCounter, 1, 5) = Mid(intCounter2, 1, 5) Then

 strMatch = "True"
 strMatchlist = strMatchList & " " & _
 Mid(intArrayCounter, 1, 5)

 'Extract qty for both entries, add these values together and
 'write a single entry
 intQtyOne = Mid(intArrayCounter, 9, 5)
 intQtyOne = CInt(Trim(intQtyOne))
 intQtyTwo = Mid(intCounter2, 9, 5)
 intQtyTwo = CInt(Trim(intQtyTwo))
 intTotalQty = intQtyOne + intQtyTwo
 intSpacing = Len(intTotalQty)
 intSpacing = 5 - intSpacing

 'Extract In Stock value for both entries, add these values
 'together and write a single entry
 intInStockOne = Mid(intArrayCounter, 39, 3)
 intInStockOne = CInt(Trim(intInStockOne))
 intInStockTwo = Mid(intCounter2, 40, 3)
 intInStockTwo = CInt(Trim(intInStockTwo))
 intTotalInStock = intInStockOne + intInStockTwo
 intSpaces = Len(intTotalInStock)
 intSpaces = 4 - intSpaces
 OutPutFile.WriteLine Mid(intArrayCounter, 1, 5) & " " & _
 intTotalQty & Space(intSpacing) & _
 Mid(intArrayCounter, 14, 25) & Space(intSpaces) & _
 intTotalInStock
 End If
 Next
 If strmatch <> "True" Then
 OutPutFile.Writeline intArrayCounter
 End If

 End If
 End If
 End If
 End If

 Next

 'Process non-duplicate production inventory data on the second server
 For Each intArrayCounter In astrProductionArray
 If Instr(1, strMatchList, Mid(intArrayCounter, 1 ,5)) = 0 Then
 OutPutFile.WriteLine intArrayCounter
 End If
 Next

 If strDebug = "Enabled" Then
 MsgBox "Done writing to the Summary Report"
 End If

 OutPutFile.Close()

End Sub

Sub CreatConsolidatedWordReport()

 Dim objWordDoc, strSourFile, FileRef, strRptLine, intWordCounter
 Dim strFileNameString
 ReDim astrWordVersionArray(0)

 Set objWordDoc = WScript.CreateObject("Word.Application")

 Set FileRef = FsoObj.OpenTextFile(strConSolRptName, cForReading)

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

 strConSolRptName = strConsolFolder & "" & strFileNameString & _
 "_ConsolSumRpt.doc"

 intWordCounter = 0

 If strDebug = "Enabled" Then
 MsgBox "Writing the Word version of the consolidated summary report."
 End If

 'Read the entire report into an array
 Do Until FileRef.AtEndOfStream

 strRptLine = FileRef.ReadLine()

 ReDim Preserve astrWordVersionArray(intWordCounter)
 astrWordVersionArray(intWordCounter) = strRptLine

 intWordCounter = intWordCounter + 1

 Loop

 FileRef.Close()

 'Start creating the Word document
 objWordDoc.Documents.Add()

 objWordDoc.Selection.Font.Name = "Courier"
 objWordDoc.Selection.Font.Size = 8
 objWordDoc.Selection.Font.Bold = False
 'Spin through the array, format and write the Word version of the report
 For Each intWordCounter in astrWordVersionArray

 'Change Font properties for selected report headings
 If Instr(1,intWordCounter, "Master Consolidated Summary") Then

 objWordDoc.Selection.Font.Name = "Arial"
 objWordDoc.Selection.Font.Size = 12
 objWordDoc.Selection.Font.Bold = True

 End If

 If Instr(1,intWordCounter, "Errors:") Then
 objWordDoc.Selection.Font.Name = "Arial"
 objWordDoc.Selection.Font.Size = 10
 objWordDoc.Selection.Font.Bold = True
 End If

 If Instr(1,intWordCounter, "Sales summary:") Then
 objWordDoc.Selection.Font.Name = "Arial"
 objWordDoc.Selection.Font.Size = 10
 objWordDoc.Selection.Font.Bold = True
 End If

 If Instr(1,intWordCounter, "Returns summary:") Then
 objWordDoc.Selection.Font.Name = "Arial"
 objWordDoc.Selection.Font.Size = 10
 objWordDoc.Selection.Font.Bold = True
 End If

 If Instr(1,intWordCounter, "Daily Production Summary") Then
 objWordDoc.Selection.Font.Name = "Arial"
 objWordDoc.Selection.Font.Size = 10
 objWordDoc.Selection.Font.Bold = True
 End If

 'Write a line of the report
 objWordDoc.Selection.Typetext(intWordCounter)

 'Add a paragraph marker (.e.g. linefeed)
 objWordDoc.Selection.TypeParagraph

 'Reset default Font properties
 objWordDoc.Selection.Font.Name = "Courier"
 objWordDoc.Selection.Font.Size = 8
 objWordDoc.Selection.Font.Bold = False

 Next

 'Save the Word file
 objWordDoc.ActiveDocument.SaveAs(strConSolRptName)

 'Close the document
 objWordDoc.ActiveDocument.Close()

 'Exit Word
 objWordDoc.Quit()

 End Sub

 Sub NotifyOperationsStaff()

 On Error Resume Next

 Dim strUserName, strNtkNotifyList

 Dim astrNotifyArray

 strNtkNotifyList = "MJLF001 ASCK001"

 strNtkNotifyList = _
 WshShl.RegRead("HKLMSoftwareIntuitVBScriptsMstSumRptsNtkNotifyList")
 If Err <> 0 Then
 If strEventLog = "Enabled" Then
 WriteToEventLog ("Summary Report Collection script - Using " & _
 "default for strNtkNotifyList.")
 Err.Number = 0
 End If
 End If

 astrNotifyArray = Split(strNtkNotifyList)

 For Each strUserName In astrNotifyArray
 WshShl.Run "Net Send " & strUserName & " " & "OrderInventory consolidated
report now available."

 Next

End Sub

Sub WriteToEventLog(strMessage)

 WshShl.LogEvent 4, strMessage

End Sub

Sub TerminateScript()

 WScript.Quit()

End Sub

Figure 24.2 shows a sample portion of the Word report created by the script. As you can see, by changing font properties, the report has been made easier to read.

click to expand
Figure 24.2: Examining the Word version of the consolidated summary report


Summary

In this chapter, you learned how to develop a VBScript that processes the summary reports collected from the two Windows 2000 servers that support Intuit's order/inventory system. You also learned how to work with the Word object model in order to develop a Word version of the consolidated report. In addition, you learned how to use the Net Send command to create a network notification process.


Part I - Introducing Microsoft VBScriptBasics

Part II - Professional Project 1 Desktop Administration Using VBScript and the WSH

Part III - Professional Project 2 Analyzing Application Logs

Part IV - Professional Project 3 Creating a Centralized Report Management Station

Part V - Professional Project 4 Reporting Application Summary Data via the Web

Part VI - Introducing Microsoft VBScriptBasics



Microsoft VBScript Professional Projects
Microsoft VBScript Professional Projects
ISBN: 1592000568
EAN: 2147483647
Year: 2005
Pages: 366

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net