Automate Excel

I’ve been researching Excel XML the past couple days.

I started off with the goal of finding an easy way to filter XML data BEFORE it hit the spreadsheet. For instance, instead of dumping all of the XML to a list, then filtering it, I wanted to “query” the XML something like this:

(SELECT * FROM XML WHERE Record LIKE ‘Airport’)

similar to how I would query a database (in theory), however I was unsuccesful (I believe it’s possible to do it in the Schema, but that’s very user unfriendly). Also, XPath Predicates with a filter(see links) could possibly be what I’m looking for, however I couldn’t nail down the proper syntax.

A few links I found along the way:

Delicious Excel XML Links

Using the Excel 2003 Object Model to Add XML Data Integration (I could import and map XML to a repeating list with Xpath, however I couldn’t figure out the filter syntax(see XPath Predicates)….what is @foo?)

Microsoft Office Excel 2003 Preview (see XML Programmability)

W3Schools (lotsa XML links on the left menu)

XML Standards Reference (Xpath is there also)

W3C Schema Primer

Transform XML Files with XSLT When Importing into Microsoft Excel

Microsoft Excel 2002 and XML

Using Microsoft’s XMLHTTP Object to Get Data From Other Web Pages

Determine the Version of MSXML Parser Installed on a Computer

I created an Excel macro to post data to our WebService servlet.