In the first article of this series, I explained a client’s order entry process, which, although not very complicated, required a lot of duplicate data entry that resulted in wasted time and unnecessary data entry errors. My clients were entering orders into their order entry system that two of their providers had already entered into their own systems. My client would receive these orders electronically, one via a Microsoft Excel file downloaded off the Internet and the other by e-mail. My task was to extract the order data from these sources and load it into my client’s order entry system in a way that would require a minimal amount of labor.

The client’s order entry system already had an XML interface that was able to import XML files validated against a schema provided by the vendor. In the first article, I created a COM object that encapsulated the XML file. This ORDER object had properties representing the various elements of the XML schema. It also had a collection of LINEITEM objects, which encapsulated the details about each item that was part of the order. This XML Writer COM object would be called by front-end interfaces that I would integrate with the various methods when the client received order data. As an added bonus, I would integrate the XML Writer with the company’s intranet allowing salespeople to enter order information over their VPN connections while out of the office.

Finishing the XML component
There were two other methods I needed to complete the XML Writer. The first would set property values specific to the client or front end the component was being called by. Although these could have been left more generic and implemented in the individual front ends, I decided to put them in the XML Writer to keep them centralized. The methods would be named SetClientXEnvironment(), where the X identified a particular client or front end. Not elegant, but definitely in keeping with the “keep it simple” principle. SetClientXEnvironment() would set a root for the XML file name that corresponded to the client the order was coming from and set a variable to the client’s name. It would also set the path where the XML file would be written. A typical SetClientXEnvironment() method would look like this:
Public Sub SetClientEnvironment()
    strClientRoot = “AR”
‘used to name the order data XML file
    strClientID = “ImageX”
‘used in the XML file CLIENT_NUM element
    strCatalogNumber = “1001”
‘used in the XML file ITEM element
strFileRoot = strClientRoot & Right(“00” & Month(Now()), 2) & Right(“00” & Day(Now()), 2) & Right(“000” & Hour(Now()), 2) & Right(“000” & Minute(Now()), 2)
‘name the file with the client root and a timestamp
   strXMLFilePath = “Q:\inbox\”
‘path to the order entry system inbox
End Sub

The second method would actually write the XML file. The XML Writer stored the order data in properties and collections. The method to create the actual file would read through these properties, generate the XML data, and write it to a file. After setting the required project references, I created a new XML document in memory and added the root element required by the schema.
Dim xXML As MSXML2.DOMDocument26
Dim nodeRoot As MSXML2.IXMLDOMElement
Set xXML = New MSXML2.DOMDocument26
Set nodeRoot = xXML.createElement(“ORDER”)
nodeRoot.setAttribute “ordernum”, strOrderNumber

xXML represented the complete XML data structure. nodeRoot provided a reference to the root node to which all other nodes would attach. Attributes are added to an XML element using the setAttribute method, which takes the attribute name as the first parameter and the value as the second. strOrderNumber is the private variable in the XML Writer object that represents the order number. If strOderNumber = “123abc” then I would now have an XML data structure as such:
<ORDER ordernum=”123abc”></ORDER>

The next element to add was the CLIENT_NUM element, the value of which is stored in the strClientID variable.
Dim nodeRoot As MSXML2.IXMLDOMElement
Set newNode = xXML.createElement(“CLIENT”)
newNode.Text = strClientID
nodeRoot.appendChild newNode

First, create a new element object for the <CLIENT> element. The text method assigns the text that would be enclosed by the opening and closing element tags. Finally, append the new element to the root node. If strClientID = “MyClient”, my XML data now looks like this:
<ORDER ordernum=”123abc”>

The remaining elements are added in the same manner. The line item data are written to <ITEM> elements that are children of the <LINE_ITEM> element. In this case, the <LINE_ITEM> element is created and appended to the nodeRoot, as shown before. The <ITEM> elements are then created, their values set appropriately, and appended to the <LINE_ITEM> element rather than to the root element. Once the XML data structure is completed, writing it is as simple as a single command:
textFile.Write nodeRoot.XML

where textFile is a valid text file. The XML property of the nodeRoot object is a string variable representing the entire XML data structure that was built and appended to the nodeRoot object. The XML Writer component creates the text file in the inbox folder of the order entry system. Once there, the XML gets picked up and processed as part of the normal system’s processes.

