Building an XML Data Class

Now that we've got an idea of what XML data services Excel provides us, let's build a class that will give us an easy-to-use interface to this functionality. Before we begin, though, let's think about what we'd like our class to do:

• We want it to bring in data from various XML data sources.

• We want the ability to refresh the data in case the source file is updated (either manually or through an automated process).

• We want to append data from another file that conforms to the same XML schema.

• We want to be able to clear and reset the data bindings.

• We want to be able to persist the data back to the original file or to a new file.

Those are some basic functions we'd expect from this object, but what else might a class of this type do for us?

• Check for the existence of XML maps?

Open a new workbook and add a new class module in the VBE. Name the class cXML. Before we do any importing of XML data, it's a good idea to see if a map exists for our data. Add a new public property and call it HasMaps. Normally, I would add a module-level variable to hold the contents of this property, but the HasMaps property will be read-only. Creating a module-level variable presents us with the option of accessing that variable directly in our code. I'm suggesting leaving it out to prevent that possibility. Add the following code to the HasMaps property:

Public Property Get HasMaps() As Boolean Dim blnReturn As Boolean blnReturn = ActiveWorkbook.XmlMaps.Count >= 1 HasMaps = blnReturn End Property

My preference is to keep my code as concise as possible without sacrificing readability. I'm using one line of code in place of an If...Else block. The long form, if you prefer it, looks like this.

Dim blnReturn As Boolean

If ActiveWorkbook.XmlMaps.Count >= 1 Then blnReturn = True Else blnReturn = False End If

HasMaps = blnReturn

Add a property to store the name of the XML file to import plus a few additional setup properties:

Public Property Get XMLSourceFile() As String

XMLSourceFile = m_sXMLSourceFile End Property

Public Property Let XMLSourceFile(newXMLSourceFile As String)

m_sXMLSourceFile = newXMLSourceFile End Property

Public Property Get DataRange() As Excel.Range

Set DataRange = m_oRange End Property

Public Property Set DataRange(newRange As Excel.Range)

Set m_oRange = newRange End Property

Property Get Overwrite() As Boolean

Overwrite = m_blnOverwrite End Property

Property Let Overwrite(newOverwrite As Boolean)

m_blnOverwrite = newOverwrite End Property

Public Property Get MapName() As String

MapName = m_sMapName End Property

