Importing XML in Excel

Importing XML data into an Excel workbook is a fairly straightforward process. The Workbook object has a method called XmlImport that does the work for us.

Open a new workbook and save it with an .xlsm format file extension. Open the VBE, add a standard module, and create a new function named GetXMLData():

Sub GetXMLData() ActiveWorkbook.XmlImport URL:=

"C:\projects\Excel\cds.xml", ImportMap:= Nothing, Overwrite:=True, Destination:=Range("$A$1") End Sub

Caution Be sure to adjust the file path to reflect your location for the XML file.

The ActiveWorkbook object is actually a property of the Application object that returns a Workbook object type, and therefore contains all the properties and methods of the Workbook object.

Figure 3-1 shows the ActiveWorkbook object as it appears in the Visual Basic Object Browser. Figure 3-2 shows the Workbook object and its properties and methods. The ActiveWorkbook object has all the properties and methods of the Workbook object.

Classes © Action © Actions S Addln S Addlns © Adjustments © AllowEdltRange © AllowEdltRanges EPSiAppNcatlon y

Members of'Application" Activate MicrosoftApp nf? Active Cell nf? Active Chart nf? ActiveEncryptionSession

Active Printer ^ Active She et ^ ActiveWindow l^iÄäiveWorkbook

Property ActiveWorkbook As Workbook read-only

Member of Excel .Application

Figure 3-1. ActiveWorkbook property of Application object

If you try double-clicking the ActiveWorkbook property, you will not find any properties or methods associated with it in the Object Browser. Click the Workbook link in the description section of the Object Browser, however, and you will see the Workbook object's properties and methods listed, as shown in Figure 3-2. Since a reference to the ActiveWorkbook is actually returning a Workbook, you can access all of these properties and methods.

Classes © Watches © WebOptions

WebPageFont © WebPageFonts © Window © Windows

© WorkbookConnectio[v|

Members ofWorkbook" AcceptAIIChanges Activate 9 Activate eI? Active Chart Active She et 9 Addinlnstall 9 AddinUninstall Ad dTo Favo rite s

I Class Workbook] Member of Excel

Figure 3-2. Workbook object and class members

I'll present you with a quick example to show that the two objects contain the same class members. Following are two screenshots. Figure 3-3 contains the Immediate window showing IntelliSense for the Workbook object, and Figure 3-4 shows IntelliSense for the ActiveWorkbook object.

Immediate application.Workbooks(1)

^ i Acce ptAI 1 Ch an g e s

^ Activate

j

lá? Active Chart

nf? ActiveSheet

^ Ad dTo Favo rite s

nf? Application

ApplyTheme

-

Figure 3-3. IntettiSense for Workbook object ectiveworktook

^ i Acce ptAI 1 Ch an g e s

-

^ Activate

LJ

là? Active Chart

nf? ActiveSheet

^ Ad dTo Favo rite s

nf? Application

-1

ApplyTheme

Figure 3-4. IntettiSense for ActiveWorkbook object

So if you find yourself examining an object in the Object Browser and you don't see any class members, check to see if it's a property of another object and look at that object's class members.

Before we run the code, let's take a look at the Xmllmport function and see what it does (see Figure 3-5).

Immediate ectiveworkfcook.XmlImport

XrrillrriportiLfr'Ms String, impoftMap A s Xrn IM a p [Overwrite] [Destination]) As XIXmllmportResult |

Figure 3-5. XmlImport function arguments

The XmlImport function takes four arguments, as shown in Table 3-1.

Table 3-1. XmlImport Function Argument Descriptions

Name

Required (Y/N)

Data Type

Description

URL

Y

String

Path to an XML data file.

ImportMap

Y

XMLMap

The XML map to apply when importing the file.

Overwrite

N

Variant

Specifies whether or not to overwrite data that has been mapped to the schema map specified in the ImportMap parameter. Set to True to overwrite the data or False to append the new data to the existing data.

Destination

N

Variant

Specifies the top-left cell of the range that will display the data.

The XmlImport function returns a value of type XIXmlImportResult, which is an enum showing the success or failure of the call. Figure 3-6 shows the available options displayed in the Object Browser window.

Classes sip XIWebSelectlonTypeQ

sip XlWIndowState sip XIWIndowType sip XlWIndowVlew sip XIXLMMacroType sip XIXmlExportResult sip XIXmiLoadOption [v]

