Users of Office 2003, Office XP, and Office 2000 can open, edit, and save files
using the new Excel XML format.
By providing the XML file format, Microsoft has given us the ability to create Excel files
(and Word and PowerPoint files as well) on machines that do not have these applications
installed. Navigating the Excel DOM, some of which we worked with in the previous chapter,
is no longer the only method to access Excel data and/or create Excel files.
XML also gives us the advantage of using XSL to transform external XML to Excel format
and from Excel format to many other formats.
Importing XML in Excel 2007
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
nCaution Be sure to adjust the file path to reflect your location for the XML file.
99
C H A P T E R 3
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.
Pages:
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123