We've added a property to store and retrieve the DataRange into which we'll put our data (remember, it's the top-left cell reference in the range). We've also added an overwrite flag and a read-only property to retrieve the name of the XML map for this object. The module-level declarations section should now look like this:

Dim m_sXMLSourceFile As String Dim m_blnOverwrite As Boolean Dim m_oRange As Excel.Range Dim m_sMapName As String

Now let's start getting some work done. We are going to build, import, append, refresh, and save functions for our XML data. The first step is to get data from the XML file into the worksheet. We have a few scenarios in which to put data on our worksheet:

• Bringing in data from an XML file

• Overwriting existing XML data with a file that shares the same schema

• Appending XML data to our existing XML data

We'll start out by building a function that gives a developer who might be using this class in a project the ability to bring in new data (thereby creating an XML map) and a function to add additional data to previously imported XML data (or overwrite it if desired). These will be declared as private functions within the class. We will create a wrapper method to let our code make the decision as to which process we are calling. We'll also add a method to provide a direct call to an append function.

In the cXML class module, add a private method named GetNewXMLData. The code for this method will look very familiar:

Private Function GetNewXMLData() ActiveWorkbook.XmlImport m_sXMLSourceFile, Nothing, m_blnOverwrite, m_oRange m_sMapName = ActiveWorkbook.XmlMaps(ActiveWorkbook.XmlMaps.Count).Name End Function

We are making the same call to the XmlImport method of the ActiveWorkbook object as we did in the examples that we created in standard code modules—but rather than directly setting its arguments, we are referring to the internal variables of our cXML class.

The GetNewXMLData method is actually doing two jobs for us. The first, of course, is getting the data into our worksheet. Remember that a call to the XmlImport method brings in data and creates an XML map. The second line of code in this method is setting our class's MapName property for us:

m_sMapName = ActiveWorkbook.XmlMaps(ActiveWorkbook.XmlMaps.Count).Name

This will come in handy when we need to add data or overwrite the current set of data. By checking the XmlMaps.Count property, we can get the latest addition to the collection that was added by the XmlImport method.

Now we'll add a second private function that will append or overwrite data for an existing XML map. Add a new private function and name it GetXMLForExistingMap. Add the following code:

Private Function GetXMLForExistingMap(DoOverwrite As Boolean)

ActiveWorkbook.XmlMaps(m_sMapName).Import m_sXMLSourceFile, DoOverwrite End Function

This function takes one argument, which is used to flag whether we want to append or overwrite our existing data. The single line of code should again be familiar. We are using the XmlMaps collection's Import method to get our data. Notice that we're using the internal m_sMapName variable to determine which XML map the data corresponds to.

Now let's add that wrapper method and let our class decide how to handle the data retrieval. Add a public function to the cXML class and name it GetXMLData. Add the following code:

Public Function GetXMLData(Optional DoOverwrite As Boolean = True) If (m_sMapName = "") Or (Not Me.HasMaps) Then

GetNewXMLData Else

'must set XMLSourceFile Property before appending if necessary

GetXMLForExistingMap DoOverwrite End If End Function

The GetXMLData method has one optional argument, which is used to set an overwrite flag for the incoming data. This argument has a default value of True, remaining consistent with Excel's built-in object interfaces.

The code begins with branching logic to determine whether our object already contains a reference to an XML map or whether the workbook does not contain any XML maps.

Once that's determined, the code will either bring in new data and create an XML map via the private GetNewXMLData function or it will call GetXMLForExistingMap, where, depending on how the overwrite flag is set, it will either append or overwrite the data in the existing mapping. The second branch also contains a comment noting that the XMLSourceFile property should be set before calling this function to perform an append.

Before we create client code to test this, remember that the original GetXMLData function that we wrote in our standard module in a previous example generated an error if it was run two times in a row (against a data table that was already mapped). We are going correct that error in our cXML class. Our private GetNewXMLData method is almost identical to that original code. This was done this way to show the relationship of the standard code to the class code.

We can use the XPath property of the Range object to determine if our destination cell (which is set when we instantiate our cXML object) already belongs to a mapping. If it belongs to a mapping, we'll perform a data refresh using the XmlMaps collection; if not, we'll import the data and create a new XML map. We are also going to validate the import using the XlXmlImportResult data type as the return value of our XmlImport call.

The first thing we need to do is return the name of the destination range's map if it has one. Add the following private function to the cXML class:

Private Function CurrentMapName() As String Dim strReturn As String On Error GoTo Err_Handle If Me.HasMaps Then strReturn = Else strReturn = "" End If

Exit_Function: CurrentMapName = strReturn Exit Function Err_Handle:

'not in a cell in the mapped table - treat as new mapping strReturn = "" Resume Exit_Function End Function

This code first checks to see if the workbook has any XML maps. If it does, it returns the map name for the class's destination range. If not, it returns an empty string. The error handler is there in case the destination range is set to a location outside an XML-mapped area. It returns an empty string in that case, and treats it like a new mapping.

Now we'll modify the private method, GetNewXMLData, to use CurrentMapName and to give us a return value on our import. And while we're at it, we'll add a result output to GetXMLForExistingMap, and finally, the GetXMLData method will respond to those results with a message to the user. The finished code for all three methods looks like this:

Private Function GetNewXMLData() As XlXmlImportResult

Dim sCurrMap As String

Dim result As XlXmlImportResult

'check to see if data range is already bound to a map sCurrMap = CurrentMapName If sCurrMap = "" Then result = ActiveWorkbook.XmlImport(m_sXMLSourceFile, Nothing, m_blnOverwrite, m_oRange) m_sMapName = ActiveWorkbook.XmlMaps(ActiveWorkbook.XmlMaps.Count).Name Else m_sMapName = sCurrMap

ActiveWorkbook.XmlMaps(m_sMapName).DataBinding.Refresh result = xlXmlImportSuccess End If

GetNewXMLData = result End Function

Private Function GetXMLForExistingMap(DoOverwrite As Boolean) As XlXmlImportResult 'calling this function to append data requires setting the XMLSourceFile Property Dim result As XlXmlImportResult result = ActiveWorkbook.XmlMaps(m_sMapName).Import(m_sXMLSourceFile, DoOverwrite)

GetXMLForExistingMap = result End Function

Public Function GetXMLData(Optional DoOverwrite As Boolean = True) Dim result As XlXmlImportResult

If (m_sMapName = "") Or (Not Me.HasMaps) Then result = GetNewXMLData Else

'must set XMLSourceFile Property before appending if necessary result = GetXMLForExistingMap(DoOverwrite) End If

Select Case result Case xlXmlImportSuccess

MsgBox "XML data import complete" Case xlXmlImportValidationFailed

MsgBox "Invalid document could not be processed" Case xlXmlImportElementsTruncated

MsgBox "Data too large. Some data was truncated" End Select End Function

Let's create some client code to test our cXML class out as we build it.

Add a standard module to the workbook, and declare a module-level variable: Dim oEmpDept As cXML

Add a new procedure and name it GetEmpDept. Add the following code:

Note Be sure to change the file path to where you have the XML file stored.

Public Sub GetEmpDept() Set oEmpDept = New cXML

oEmpDept.XMLSourceFile =

"C:\Chapter 3\EmpDept.xml" Set oEmpDept.DataRange = Sheets(1).Range("A1") oEmpDept.GetXMLData End Sub

Before running the client code, save your work. We are going to import some test data, and then we'll close the workbook without saving to remove the data and XML maps between each test. This is an easy way to reset the project without deleting worksheets and XML maps between tests.

Our data file contains a listing of Adventure Works employees and their department and job information. Since the object is just being instantiated here and its MapName property is empty, the code will fall into the first branch of the If statement and call the GetNewXMLData method.

Run the GetEmpDept procedure. Figure 3-16 shows the last few rows of the imported XML data.

Figure 3-16. Last few rows of Adventure Works employee XML file

Run the GetEmpDept procedure once again. This time, the data is just refreshed. If you need to see the proof, put a breakpoint in the GetEmpDept procedure before you run it the second time. Figure 3-17 shows the code in break mode when being run a second time. Figure 3-18 shows the code falling into the Else statement and calling the RefreshXML method instead when the XML map already exists.

Private Function GetNewXMLData () As XIXtwI TniportReaul

Dim sCurrMap As String

Dim result As XlXmlImportResuit

1 check to see if data range is already bound to a map sCurrMap = CurrentMapName If sCurrMap = "" Then result = ActiveWorkbook.XmlImport (m_sXMLSource Fi1e, Nothing, _

m_blnOverwrite, m_oRange} m_sMapName = Act iveWo r Jcbo o k.XmlMaps(Act iveWo rkbo o k.XmlMaps.Count).Name Else m_sMapName = sCurrMap Me.RefreshXML

result = xlXmllmportSuccess End If

GetNewXMLData = result End Function

Figure 3-17. First time importingEmpDept.xml

Private Function GetNewXMLData ( ) As XlXmlln^ortResul

Dim sCurrMap As String

Dim result As XlXmllmport Result

■check to see if dete range is already bound to a map sCurrMap = CurrentMapName If sCurrMap = "" Then result = ActiveWor Jcbo ok. Xml Import [m_sXMLSourceFile, Nothing, _

m_t:lnO/erwrite, m_oRange) m_3MapName = ActiveWorkfcook.XmlMaps (ActiveWorkfcook.XmlMaps . Count) .Name Else m_3MapName = sCurrMap | Me.RefreshXML

result = xlXmllmportSuccess End If

GetNewXMLData = result End Function

Figure 3-18. Second time importing EmpDept.xml

Close the file without saving it, and then reopen it. Let's append some data contained in another XML file that conforms to the same XML schema. In the same standard module, add another method and name it GetAdditionalEmpDeptInfo.

Public Sub GetAdditionalEmpDeptInfo() 'appends data from files sent in from field offices. If oEmpDept Is Nothing Then

Set oEmpDept = New cXML End If oEmpDept.XMLSourceFile =

"C:\Chapter 3\EmpDeptAdd.xml" Set oEmpDept.DataRange = Sheets(1).Range("A1") oEmpDept.GetXMLData False End Sub

Save the file after adding this code.

This function might be used to append data from files you receive on a regular basis. Run the GetEmpDept macro once again. Open the Macro dialog and run the GetAdditionalEmpDeptInfo subroutine to append the new data.

Let's take a look at what this code is doing. First we check to see that we have created an oEmpDept object, and if not, create one. Then we set the XMLSourceFile property to the location of the file containing the additional XML data (otherwise, we'll just append the same data to our list). Once that's done, we call the GetXMLData method again, but this time we pass in the Overwrite flag with a value of False to tell the method to append the data.

