Playing together: Excel and XML

Q: What is XML?

XML (extensible markup language) provides an environment in which data objects ("tags") can be a priori defined to have specific characteristics, and relationship to each other. It is comparable to defining a database of variables. The definition file (myXMLdefinitionfile.xsd) is called a "schema" definition file (.xsd stands for XML schema definition file). Schema is just another name for a model or template.

Q: What does this have to do with Excel?

Excel 2003 and later versions have built-in capability to operate with .xml files (edit, import, export and the like). The .xml file can be "mapped" to an Excel spreadsheet. This "mapping" allows an Excel model, or spreadsheet calculation to operate on data from arbitrary sources - as long as the schema for the data exists. This approach was used in a recent application in which the same data was in different places in different versions of an Excel data template.

Recurse Me

Q: What is recursion, and how can it be used?

Recursion occurs when a subroutine or function "calls itself". This is most useful for situations where the same code must be applied multiple times to a number of different data objects.

The classic example is to solve the problem y=n! (read y equals n factorial). The solution is to mutliply n first by (n-1), then multiply the result by (n-2) and so on until only "1" is left as a factor. One can readily see that the first factor is to be multiplied by (n-1)!  This can be accomplished with recursive code outlined below:

Sub TryNfactorial()
n = 3
y = Nfactorial(n)
MsgBox (y)
End Sub

Function Nfactorial(n)
If n > 1 Then Nfactorial = n * Nfactorial(n - 1) Else Nfactorial = 1 ' while n > 1 - keep going deeper
End Function

A powerful additional use of recursion is to create a recursive VBA function which deals with stored files in multiple folders in a folder hierarchy. For example - say we wanted to add a plot to all .xls files which had "DataToPlot" in the filename. Below is an outline of this code.

1) Use MsofilePicker to ask the user for the first node of the structure of interest
2) Use the filescripting object to create the folder, sub-folder and files objects for the current node
3) If there are more folders at this node - call the same (now recursive) routine again to shift the node to a new (deeper) location
4) When there are no more folders (only files) - process the files - check if they are .xls and have "DataToPlot" in the name (with the "Like" statement) - if so open, create a plot, and save (and return)

We have applied this approach to several different Windows-based file modifications successfully. See our website for more information:

http://www.TechnicalAutomationServices.com/

Multi-page format changes

Dear MacroGenie - "How can I change the format of the same range of cells on several selected sheets?"

MacroGenie: The formatting of spreadsheet cells, the zoom level, and many other characteristics can be modified at once. Select the cells of interest on the first worksheet, then select additional worksheets with on the sheet names. Make the desired change, and all sheets will be updated.

MacroGenie Leaves The Lamp

Welcome master,

I have joined your world to assist you with your Excel bidding. In the coming years I shall convey useful Excel tidbits to make your life easier. Ask of me what you wish....

MacroGenie