Members ofXIXmllmportResulf [Ii xlXmllmpoitElementsTruncated [Ii xlXmllmportSuccess [Ii xlXmllmportValldatlonFalled

Figure 3-6. XIXmlImportResult members

Let's run our GetXMLData procedure and see what we get.

If no schema exists for this data, Excel will create one for you, as shown in Figure 3-7.

Figure 3-7. XML Schema dialog box

Excel refers to these schemas as XML maps. Click the check box so you won't see this message any longer, and let the import continue. When the import is finished, the data should look like that in Figure 3-8.

1991

3 Nirvana In Utero

4 Alice In Chains Facelift

5 Alice In Chains Dirt

1991

3 Nirvana In Utero

4 Alice In Chains Facelift

1990 1992

5 Alice In Chains Dirt

6 Alice In Chains Unplugged

1996 1993

7 Tool

Undertow

8 Tool

Aenima

1996

9 DearlJam

1992,

Figure 3-8. Imported XML file

Our call to XmlImport sets the arguments like this:

ActiveWorkbook.XmlImport URL:= _

"C:\projects\Excel\cds.xml", ImportMap:=

Nothing, Overwrite:=True, Destination:=Range("$A$1")

The URL argument is pointing to a file named cds.xml, which contains discography information. The ImportMap argument is set to Nothing for now because we do not have an existing schema for this data. We are setting Overwrite to True to overwrite any existing data, and the Destination range is setting cell A1 as the target for the start of our data range.

If we try to rerun this code as is, we will generate an error, as shown in Figure 3-9.

The operation cannot be completed because the XML table is bound to

Figure 3-9. XML map error

This is due to the fact that the first time we ran the code, Excel created a map for us and bound our data table to it. To view that map, right-click anywhere in the data range and choose XML > XML Source (Figures 3-10 and 3-11).

Figure 3-10.XML menu
Figure 3-11. The XML Source window

Figure 3-11 shows the XML Source window with the artist element selected. Selecting an element in the XML Source window selects the associated (or mapped) data range. Above the XML map is a drop-down list that contains all of the XML maps in the currently active workbook. The XML map in this project is called cds_Map.

The second time the code is run, we run into trouble because Excel automatically creates a new map for the data that is mapped to destination cell A1. Excel will not allow you to bind your data to more than one XML map. If you change the destination cell, the procedure runs fine, however. So how do we refresh our data if a source file is updated?

The XMLMaps collection has a DataBinding property that has a Refresh method. Using this method, we can read in any changes to the XML file that our data range is mapped to.

Before we begin, let's copy our GetXMLData function into a standard code module in a new workbook and save it. Run the GetXMLData macro from the Developer ribbon.

Open the cds.xml file and add a new title to the list by adding the following set of nodes at the top of the XML file:

<artist>Nirvana</artist>

<title>Greatest Hits</title>

<releasedate>2000</releasedate> </cd>

Save the file. Add the following function to the standard module:

Sub RefreshXML()

Range( "A1").Select

ActiveWorkbook.XmlMaps("cds_Map").DataBinding.Refresh End Sub

Run the code by pressing the F5 key with the insertion point inside the RefreshXML subroutine. The data range is expanded to include the new data, as shown in Figure 3-12.

IEL a

H

artist S3 title E^J|releasedate H

2

Nirvana Greatest Hits

2000

3

Nirvana Nevermind

1991

4

Nirvana In Utero

1993

5

Alice In Chains Facelift

1990

6

Alice In Chains Dirt

1992

7

Alice In Chains Unplugged

1996

8

Tool Undertow

1993

3

Tool Aenima

1996

10

Pearl Jam Ten

1992,

Figure 3-12. XML data refreshed

Figure 3-12. XML data refreshed

Remove the new set of nodes and run the RefreshXML method again, and the table will be resized to display only the data from the XML file.

Excel 2007 provides other methods to remove and load XML map settings. The DataBinding.ClearSettings method unbinds the data in a range from the XML file.

ActiveWorkbook.XmlMaps("cds_Map").DataBinding.QearSettings

The DataBindings.LoadSettings method takes the file path or URL as an argument to load the mapping from the specified XML file.

ActiveWorkbook.XmlMaps("cds_Map").DataBinding.LoadSettings "C:\projects\Excel\cds.xml"

0 0

Responses

Post a comment