Section 10.3. XML


10.3. XML

Extensible Markup Language (XML) is another potential data source. It is basically a set of rules for storing data and information about the data. It can include formatting or relationship rules, and XML can even describe an entire Excel document. You are more likely to get XML data from Internet-based applications, but it can come from any type of system and is growing in popularity.

XML handling was enhanced in the professional version of Excel 2003. But older versions can import some XML files. Remember, Excel is limited to a two-dimensional view of information. XML is not. If the XML data is too complex to be represented as rows and columns Excel will have trouble with it.

I have created an XML file with the stock data used in the previous examples. The first few lines of the file look like this:

 <?xml version="1.0"?> <Stocks> <Stock> <Symbol>CEC</Symbol> <Date>10/03/2001</Date> <Open>32.55</Open> <High>35.5</High> <Low>32.53</Low> <Close>34.5</Close> <Volume>3809</Volume> </Stock> <Stock> <Symbol>CEC</Symbol> <Date>11/01/2001</Date> <Open>38.65</Open> <High>38.95</High> <Low>38</Low> <Close>38.54</Close> <Volume>1221</Volume> </Stock> 

An XML file includes descriptive information called tags. This makes XML files larger and a little less efficient to handle. The advantage with XML is that the data is fully described; there are no questions about delimiters or mapping. XML was developed to make information sharing between applications easy.

There are several ways to get XML data into Excel, but the easiest to use is the import found on the Data XML menu. This allows you to navigate to the XML file you want to import. When you double-click on the file, the dialog in Figure 10-22 appears.

Figure 10-22. Create a schema


XML files can have a data description and handling instructions called a schema. This file doesn't have one and Excel is telling us that it will create one to use with the file. You may want to disable this dialog by checking the "do not show this message" box.

Next you see Figure 10-23.

Unfortunately, the Import dialog doesn't give you a way to schedule a refresh on the data, but there is a refresh XML data function on the XML submenu. A scheduled refresh can be built in VBA using the application object's ontime event. It takes several pieces of code to get this to work. I imported my XML data into cell A1 on a sheet named XML. This code will update the data every five minutes:

 Public Sub XMLRefresh(  ) Dim MySheet as String 

Figure 10-23. Completing the import


 ' Turn off screen updating so the user will not ' see all the jumping around. Application.ScreenUpdating = False ' Save the name of the active sheet. At the end of ' the update we will go back to this sheet. MySheet = ActiveSheet.Name ' Go to the XML sheet. This is where ' the XML data is. Sheets("XML").Select ' Select the cell we imported the data into. Range("a1").Select ' Refresh the data. ActiveWorkbook.XmlMaps("Stocks_Map").DataBinding.Refresh ' Establish a time five minutes in the future. ' We need to know this time in the sub that ' ends the process. So, RunTime is setup as a ' Public variable RunTime = Now + TimeSerial(0, 5, 0) ' Schedule this macro to run in five minutes. Application.OnTime RunTime, "XMLRefresh" ' Go back to the sheet we started on. Sheets(MySheet).Select ' Turn screen updating back on. Application.ScreenUpdating = True End Sub 

The variable RunTime needs to be available to another sub so it is declared as public in the declaration sections at the top of the module, as follows:

 Public RunTime

These two small subs are added to the workbook:

 Private Sub Workbook_Open(  ) XMLRefresh End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) On Error Resume Next Application.OnTime RunTime, "XMLRefresh", Schedule:=False End Sub 

The first one runs when the workbook opens. It runs the refresh which in turn reschedules itself. The ontime event will be there as long as Excel is running. So, if I close the workbook, five minutes later it will reopen and run the refresh. Therefore, I need a way to stop the process when the workbook closes. That is what the second sub does.

The import puts the data on a worksheet, and it can be used to feed data to an application or just populate a sheet for ad hoc analysis.

The result is shown in Figure 10-24.

Figure 10-24. Imported XML data


Excel automatically puts the data in a list. The list feature includes a button by each heading that allows you to sort or filter the data. There are also totaling features available on the Data List menu. This menu also has a "Convert to Range option allowing you to turn the list feature off if you don't want to use it.

You can also open an XML document in Excel. The data opens in a separate workbook and the format is less than ideal. But it works, and copying and rearranging the data is not difficult.

To use this approach, start with the File Open menu. Navigate to the XML file you want and when you double-click on it, the dialog in Figure 10-25 appears.

Figure 10-25. Open an XML document


The three choices let you control how the data will be handled. If you open the documents as an XML list, you get the same result as the import except the data will be in a separate workbook. The read-only workbook option returns data that looks like Figure 10-26.

Figure 10-26. Read-only XML data


If you are not using Excel 2003 Professional or higher, this is your only option without using VBA. All the data is there, but the headings include some XML tag information, the columns are arranged alphabetically, and there are extra #agg columns added. The #agg columns are intended to make duplicate data in columns easier to understand.

It is less than ideal, but you can build an application using this data, and it can be copied and rearranged without much trouble.

The third option, the XML task pane, brings up the dialog in Figure 10-27.

This allows you to select which columns you want and to control the order. Once you select your columns, you populate the sheet using Data XML Refresh XML Data, as shown in Figure 10-28.

The result is a list in a new workbook containing your data.

Figure 10-27. The XML task pane


Figure 10-28. Putting data on the sheet.




Analyzing Business Data with Excel
Analyzing Business Data with Excel
ISBN: 0596100736
EAN: 2147483647
Year: 2006
Pages: 101
Authors: Gerald Knight

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net