Using VBA to Program Open XML Files

You may be wondering why anyone would try to manipulate an Excel file by programming its XML parts. After all, doesn't Excel have a perfectly good object model? Well, there are a few benefits to be gained by programming against the Open XML files directly:

□ Encoding text files is always faster: Remember that when you are programming against the Open XML files, you are essentially working with text files. Because there is very little overhead involved in programming XML files, your procedures will run more efficiently and far faster than they would using Excel's object model.

□ Find it, change it, get out: Efficient relationship management, indexing, and shared files make it easy to find the exact component you need to manipulate. For instance, changing one string in the Sharedstring.xml file will apply that change to every instance of that string in your workbook. Changing a connection to external data is as easy as editing text within the connections.xml. Removing macros from a workbook is as easy as deleting the VBA project in the Excel container and updating the .rels XML file. With the Excel object model, you must negotiate over various objects, methods, and properties to make any change in the workbook.

□ Working with multiple workbooks: When you need to make changes to dozens of files at one time, you typically apply some automation to instantiate a new instance of Excel to make the needed changes to that workbook. You then save that workbook and commence to open the next one to apply the same changes. With the Open XML files, there is no need to call Excel at all. You simply unpack the compressed container, edit the XML files, and repack. This not only simplifies the updating of multiple workbooks, but it speeds up the process considerably.

In this section, you get a glimpse of these benefits by walking through some examples of how you can use the techniques you have learned thus far to program Excel via its XML parts.

0 0

Post a comment