When we say Excel XML, there are several things we could be referring to. In fact, the newer Excel file formats actually use XML natively to save workbooks – these are the Excel file types that end in “x”, such as my_model.xlsx.
But what I am writing about today is how easy it is to use XML in Excel. XML files are often from a web source that keeps the information within the files current. Sometimes these sources are referred to as web services, as the files can be generated from a database, on demand.
If you want to use XML data in your workbook, you can do so in a huge number of ways with VBA. But there is an extremely simple solution that requires no programming whatsoever. The easiest way to facilitate an XML to Excel conversion is to employ the New Web Query dialog. Just select the Data tab on the Ribbon and then click on From Web. Enter the address of the XML file and click Go.
Excel does a great job of removing all of the XML tags, leaving just the data we want. Click Import. If the XML file does not refer to a schema, Excel creates one automatically and is very accurate when doing so.
The result is an Excel Table placed at the location you specify. You can now use this data in any way you would normally use Excel data. But there is a huge bonus here. You can REFRESH the table whenever you like and if there is newer data available from the source, the table updates itself. This means your entire model can be updated, simply via an update at the source.
To demonstrate, please download my dynamic Periodic Table Excel template.
It is built on the foundation of several XML source files. To be sure, the data that describe the chemical elements do not change at break neck speed, but they do change as new elements are discovered and better experimentation procedures produce more accurate property values.
I modeled this template based on a dynamic web periodic table that I created in 2007. It works great in Chrome, Firefox, and Safari, but only works (slowly) for version 8 of Internet Explorer. If you scroll to the bottom of the web version, you will see the links to the XML data.
The Tables in the Excel version use these XML links. So when I update those XML files on my server, your copy of the Excel template will update as well. Cool, no?
This Excel XML technique could be used in a myriad of business applications.
Please let me know what you think of this technique, the Excel template, and even the dynamic web Periodic Table.
Learn Advanced Excel!
Advanced interface design is just one of literally dozens of advanced methods that my students in the inaugural class of the Excel Hero Academy are learning.
I’ve received dozens of messages from students telling me that the academy is the best Excel training they have ever found at any price and that it is possibly the best investment they’ve ever made. Wow.
In a few weeks I will be offering the course again. There’s already over 300 people on the interest list for the next class. Why not join us? Just fill the form below and I’ll send you more information as we get closer!
And finally, if you have some interest in my upcoming VBA-only courses offered through the academy, please fill in this interest list form:
If you are reading this in email or RSS and connot see the above form, please click here.