Excel Pragma

Interesting article on MSDN : Creating XML Mappings in Excel 2003

Walk through common XML tasks in Microsoft Office Excel 2003 to learn more about the new XML functionality in Office 2003 Editions. Learn how to add custom schemas, work with XML maps in Excel, and create a series of mappings based on various schemas. (19 printed pages)

I saw the Goodbye CSV and Text Files – Hello XML post on AutomateExcel.com . This only confirmed the possibilities that XML opens for Excel Developers and  gives the reassurance that the time that I’ve spent so far learning and playing around with XML has been well spent ! Over the past month, I have been using XML extensively on a specific project to separate the data collection workbooks from the processing workbooks.

Practically, the project consists of collecting a huge amount of data from the client (data-entry, error-trapping, consistency checks) and then processing this data (generating database scripts for insert into Oracle).

These two operations could have been combined into a single workbook that would contain the data-entry forms, the error-checking and then the script generation process but this would result in a massive file that would probably not go through any mail servers ! In addition, some of the data needs to be re-used in other workbooks so this would also mean some serious workbook linking and good luck debugging !

Enters XML ! and here’s how it’s helped me so far:

• the project is now two layers : one for data-entry (with plenty of error-checking) and one for processing.
• the data is shared between the two layers in XML. (export to a file from one side, and then refresk from the other side)
• the data is also stored separately from the worksheets in a central location (for back-up and for re-use in other workbooks)
• the data is independent from the workbooks so upgrades on the workbooks can happen without any loss of data.
• the data can also be used in other worksheets as needed. Other worksheets can also load only the specific fields that are needed instead of loading and processing the full file.

I am sure there’s still plenty to explore in Excel’s XML . I’d like to know how you’re using it too.