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.

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:

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.

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.

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.

Related

Категории