Remember to not save the file after importing the data so that you can easily reset the file. Figure 3-19 shows the new XML data appended to our existing worksheet.

Figure 3-19. New rows appended to Adventure Works employee information

There may be a case where you do not want to change the XMLSourceFile property but still need to append data. Let's create a method in our class that allows us to point to the data file directly.

First reset the file by closing without saving. Then reopen the file. In the cXML class, add a method called AppendFromFile. We'll pass in the file name as an argument and use the XmlMaps collection's Import method to append the data. The finished method will look like this:

Public Function AppendFromFile(FileName As String)

'calling this function to append data will not modify the XMLSourceFile Property

ActiveWorkbook.XmlMaps(m_sMapName).Import FileName, False End Function

Once again, this line of code is identical to the code in our earlier experiments, with the exception of calling on the class's internal variable for the XML map name. We can quickly test this method. Add a new subroutine to the standard module called AppendEmpDeptInfo. Add the following code:

Public Sub AppendEmpDeptInfo()

'sample routine to get additional XML data w/o modifying XMLSourceFile Property oEmpDept.AppendFromFile

"C:\Chapter 3\EmpDeptAdd.xml" End Sub

Run the GetEmpDept macro to get the initial data on the worksheet. Then run the AppendEmpDeptInfo procedure from the Macro dialog box. The result is identical, but the XMLSourceFile property was not modified.

