Figure

You can programmatically retrieve information about element nodes from the XML document. The IterateThruElements procedure was used to create this spreadsheet.

To list all the nodes that match a specified criterion, use the selectNodes method. The following procedure (SelectNodes_Specify-Criterion) returns to the Immediate window the text for all Title nodes in the Courses.xml file. The "//Title" criterion of the SelectNodes method looks for the element named "Title" at any level within the tree structure of the nodes.

3. Enter the following procedure in a module. Remember to change the path of the XML document to point to the file location on your disk.

4. Run the procedure in a step mode by pressing F8.

Sub SelectNodes_SpecifyCriterion() Dim xmldoc As MSXML2.D0MDocument30 Dim xmlNodeList As MSXML2.IXMLD0MNodeList

' Create an instance of the DOMDocument Set xmldoc = New MSXML2.D0MDocument30 xmldoc.async = False ' Load XML information from a file xmldoc.Load ("C:\ExcelVBA2002\Chap17\Courses.xml") ' Retrieve all the nodes that match the specified criterion Set xmlNodeList = xmldoc.selectNodes("//Title") If Not (xmlNodeList Is Nothing) Then For Each myNode In xmlNodeList

Debug.Print myNode.Text Next myNode End If End Sub

The criterion in the selectNodes method can be more complex. Let's assume that you are only interested in the title for the Course element with the ID of "VBA2EX." To retrieve this information, use the following statement:

Hi

i<i.;i.lt fund -

l„nt1

SJ E'le £dil & ew

Irisait Firmal lods Ùata Wndtw

Halo -

s

X

1 Anal CE

- IQ - n / U _

= = 1 _j

- & ' & -

fß »Hg §B iiffi - « - Z

-ÍI Ü

um - \i)

A1S

A

E

C

E '

T

1

E:amppl Marna

Tesl

1

3

Title

Beginning VEIA In Eycsl

.1

Startiiate

2/l/2im

5

Sessions

6

E

7

Tilly

Ints-mediats VBA in Evcy

0

Startdate

4/3Z2C03

Sessions

|

10

11

Title

Advanced VEtA. in E::ce

12

Startdate

&3CC03

13

Sessions

\2

11

> n'\bhectl,I

Shset2 / llseB / h|

1

tir

Dram- fe fljjtoSharsB - X f. O H Jf\

0 SO Q » . £

Read/ ;

Set xmlNodeList = xmldoc.selectNodes("//Course[@ID='VBA2EX']//Title")

The above statement tells the XML processor to start searching for an element named Course at any level within the tree structure of nodes and find only the course element whose ID attribute contains the value of "VBA2EX," and return the Title element.

If all you want to do is retrieve the first node that meets the specified criterion, use the selectSingleNode method of the XML document. As the argument of this method, specify the string representing the node that you'd like to find. For example, the following procedure finds the first node that matches the criterion "//Title" in the Courses.xml document. The result of this procedure is the text "Beginning VBA in Excel" written to the Immediate window.

5. Enter the following procedure in a module. Remember to change the path of the XML document to point to the file location on your disk.

6. Run the procedure in a step mode by pressing F8.

Sub Select_SingleNode()

Dim xmldoc As MSXML2.DOMDocument30 Dim xmlSingleN As MSXML2.IXMLDOMNode

' Create an instance of the DOMDocument

Set xmldoc = New MSXML2.DOMDocument30 xmldoc.async = False ' Load XML information from a file xmldoc.Load ("C:\ExcelVBA2002\Chap17\Courses.xml") ' Retrieve the reference to a particular node Set xmlSingleN = xmldoc.selectSingleNode("//Title") Debug.Print xmlSingleN.Text End Sub

The following statements will retrieve the first Course node with the ID attribute:

Set xmlSingleN = xmldoc.selectSingleNode("//Course//@ID") Debug.Print xmlSingleN.Text

If you replace the last two lines in the Select_SingleNode procedure with the above statements and run the procedure again, you should see the text "VBA1EX" in the Immediate window.

Once you find the correct node to work with, you can easily modify its value. For example, to change the text of the first Course element with the ID attribute, use the following lines of code:

Set xmlSingleN = xmldoc.selectSingleNode("//Course//@ID")

xmlSingleN.Text = "VBA1EX2002"

xmldoc.Save "C:\ExcelVBA2002\Chap17\Courses.xml"

Notice that to make a permanent change in the XML document, you must resave it using the Save method.

When using the selectSingleNode method, you should use the Is Nothing conditional expression to determine whether a matching element was found in the loaded XML document. The modified Select_SingleNode_2 procedure shown below will display the user-friendly message if the criterion specified as the argument of the selectSingleNode method is invalid. To try out this example, replace the XPath expression "//Course//@ID" with 7/Cours//@ID" and run the procedure. You should see the text "No nodes selected" in the Immediate window.

7. Enter the following procedure in a module. Remember to change the path of the XML document to point to the file location on your disk.

8. Run the procedure in a step mode by pressing F8.

Sub Select_SingleNode_2()

Dim xmldoc As MSXML2.D0MDocument30 Dim xmlSingleN As MSXML2.IXMLD0MNode

' Create an instance of the DOMDocument

Set xmldoc = New MSXML2.D0MDocument30

xmldoc.async = False

' Load XML information from a file xmldoc.Load ("C:\ExcelVBA2002\Chap17\Courses.xml")

' Retrieve the reference to a particular node

'Set xmlSingleN = xmldoc.selectSingleNode("//Title")

Set xmlSingleN = xmldoc.selectSingleNode("//Course//@ID")

If xmlSingleN Is Nothing Then

Debug.Print "No nodes selected."

Else

Debug.Print xmlSingleN.Text xmlSingleN.Text = "VBA1EX2002" Debug.Print xmlSingleN.Text xmldoc.Save "C:\JK_B00KS_ALL\Excel_EN_2002\" _ & "ChaptersNew\Chapter_17\Courses.xml"

End If End Sub

XML DOM provides a number of other methods that make it possible to programmatically add or delete elements. Covering all of the details of the XML DOM object model is beyond the scope of this chapter. When you are ready for more information on this subject, visit the following web links: http://www.w3.org/DOM/ and http://www.w3.org/XML/.

XML via ADO

In Chapter 15 you learned how to perform database operations using the ActiveX Data Objects (ADO). This section will show you what you can do with XML and ADO. Since ADO version 2.5 (released in 2000), you can save all types of recordsets as XML to disk. You can also save any type of ADO recordset to XML in memory, using the ADO Stream object.

0 0

Post a comment