Before you can do anything with DOM, you will have to set a reference to the MSXML object library. To do so, open the Visual Basic Editor and select Tools O References. In the References dialog box, select the latest version of Microsoft XML, as demonstrated in Figure 12-9.
References - VBAProject m
Microsoft XMLj v2.6 [a|
Microsoft XML, v3.0 Microsoft XMLjVt.O
Microsoft XML, version 2.0 Microsoft.Vsa.dll MimeDir 1.0 Type Library mmAEPIugln 1.0 Type Library MMC Internal Web Browser event sink 1,0 Type Libre MMFUtil 1.0 Type Library MMRadioEngine 1.0 Type Library MMS Application Library MOFL 2.0 Type Library MnhvfiRrnw^fir 1 .n Tvne I ihrsrv
Microsoft XML, v5.0 -
Location: C:\Program Files\Common Files\Microsoft Shared\OFFICEll\M Language: Standard
You will notice that there are four different versions of MSXML shown in Figure 12-9. A fifth version (Microsoft XML, v6.0) may be available to you if you have installed SQL Server 2005. You generally want to set your reference to the latest version available. However, if you are building XML-based solutions that will be distributed to other users, you will want to take into account the version of Internet Explorer your users have installed. If there is a good chance that some of your users are using Internet Explorer 5.0, you will want to set the reference to Microsoft XML, v3.0.
In any case, the differences between the various versions, for the most part, will not impact most of the tasks you as an Excel developer will need to accomplish. For instance, you can be confident that the rest of the procedures in this chapter would run fine with Microsoft XML, v3.0. If you have a burning need to find out what are the exact differences between the various versions of MSXML, you can find out here: http://windowssdk.msdn.microsoft.com/en-us/library/ms753751.aspx.
Once your reference is set, take a look at the code shown here. In this procedure, you are loading an XML file into a DOMDocument object. The DOMDocument object is the top-level container for the parsed XML file, serving as the parent for all nodes in the XML file's node structure. To programmatically explore and manipulate XML documents, they must first be exposed through the DOMDocument object.
Sub Load_ReadXMLDoc() Dim oMyDoc As DOMDocument
'Create an instance of the DOMDocument Set oMyDoc = New DOMDocument
'Disable asynchronous loading oMyDoc.async = False
'Load XML information from a file oMyDoc.Load (ThisWorkbook.Path & "\SalesByRegion.xml")
'Use the DOMDocument object's XML property to retrieve the raw data Debug.Print oMyDoc.XML
Set oMyDoc = Nothing
First, instantiate a DOMDocument object, assigning it to the MyDoc variable. Next, disable asynchronous loading. Asynchronous loading is a process that MSXML uses by default to load documents in stages, allowing for cancellation and feedback during load. You generally will want to disable asynchronous loading to ensure that the document loads in its entirety before a result is returned.
Use the Load method to load the chosen XML document into the DOMDocument object. This essentially takes a snapshot of the XML document and loads an in-memory version of the XML document that you can explore and modify via VBA. In this example, you retrieve the raw XML data from the document and output it to the Immediate window. After you are done with any DOMDocument, you should always release it from memory.
Was this article helpful?
Are You Ready to Start Writing? Times are very hard, and there is no reason to assume or imagine that things are going to improve any time soon. The world as a whole has undergone some very traumatic changes over the last few years, and the financial situation in most countries seems grim.