Working with ADO.NET Datasets in Microsoft Office
Chris Kunicki
OfficeZealot.com
August 1, 2002
Download Office08012002.exe.
XML Web services! XML Web services! XML Web services are everywhere! Of course, you already know that. But you can't start an article that talks about XML Web services without joining the XML Web services parade. Personally, I find XML Web services to be extremely useful.
What has impressed me the most? Three main things stand out:
- How easy an XML Web service is to create.
- How quickly my customers and third parties are exposing useful information with XML Web services.
- How easy XML Web services are to consume with Microsoft® Office.
However, I have found a reoccurring need that hasn't been addressed yet for those building solutions that integrate Microsoft Office and XML Web services. I have found on a number of occasions that I need to be able to consume ADO.NET datasets that have been serialized as XML and transmitted through an XML Web Service into solutions that I am building with Microsoft Office. And I am not alone in this; I have seen many requests in the newsgroups for an easy way to accomplish this task. Because Microsoft .NET is so new, the current versions of Microsoft Office don't provide an out-of-the-box solution to consuming ADO.NET data. This doesn't mean it's not possible though.
In this article, I will address the need to consume ADO.NET datasets exposed through XML Web services in a reusable way into Microsoft Office. This solution presented here works with Microsoft Office XP and Microsoft Office 2000. The Microsoft Visual Basic® for Applications (VBA) source code also requires the Microsoft SOAP Toolkit 3.0 and the Microsoft XML (MSXML) Core Services 4.0 library available at the MSDN SOAP Developer Center.
First, we need to review the structure of a dataset that has been serialized as XML. Second, with knowledge of this XML structure, we will look at two approaches to using the data in Microsoft Office.
Getting the Dataset
First, let's consider the first point from my opening comments: the ease of creating an XML Web service. In Microsoft Visual Studio® .NET, I wrote a simple function that returned an ADO.NET dataset. In less than a second, I converted that same code into an XML Web service by simply adding the <WebMethod()> attribute:
Private sConnNwind As String = "Data Source=localhost;" & _
"Initial Catalog=northwind;" & _
"User ID={UserID};" & _
"password={Password}"
<WebMethod()> Public Function RunQuery() As DataSet
Dim cn As SqlConnection = New SqlConnection(sConnNwind)
Dim cmd As SqlCommand = New SqlCommand("SELECT * from Orders", cn)
Dim da As SqlDataAdapter = New SqlDataAdapter()
da.SelectCommand = cmd
cn.Open()
Dim dsAuthors As DataSet = New DataSet()
da.Fill(dsAuthors)
cn.Close()
Return dsAuthors
End Function
Visual Studio .NET makes this all so easy! I can't imagine having to write the code to convert this into an XML document, and then into a SOAP message for transmission as an XML Web service. Even so, I don't want to spend much time on the Visual Basic .NET code; these concepts are well covered elsewhere here on MSDN. There are two key points to this snippet of code that I want to draw your attention to:
- The function runs a query from the Northwind database and returns a dataset.
- The
<WebMethod()>attribute marks this function to be visible as an XML Web service.
As a quick reminder, a dataset is similar to an ADO Recordset object, but is much more powerful. It has the ability to store multiple results from queries in one DataSet object, along with their relationships, and most importantly, to serialize the data it contains into an XML stream.
So in review, when the RunQuery function is called, ADO.NET connects to the Northwind Microsoft SQL Server™ database and retrieves the results of the query into a DataSet object. The dataset is then returned from the function. .NET is able to serialize the dataset and the XML stream is sent back to the caller. The serialized dataset will look something like this:
<?xml version="1.0" encoding="utf-8"?>
<DataSet xmlns="http://tempuri.org/WS_XML">
<xs:schema id="NewDataSet"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="NewDataSet" msdata:IsDataSet="true">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="Table">
<xs:complexType>
<xs:sequence>
<xs:element name="OrderID" type="xs:int" minOccurs="0" />
<xs:element name="CustomerID"
type="xs:string" minOccurs="0" />
<xs:element name="EmployeeID"
type="xs:int" minOccurs="0" />
<xs:element name="OrderDate"
type="xs:dateTime" minOccurs="0" />
<xs:element name="RequiredDate"
type="xs:dateTime" minOccurs="0" />
<xs:element name="ShippedDate"
type="xs:dateTime" minOccurs="0" />
<xs:element name="ShipVia" type="xs:int" minOccurs="0" />
<xs:element name="Freight"
type="xs:decimal" minOccurs="0" />
<xs:element name="ShipName"
type="xs:string" minOccurs="0" />
<xs:element name="ShipAddress"
type="xs:string" minOccurs="0" />
<xs:element name="ShipCity"
type="xs:string" minOccurs="0" />
<xs:element name="ShipRegion"
type="xs:string" minOccurs="0" />
<xs:element name="ShipPostalCode"
type="xs:string" minOccurs="0" />
<xs:element name="ShipCountry"
type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
<NewDataSet >
<Table diffgr:id="Table1" msdata:rowOrder="0">
<OrderID>10248</OrderID>
<CustomerID>VINET</CustomerID>
<EmployeeID>5</EmployeeID>
<OrderDate>1996-07-04T00:00:00.0000000-05:00</OrderDate>
<RequiredDate>1996-08-01T00:00:00.0000000-
05:00</RequiredDate>
<ShippedDate>1996-07-16T00:00:00.0000000-
05:00</ShippedDate>
<ShipVia>3</ShipVia>
<Freight>32.38</Freight>
<ShipName>Vins et alcools Chevalier</ShipName>
<ShipAddress>59 rue de l'Abbaye</ShipAddress>
<ShipCity>Reims</ShipCity>
<ShipPostalCode>51100</ShipPostalCode>
<ShipCountry>France</ShipCountry>
</Table>
<Table diffgr:id="Table2" msdata:rowOrder="1">
<OrderID>10249</OrderID>
<CustomerID>TOMSP</CustomerID>
<EmployeeID>6</EmployeeID>
<OrderDate>1996-07-05T00:00:00.0000000-05:00</OrderDate>
<RequiredDate>1996-08-16T00:00:00.0000000-
05:00</RequiredDate>
<ShippedDate>1996-07-10T00:00:00.0000000-
05:00</ShippedDate>
<ShipVia>1</ShipVia>
<Freight>11.61</Freight>
<ShipName>Toms Spezialitäten</ShipName>
<ShipAddress>Luisenstr. 48</ShipAddress>
<ShipCity>Münster</ShipCity>
<ShipPostalCode>44087</ShipPostalCode>
<ShipCountry>Germany</ShipCountry>
</Table>
</NewDataSet>
</diffgr:diffgram>
</DataSet>
This XML serialized dataset has two primary sections:
- The XML Schema (often abbreviated as XSD) section, which contains information about the structure of the data the XML document contains. This schema information is based on the W3C XML Schema standard.
- The second section actually contains the data from the query. As you can see, the XML serialized dataset as represented in this document is a self-contained, portable representation of data from our SQL Server database. It contains the table structure, data types, and the data within elements.
Is this information useful to a Microsoft Office User? You bet it is. Microsoft Office is all about making data and information useful, relevant, and actionable. As XML Web services continue to proliferate on the Internet and internal enterprise networks, users of Microsoft Office will want to leverage the information they expose. One of the things that I truly appreciate about users of Microsoft Office is their ability to take raw data and to crunch it, squeeze it, bend it, or do whatever they need to make it relevant to getting their job done. Microsoft Office provides them with the tools they need to do this and most importantly, I don't have to write more code to solve problems that they are better suited to address.
OK, a more honest answer is that I'm lazy at heart. I like to provide them with the data in a Microsoft Office friendly way and then let the user deal with it.
Armed with this information, is it possible to reconstruct this data in a useful way on the desktop within Microsoft Office? Yes it is. In the remainder of this article I will show how you can use a built-in approach of Microsoft Excel to consume an XML serialized dataset and a custom solution using VBA code to do this from any Office application, giving you complete flexibility.
Don't Reinvent the Wheel
At the beginning of this article, I stated that Microsoft Office does not provide an out-of-the-box solution for consuming XML serialized datasets. However, Microsoft Excel does provide a feature that comes very close. One of the things that you learn as you gain experience in building Microsoft Office solutions is to not reinvent the wheel. As programmers, we have a tendency to want to write code for everything we do. When working with Microsoft Office it is important to learn to ask yourself a simple question, "Is there a built-in feature that I can leverage to get the job done quicker without having to write code?" In the case of the problem we're trying to solve, Microsoft Excel has a feature we can utilize called the Web query. Web queries have their limitations, but can be useful in many situations when dealing with XML Web services, and in my opinion are an often overlooked gem of functionality. Let's quickly review the strengths and weaknesses of the Microsoft Excel Web queries feature.
For the remainder of the article, we will utilize a publicly available Web service available at http://services.fabrikam.com/OWSISample/Order.asmx/OpenOrders. Take a minute to run this XML Web service from your browser. It returns a query of currently open orders for Fabrikam.com (Fabrikam.com is a sample application built on top of Microsoft Office; for more information visit the Fabrikam.com Web site). The XML you see in the browser is a serialized dataset and looks very similar to the prior dataset from the SQL Server Northwind database.
Let's utilize this Web Service in a Microsoft Excel Web query. To do this, follow these steps:
Start Microsoft Excel.
On the Data menu, point to Import External Data and click New Web Query.
The New Web Query dialog box appears.
In the Address list, type http://services.fabrikam.com/OWSISample/Order.asmx/OpenOrders, and click Go. Figure 1 illustrates what you should see.
.gif)
Figure 1. New Web query dialog box previewing the Fabrikam OpenOrders XML Web service.
Click Import.
Next you are asked about where to insert the Web query data. Select any cell on a blank sheet and then click OK.
The Web query runs and insert the results as shown in Figure 2.
.gif){kind=link}
Figure 2. Web query results inserted into a worksheet.
As you can see, with little effort we have brought the results of an XML Web service into Excel without any code. I have used this on a number of occasions and have found it to be a fairly reliable technique. The Web query technology in Excel is amazingly flexible; refer to Figure 3 to see the configuration options available to you. My favorite feature is its ability to update cells based on certain parameters. For example, you can have the Web query refresh itself every so many minutes that you specify or when the workbook is first opened. This assures that you always have the most current information.
.gif)
Figure 3. Web Query Properties dialog box.
There are a few disadvantages though. First, you may notice that the column headings in Excel are rather cryptic. Excel has a built-in XML converter designed to take arbitrary XML and transform it into a spreadsheet. The approach Excel uses is very generic because it doesn't have any clue about structure and the use of XML it is loading. In our case, the XML that we are working with is not arbitrary; its format or schema is well-known. This means that when we use a Web query we are stuck with a generic layout, no formatting, and other elements from the XML that are of no use to us in Excel.
Additionally, Excel currently does not know how to handle XSD data types. Therefore, when we run a Web query, various data types may not be converted or handled in the way we expect. For example, looking at Figure 2, notice the date column has dates that look like 2002-07-19T10:53:36.000000004:00. XSD represents dates in the ISO 8601 date format. As you can see, Excel doesn't recognize this date format and treats it as if it were a string.
Finally, the most significant disadvantage to the Web query with XML flattening capability is that it is only available in Microsoft Excel 2002. Therefore, it would be very useful to have a technique that overcomes these limitations and works in any Microsoft Office application. Before I show you how to do this with VBA code, let me encourage you, if you have not already done so, to spend a little time getting more acquainted with Web queries. They are truly helpful in working with XML Web Services without writing code. For more information on Web queries, see Charles Maxson's article Well-kept Secret: Excel's Web Queries Enable You to Populate Worksheets from Web Sites.
Roll Your Own Solution
So that it is clear, let's restate the problem—we need the ability to consume ADO.NET datasets from an XML Web service that have been serialized as XML into any Microsoft Office application. We want the data to be made available to us in a way that is easy to deal with, and we also want the source code so that we have full control over the process.
The following solution will work in any VBA enabled application, and will also work from Microsoft Visual Basic 6.0. To use this solution, we will implement the following steps:
- Retrieve the XML from an XML Web service.
- Parse the XML into a VBA Array object.
- Insert the data into an Office application.
Step 1: Retrieve the XML
First, we need to retrieve the ADO.NET dataset serialized as XML from an XML Web service. This is the easiest step in our solution. From the VBA integrated development environment (IDE) in one of the Microsoft Office applications (this could be Word, Excel, Microsoft PowerPoint®, and so on), we need to set a reference to the following two libraries:
- Microsoft Soap Type Library v3.0
- Microsoft XML, v4.0
If you do not have these libraries, you can download them for free at the MSDN SOAP Developer Center. The following code runs the XML Web service and retrieves the results into an MSXML IXMLDOMNodeList object:
Dim sc As MSSOAPLib30.SoapClient30
Dim xdl As MSXML2.IXMLDOMNodeList
Set sc = New MSSOAPLib30.SoapClient30
sc.MSSoapInit "http://services.fabrikam.com/OWSISample/Order.asmx?wsdl"
Set xdl = sc.OpenOrders
To start, we create a SoapClient30 object. The SoapClient30 object is a utility object included in the Microsoft SOAP Toolkit 3.0 . It encapsulates all the necessary logic and plumbing for connecting to a remote XML Web service, executing a Web method, and returning the XML message to the client. To do this, a reference to the XML Web service's Web Service Description Language (WSDL) file must be passed into the MSSoapInit property. This allows the SoapClient30 object to know what services and methods are available. Once the SoapClient30 object is initialized, we can call into any methods exposed by the XML Web service:
Set xdl = sc.OpenOrders
In our case, we are calling the OpenOrders method, which returns the XML that we viewed in the previous section of this article. The Microsoft SOAP Toolkit 3.0 makes this easy; it takes just four lines of code. Take note that the response from the XML Web service comes back as an IXMLDOMNodeList object. An IXMLDOMNodeList object is an object in the MSXML 4 library and contains fragments of XML and are represented as IXMLDOMNode objects. This is where things get interesting. The IXMLDOMNodeList object contains two nodes of XML fragments. The first node contains the XML Schema (which defines data structure and data types), and the second node contains our actual data. This is convenient for us, now we have just two node objects that we can parse for the structure of the XML and then parse out the data from the second node.
Step 2: Parse the XML into a VBA Array Object
Before I show you how to parse the XML returned from our XML Web service into an array, I need to explain why I parse the data into an array and not some other in-memory representation like an ADO Recordset object or a VBA typed structure. When I first started out, I intended to support other in-memory representations. First, I started with arrays because they are the common denominator across VBA enabled applications. They're simple, fast, and have no external dependencies like ADO. I reasoned that after I had the array logic working, I could easily modify the code to meet further needs. But the simple truth is that the array proved to be very flexible and thus far has met all my needs. You will clearly see though that the code can be modified to create an ADO recordset or something else easily.
The following code is used to parse the XML into an array:
Public Function ParseDataSet(ByRef xdlXSDFromSoapClient As IXMLDOMNodeList, _
ByVal sTableName As String, _
ByVal bReturnFieldHeaders As Boolean) As Variant
Dim xdd As MSXML2.DOMDocument40
Dim xdlStructure As MSXML2.IXMLDOMNodeList
Dim xdlRows As MSXML2.IXMLDOMNodeList
Dim lcntRows As Long
Dim lctrRow As Long
Dim lcntFields As Long
Dim saFieldDefinitions() As String 'Array to store field name and _
'field data type.
Dim lctrFieldDef As Long
Dim iRowHeader As Long
Dim vArray() As Variant 'Array of table data returned by the function.
Dim iCntUbound As Integer
Dim iCtr As Integer
Dim sNodeName As String
Dim xdlFields As IXMLDOMNodeList
Dim xdnField As IXMLDOMNode
Dim iNode As Integer
Dim sDataSetNameSpace As String
Set xdd = New MSXML2.DOMDocument40
With xdd
'Load field definition XML.
.async = False
.preserveWhiteSpace = True
.setProperty "SelectionNamespaces", _
"xmlns:xs='http://www.w3.org/2001/XMLSchema'"
.loadXML xdlXSDFromSoapClient.Item(0).XML
Set xdlStructure = xdd.selectNodes("//xs:element[@name='" & _
sTableName & _
"']/xs:complexType/xs:sequence/xs:element")
'Populate field definition array.
lcntFields = xdlStructure.Length - 1 'Zero-based.
If lcntFields = -1 Then
MsgBox "The specified table does not exist."
Exit Function
End If
End With
ReDim saFieldDefinitions(lcntFields, 1) As String
For lctrFieldDef = 0 To lcntFields
With xdlStructure.Item(lctrFieldDef).Attributes
saFieldDefinitions(lctrFieldDef, 0) = .getNamedItem("name").Text
saFieldDefinitions(lctrFieldDef, 1) = .getNamedItem("type").Text
End With
Next
'Start build array.
With xdlXSDFromSoapClient.Item(1).FirstChild.Attributes
sDataSetNameSpace = .getNamedItem("xmlns").Text
End With
Set xdd = New MSXML2.DOMDocument40
With xdd
.async = False
.preserveWhiteSpace = True
.loadXML xdlXSDFromSoapClient.Item(1).XML
If sDataSetNameSpace = "" Then
Set xdlRows = xdd.selectNodes("//" & sTableName)
Else
.setProperty "SelectionNamespaces", "xmlns:df='" & _
sDataSetNameSpace & "'"
Set xdlRows = xdd.selectNodes("//df:" & sTableName)
End If
End With
lcntRows = xdlRows.Length - 1 '0 Indexed
If lcntRows = -1 Then
MsgBox "XSD Web Service returned no records", vbCritical, "No Data"
Exit Function
End If
'Add row headers (optional).
If bReturnFieldHeaders = True Then
'Add header row.
ReDim vArray(lcntRows + 1, lcntFields) As Variant
iRowHeader = 1 'Add row header.
For lctrFieldDef = 0 To lcntFields
vArray(0, lctrFieldDef) = _
Replace(saFieldDefinitions(lctrFieldDef, 0), _
"_x0020_", " ")
Next
Else
'No header row.
ReDim vArray(lcntRows, lcntFields) As Variant
iRowHeader = 0 'No Row Header
End If
'Add row data to array.
iCntUbound = UBound(saFieldDefinitions)
For lctrRow = 0 To lcntRows
Set xdlFields = xdlRows.Item(lctrRow).childNodes
For Each xdnField In xdlFields
'Loop for index into field name and data type.
sNodeName = xdnField.nodeName
For iCtr = 0 To iCntUbound
If saFieldDefinitions(iCtr, 0) = sNodeName Then
iNode = iCtr
Exit For
End If
Next
Select Case saFieldDefinitions(iNode, 1)
Case "xs:int"
vArray(lctrRow + iRowHeader, iNode) = CLng(xdnField.Text)
Case "xs:integer"
vArray(lctrRow + iRowHeader, iNode) = CVar(xdnField.Text)
Case "xs:long"
vArray(lctrRow + iRowHeader, iNode) = CVar(xdnField.Text)
Case "xs:date"
vArray(lctrRow + iRowHeader, iNode) = _
ConvertISO8601DateFormatToVBDateTime(xdnField.Text)
Case "xs:dateTime"
vArray(lctrRow + iRowHeader, iNode) = _
ConvertISO8601DateFormatToVBDateTime(xdnField.Text)
Case "xs:double"
vArray(lctrRow + iRowHeader, iNode) = CDbl(xdnField.Text)
Case "xs:short"
vArray(lctrRow + iRowHeader, iNode) = CInt(xdnField.Text)
Case "xs:float"
vArray(lctrRow + iRowHeader, iNode) = CSng(xdnField.Text)
Case "xs:boolean"
vArray(lctrRow + iRowHeader, iNode) = CBool(xdnField.Text)
Case "xs:byte"
vArray(lctrRow + iRowHeader, iNode) = CInt(xdnField.Text)
Case "xs:time"
vArray(lctrRow + iRowHeader, iNode) = _
ConvertISO8601DateFormatToVBDateTime(xdnField.Text)
Case Else
vArray(lctrRow + iRowHeader, iNode) = xdnField.Text
End Select
Next
Next
ParseDataSet = vArray
End Function
The ParseDataSet function requires three parameters:
| Parameter | Description |
|---|---|
| xdlXSDFromSoapClient (IXMLDOMNodeList object) | The IXMLDOMNodeList object returned from the SoapClient30 object as demonstrated in the previous section of this article. |
| sTableName (String value) | The unique table name as used in the XML returned from the XML Web service.
Note This parameter, like XML, is case-sensitive. |
| bReturnFieldHeaders (Boolean value) | True if the field names are to be returned as the first row in the array. |
This routine is rather long, but there is a reason for this. Originally I had this function split into three separate functions. I found though, that because of the many loops required in walking through the XML document object model (DOM), that performance is impacted as calls are made outside of the function. For this reason, it makes more sense to have all the code inline in one function. This improved performance about three times over having separate functions.
As you review the code for the ParseDataSet function, you will notice that it is familiar if you have parsed XML before with the MSXML parser. The first part of the routine walks through the XML Schema to obtain the ADO.NET dataset field names and data types. This information is important if the program has selected to have field names come back in the first row of the array and is also used to convert the XML string data to its appropriate VBA data type.
The second part of the code actually walks the XML DOM, record by record, extracting the data into an array. In the final part of the code you will notice a large Select Case statement. This Select Case statement examines the field's data type from the XML Schema and performs data coercion from its XML string type data into the appropriate VBA data type. If we did not do this, all integers in our XML would be represented in our array as strings; we want the data to be real integers. This is true for all other data types.
The Select Case statement also makes a call out to the ConvertISO8601DateFormatToVBDateTime function. This is the one exception where the ParseDataSet function will call out to an external routine. The logic for converting the ISO 8601 date format is rather convoluted and it made sense to separate it out. If the XML has date fields, this routine does add a minor impact to processing time, but for the most part it is negligible. The following is the ConvertISO8601DateFormatToVBDateTime function:
Public Function ConvertISO8601DateFormatToVBDateTime(ByRef vData As Variant) As Variant
Dim lMonthSep As Long 'Month separator position.
Dim lDaySep As Long 'Day separator position.
Dim lMinutesSep As Long 'Minutes separator position.
Dim sDt As String
Dim sTm As String
lMonthSep = InStr(1, vData, "-", vbBinaryCompare)
lDaySep = InStr(lMonthSep + 1, vData, "-", vbBinaryCompare)
If lMonthSep > 0 Then
sDt = Mid(vData, lMonthSep + 1, 2) & "/" & _
Mid(vData, lDaySep + 1, 2) & "/" & _
Left(vData, lMonthSep - 1)
End If
'Extract time.
lMinutesSep = InStr(1, vData, ":", vbBinaryCompare)
If lMinutesSep > 0 Then
sTm = Mid(vData, lMinutesSep - 2, 8)
End If
ConvertISO8601DateFormatToVBDateTime = CDate(sDt & " " & sTm)
End Function
The ConvertISO8601DateFormatToVBDateTime function breaks down the ISO 8601 date format into its constituent parts and then reconstructs them into a proper VBA date. As an example, our prior date of 2002-07-19T10:53:36.000000004:00 would look like 07/19/2002 10:53 AM after conversion.
For the most part, the parsing of the XML into an array is the trickiest part of this solution. The ParseDataSet function takes care of the guesswork of parsing ADO.NET data that has been serialized as XML into an array. How do we use the function? Let's extend the code we used to call the XML Web service to now parse the data:
Dim sc As MSSOAPLib30.SoapClient30
Dim xdl As MSXML2.IXMLDOMNodeList
Dim vDataSet As Variant
Set sc = New MSSOAPLib30.SoapClient30
sc.MSSoapInit "http://services.fabrikam.com/OWSISample/Order.asmx?wsdl"
Set xdl = sc.OpenOrders
vDataSet = ParseDataSet(xdl, "Table", True)
We have added just two lines of code:
Dim vDataSet As Variant
vDataSet = ParseDataSet(xdl, "Table", True)
First we create a variable of type Variant. We then call the ParseDataSet function passing in the IXMLDOMNodeList object, the name of the table we want returned from the dataset, and True to indicate that field header names should be put into the first line of the array. You may be wondering right now: doesn't the ParseDataSet function return an array? Why are we assigning the ParseDataSet function's return value to a Variant? The poor little Variant data type sure gets knocked around a lot, but it is a useful data type. The approach we are using here is called a Variant array. You can actually create an array and assign it to a Variant variable. This allows us to have a variable that can hold the array, without knowing the dimensions the array will need to be sized too, as this information isn't known until run-time when the actual dataset is returned. A Variant array can be used like any other array. You can retrieve elements from the array using vDataSet(0,0) notation, and any other array aware functions like UPPER and LOWER.
Equipped with our reusable routines ParseDataSet and ConvertISO8601DateFormatToVBDateTime, we can now parse the results of an XML Web service that returns a XML serialized dataset with just a few lines of code. I have bundled these two functions into a module named basWebServices.bas that you can import into your VBA project. The basWebServices.bas module is included with the article download.
To conclude this step, let me just mention a few things about these routines. As you might imagine, they are not perfect. I have tested the code in a number of implementations, but have found that .NET is very flexible in how datasets can be serialized to XML. I have tested this code with XML serialized datasets that contain single tables and multiple tables. I do know that this code currently does not support nested tables, as I have not found a situation where I have needed that in a Microsoft Office solution. Therefore, you will need to modify the source code in cases where the .NET developer has deviated from the default mechanisms for returning a dataset from an XML Web service or complex table hierarchies are in the dataset. But that is the benefit of having the source code—you have that flexibility.
Step 3: Do Something with the Array
I mentioned that step 1 was easy; step 3 is even easier. Now that we have the ADO.NET dataset into an array, we can do just about anything with it in Office. The following code is an extension to our ongoing sample. It retrieves the XML from the XML Web service, parses it, and then inserts it into the current spreadsheet in Excel:
Dim rngData As Range
Dim sc As MSSOAPLib30.SoapClient30
Dim xdl As MSXML2.IXMLDOMNodeList
Dim vDataSet As Variant
'Run XML Web service and parse response into an array.
Set sc = New MSSOAPLib30.SoapClient30
sc.MSSoapInit "http://services.fabrikam.com/OWSISample/Order.asmx?wsdl"
Set xdl = sc.OpenOrders
vDataSet = ParseDataSet(xdl, "Table", True)
'Insert array as formatted grid.
Set rngData = ActiveCell.Resize(UBound(vDataSet, 1) + 1, UBound(vDataSet, 2) + 1)
rngData = vDataSet
ActiveCell.AutoFilter
Selection.AutoFormat Format:=xlRangeAutoFormatList2, Alignment:=True
This is one of the things I love about Excel. You can assign a Range object that has been assigned to the same dimensions of the array right into the spreadsheet. Notice the line rngData = vDataSet. It's that easy! To polish it off, I do a few things to make it look better utilizing the AutoFilter feature so the user can drill down into the data and apply an AutoFormat for presentation. Figure 4 shows the results. Compare this with what the Web query did in Figure 2.
.gif){kind=link}
Figure 4. XML Web service data inserted into Excel from custom VBA code.
To do the same thing in Word requires a little more work. Unfortunately, Word does not have the provision of just inserting an array into a document. At the same time, walking an array is rudimentary task. The following code accomplishes the same task as performed in Excel, but in Word:
Private Sub InsertArrayAsWordTable()
Dim sc As MSSOAPLib30.SoapClient30
Dim xdl As MSXML2.IXMLDOMNodeList
Dim vDataSet As Variant
Dim nRows As Integer
Dim nRowCount As Integer
Dim nColumns As Integer
Dim nColumnCount As Integer
Dim sRow As String
'Run XML Web service and parse response into an array.
Set sc = New MSSOAPLib30.SoapClient30
sc.MSSoapInit "http://services.fabrikam.com/OWSISample/Order.asmx?wsdl"
Set xdl = sc.OpenOrders
vDataSet = ParseDataSet(xdl, "Table", True)
'Insert array as formatted table.
nRows = UBound(vDataSet, 1)
nColumns = UBound(vDataSet, 2)
Selection.Collapse wdCollapseStart
Options.Pagination = False
For nRowCount = 1 To nRows
For nColumnCount = 1 To nColumns
sRow = sRow & vDataSet(nRowCount - 1, nColumnCount - 1)
If nColumns <> nColumnCount Then
sRow = sRow & vbTab
End If
Next
sRow = sRow & vbCrLf
Next
With Selection
.InsertAfter sRow
.ConvertToTable Separator:=vbTab
With .Tables(1)
.AutoFitBehavior (wdAutoFitContent)
.AutoFormat Format:=wdTableFormatList2
End With
.Collapse wdCollapseStart
End With
Options.Pagination = True
End Sub
The InsertArrayAsWordTable function retrieves the XML Web service data, parses it, and then loops through the array to insert the text into Word as a tab delimited string. Finally, using the ConvertToTable method, the string is converted to a table. As with Excel, we apply a little formatting to improve its presentation. Figure 5 shows the results.
.gif){kind=link}
Figure 5. XML Web service data inserted into Word from custom VBA code.
Conclusion
The solution I have presented is simple, yet effective in providing a pleasing presentation of the ADO.NET dataset into Microsoft Office. From here, the user is able to take ownership in how the data is used.
One final thought...be mindful not to return too much data to the client. We have found that in general, very few XML Web services return more than a few hundred records, which are parsed and inserted into Microsoft Office quickly. However, when thousands of records are returned, performance can be affected. You no doubt would anticipate this to be the case when transmitting large chunks of data across the wire. The area that is easy to overlook is the processing time Microsoft Office will need in dealing with your data and also the practical limitations of Microsoft Office documents. For example, Microsoft Excel has a capacity of 65,000 rows per worksheet. We have actually seen cases where customers wanted to return more than this into a worksheet. This reminded us of the famous quote that said we would never need more than 640 KB of RAM. Who ever thought we could potentially need more than 65,000 rows in a worksheet? I surely didn't!
Chris Kunicki works with customers, architects, and engineers to build cool desktop, enterprise and Web applications at OfficeZealot.com. Chris is a long time enthusiast of Office development and has been evangelizing Office as an important platform for building solutions by writing and speaking to users and developers. You can reach him at chris@officezealot.com. Check out his slant on things at http://www.officezealot.com.