In case the contents of the file you're reading will be updated from time to time by external sources, the XmlMaps collection has the ability to refresh the data source.

Add a new method to the cXML class called RefreshXML. Here is the code for the RefreshXML method:

Public Function RefreshXML()

ActiveWorkbook.XmlMaps(m_sMapName).DataBinding.Refresh End Function

Now that we have our XML data in a worksheet, we can modify it or add records. We need to add one last bit of functionality to our class: the ability to save the data back to XML. Reset your project by closing without saving and reopening it.

Add a new method to the cXML class called SaveToFile. The finished SaveToFile method will look like this:

Public Function SaveToFile(Optional SaveAsFileName As String = "FileNotSet") 'if no SaveAsFileName is provided the current XMLSourceFile will be overwritten Dim ExportMap As XmlMap

If SaveAsFileName = "FileNotSet" Then

SaveAsFileName = m_sXMLSourceFile End If

Set ExportMap = ActiveWorkbook.XmlMaps(m_sMapName) If ExportMap.IsExportable Then

ActiveWorkbook.SaveAsXMLData SaveAsFileName, ExportMap Else

MsgBox ExportMap.Name & " cannot be used to export XML" End If End Function

We've included an optional argument for the file name of the saved document and passed in a default nonsense value. If we want to save the data back to the file from which it came, we simply call the method with no argument. The code will use whatever file is stored in the XMLSourceFile property. If we want to write the data out to a new file, we pass in the new file name. As in our original example, we're checking to ensure the map is exportable, and then we're calling the ActiveWorkbook object's SaveAsXMLData method.

0 0

Post a comment