Integrating with Excel
My clients sign on to their provider’s Web site and open a report containing the data for a single order. The provider also provided a hyperlink, which would let my client open this report in Microsoft Excel through their Web browser (done by changing the MIME type of the Web server—a topic for a future article). The report contained one row per line item. The columns held all the data needed and more.

My approach was to write an Excel module, stored in an Excel Add-In, which would be triggered by a custom menu item. I couldn’t be sure of the order that the columns were in, so, first, the module would sort the columns into an order I would define and verify that the required data was present. Then, it would loop through the rows and create an XML order file for each order, by instantiating the XML Writer component and setting the properties as required.

I knew that the first row would always contain the column headers, but, because of the flexibility that the provider gave my client in downloading the report, the columns could be in any order. To sort them to the order I required, I inserted a row above the header row and a number based on the value of the header field. The code to do this is as follows:
Dim currentCell as Excel.Range
Set currentCell = Application.ActiveSheet.Range(“A1”)
Set currentCell = Application.ActiveSheet.Range(“A2”)
Do While Not IsEmpty(currentCell)
Set nextCell = currentCell.Offset(0, 1)
Set topCell = currentCell.Offset(-1, 0)
Select Case LCase(currentCell.Value)
            Case “po number”
                topCell.Value = 1
            Case “shipping method”
                topCell.Value = 1
       End Select
       Set currentCell = nextCell

currentCell is used as a reference to the cell that the module is currently working with. The first 3 lines of code set the current cell to A1 and then insert a row above it. This moves the column headers to the second row. The 4th line sets currentCell to cell A2 and the Do While loop will move currentCell across the second row, interrogating the value of the column header and inserting an order number in the cell above it. nextCell is set to the cell to the right of currentCell and topCell is set to the cell above currentCell.

Once this code is run, the first row of the worksheet contains the order numbers that I want the columns to be in. The macro then sorts the worksheet based on the values in the first row and then deletes the first row.
Set currentCell = Application.ActiveSheet.Range(“A1”)
currentCell.Sort currentCell, xlAscending,,,,,,,,,xlSortRows
Set currentCell = Application.ActiveSheet.Range(“A1”)

The columns are now in a defined order. Then, the rows are sorted by PO Number, which, you can see from the code above, is in the first column. This ensures that all the rows with order data are together and any blank rows are moved to the end of the sheet.
    Set currentCell = Application.ActiveSheet.Range(“A1”)
   currentCell.Sort currentCell, xlDescending,,,,,,,,,xlSortColumns

Next, I instantiate the XML Writer component. Certain values are duplicated in each row, like the date, order number, and cost center. I read them from the first row I process. Then, I loop through all the rows and set the properties for the line items with the appropriate values. The XML Writer takes care of creating the XML data structure and writing the XML file.

Looping through the rows meant moving currentCell down the first column until it reached a blank cell:
      Dim objOrder as XMLWriter.Order
       Set objOrder = New XMLWriter.Order
Set currentCell = Application.ActiveSheet.Range(“A2”)
… code to process the values needed only once goes here
      Do While Not IsEmpty(currentCell)
… code to process the line item data in each row goes here
     Set currentCell = currentCell.Offset(1, 0)

currentCell would always be set to the first cell in a row. Because I sorted the columns to a specified order, I knew how many columns to the right each required data element was. The code to get the values needed only once is as follows (assume the order number is in column 2, the client number is in column 3, and the date is in column 4):
objOrder.OrderNumber = currentCell.Offset(0,1)
objOrder.ClientNum = currentCell.Offset(0,2)
objOrder.Date = currentCell.Offset(0,3)
… set remaining properties

Then, in the Do While loop to process the line item data, the code would look like this:
       Set objLineItem = New XMLWriter.LineItem
       objLineItem.ItemID = currentCell.Offset(0,7)
       objLineItem.Catalog = currentCell.Offset(0,8)
       objLineItem.Description = currentCell.Offset(0,9)
       … set remaining properties

Once all the rows are processed, call a single method to write the XML file.

All the code to create the XML data file from the properties and write it to a file is contained in the XML Writer component. That’s all there is to it.

Next up: Integration with Outlook
In the next section, I’ll cover integrating the same XML Writer component with Microsoft Outlook to show how we processed order data received by e-mail. Then, we’ll finish by integrating the component with an Active Server Page running on the client’s Web server, which enables the salespeople to enter order data while working remotely over the company’s virtual private network.