Exporting a range to an XML file

This example exports an Excel range to a simple XML data file. As you might know, an XMLfile uses tags to wrap each data item. The procedure in this section uses the labels in the first row as the XML tags. Figure 27-6 shows the range in a worksheet, and Figure 27-7 shows the XML file displayed in Internet Explorer.

Although Excel 2003 has improved support for XML files, it surprisingly can't create an XML file from an arbitrary range of data unless you have a map file (schema) for the data.

Fl

|_|n|x

A

B

C

D

E

F

G

HT"

2

9001

Davolio

Nancy

Sales Representative

12/8/68

5/1/01

507 - 20th Ave. E. Apt

2A

Seattle

3

9002

Fuller

Andrew

Vice President, Sales

2/19/52

8/14/02

908 W. Capital Way

Tacorr

4

9003

Leverling

Janet

Sales Representative

8/30/63

4/1/001722 Moss Bay Blvd.

Kirklar

5

9004

Peacock

Margaret

Sales Representative

9/19/50

5/3/99

4110 Old Redmond Rc

Redm

6

9005

Buchanan

Steven

Sales Manager

3/4/55

10/17/93

14 Garrett Hill

Londoi_

7

9006

Suyama

Michael

Sales Representative

7/2/E3

10/17/97

Coventry HouseDMine

Rd

Londoi

8

9007

King

Robert

Sales Representative

5/29/6Q

1/2/98

Edgeham Hollow Wine

i est

Londoi

9

9008

Callahan

Laura

Inside Sales Coordinator

1/9/56

3/5/94

4726 - 11th Ave. N.E.

Seattli

10

9009

Dodsworth

Anne

Sales Representative

7/2/69

11/15/99

7 Houndstooth Rd.

Londoi

11

9102 Jackson

Raymond

Sales Representative

2/16/52

3/4/00

11 Franklin Way

► ir

|H

► H [\ Sheet 1 /

J_U

I

Figure 27-6: The data in this range will be converted to XML.

•3C:\Documents and SettingsUohn\Desl<top\inyrange.

Microsoft Internet Explorer

File Edit View Favorites Tools Help

Stop Refresh Home ft

<?xmi version="l,0" encoding="UTF-8" standaione="yes" ?> - <EmpioyeeList xmlns:xsi=''http://www.w3.org/2001/XMLScherri

<EmpioyeeID>9001</EmpioyeeID>

< La s tN a m e> Da vo I i o</Last Name> <FirstName>IMancy</FirstName> <Titie>Sales Re present at ive</Ti desert h Da te> 19 68-12-0 8 </BirthDate> <HireDate>2001-05-01</HireDate> <Address>507 - 20th Ave. E. Apt. 2A</Address>

< Cit y >S eatt I e</C i ty > <Region>WA</Region> <PostalCode>98122</PostaiCode> <Country>U S A</C o u nt ry > <HomePhone>(206) 555-9857</HomePhone>

</Empioyee>

<EmpioyeeID>9002</EmpioyeeID> <LastName>Fuller</LastName>

< Fi rst Name? A n drew </F i rs t N ame:> <Titie>Vice President, Sales</Titie> <BirthDate>1952-02-19</BirthDate> <HireDate>2002-08-14</HireDate> <Address>90B W. Capital Way</Address>

< Cit y >Ta coma </Ci t y > <Region>WA</Region> <PostaiCode>98401</PostaiCode>

< Cou n try >U S A</C o u nt ry > <HomePhone>(206) 555-9482</HomePhone>

</Employee>

<EmpioyeeID>9003</EmpioyeeID> <LastlMame>Leverling</LastName> <FirstName>3anet</FirstName> •TitlR^RnlB'S RpnrfiSfintativptfVTiflfiii e] Done

I J My Computer

Figure 27-7: The worksheet data after being converted to XML.

The ExportToXML procedure is in Listing 27-7. You'll notice that it has a lot in common with the ExportToHTML procedure in the previous section.

Listing 27-7: Exporting an Excel Range to a Simple XML Data File

Sub ExportToXML()

Dim ws As Worksheet Dim Filename As Variant Dim TDOpenTag As String Dim CellContents As Str Dim Rng As Range Dim r As Long, c As Integer

TDCloseTag As String

Set the range

Get a file name

Filename = Application.GetSaveAsFilename( _ InitialFileName:="myrange.xml", _ fileFilter:="XML Files(*.xml), *.xml")

Listing 27-7 (Continued)

If Filename = False Then Exit Sub

' Open the text file

Open Filename For Output As #1

Print #1, "<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes""?>" Print #1, _

"<EmployeeList xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"">"

' Loop through the cells

For r = 2 To Rng.Rows.Count Print #1, "<Employee>" For c = 1 To Rng.Columns.Count

Print #1, "<" & Rng.Cells(1, c) & ">"; If IsDate(Rng.Cells(r, c)) Then

Print #1, Format(Rng.Cells(r, c), "yyyy-mm-dd");

Else

Print #1, "</" & Rng.Cells(1, c) & ">"

Next c

Print #1, "</Employee>" Next r ' Close the table

' Close the file Close #1

' Tell the user

MsgBox Rng.Rows.Count - 1 & " records were exported to " & Filename End Sub

Chapter 28

Was this article helpful?

0 0

Post a comment