Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.
Nick Hodge's Excel Blog » Ribbon, Step-by-Step. Part 1 (File Formats)

Ribbon, Step-by-Step. Part 1 (File Formats)

I am going to endeavour to enthuse some more people into development of the new ribbon in Excel 2007 (well other applications too, but I will use Excel throughout). You will need a knowledge of XML and VBA, as these are the two technologies I will be using. I suspect it will take about 4 or 5 'sessions' to get up to some kind of understanding and I intend to cover the following:

  1. File Formats
  2. Dictator Applications
  3. Custom Tabs and the Office Menu
  4. Using Built-In Groups and Controls
  5. Using Custom Groups and Controls
  6. Changing state 'on the fly'
  7. Control Reference.

This will not cover any really advanced topics, as my hope is not to get 'deep, deep' in but to get people going and seeing just how easy it can be. Posts will not be at regular intervals, as I do have a day job!

I would suggest for more advancement and detail you look to buying the RibbonX, Customising the Office 2007 Ribbon, by Martim, Hennig and Puls. (Coincidentally, I was Technical Editor on the project too).

File Formats

It is important, in any discussion of the Ribbon, to discuss the new file formats that also launched with Excel 2007. This will explain where the XML (RibbonX) code for the ribbon is stored, along with other file data.

The file formats are registered under the Office 'Open XML' standard, but at the time of writing have still not received ISO approval, as hoped by Microsoft. The standard has a very set specification, documented over huge volumes, but for the layman, these are based on zip archives, containing XML files arranged in folders.

Basing these on XML and zip compression has resulted in a file format which has arguably better interoperability between disparate systems as, when you drop down file formats of any system, you will eventually come across some 'text import' and in modern systems, XML. Additionally, zipping these results in significantly smaller file sizes of up to 70%. The downside, of course, is earlier versions of Excel have no 'native' ability to open these files and requires 2007 to 'save as...' a 1997/2003 file or, for Excel 2003 to have the downloadable Compatibility Pack to open them from 2003, although in some areas, considerable functionality is been stripped out in this conversion.

For the first time, in an effort to improve safety in 'file contained' code (macros), there are special formats for files containing code. The file formats are as follows:

*.xlsx

This is the standard replacement for the old *.xls workbook familiar to all since Version 5. This format cannot contain VBA code, although it can contain custom UI RibbonX code (XML). Any attempt to save a file containing code to *.xlsx will throw a warning dialog and strip code or save as an *.xlsm.

*.xlsm

This has no equivalent in earlier versions, but is the new 'macro-enabled' format. Any file containing code must be saved as a format ending in 'm' or the binary format. (See below). It has no effect on the macro security settings, these still require permission to run or to be placed in a new 'Trusted Location'.

*.xlsb

This is a binary (not XML) format version of the *.xlsm file. Therefore it can contain code. It is still a zip archive, but is specially developed for speed of opening and calculation. Due to all these features, this is the new 'native' format for personal.xls (now personal.xlsb, but you have to explicitly upgrade to this as any personal.xls is used in the new version on upgrade).

*.xlam

The new 'macro-enabled' add-in format. As such, this can contain VBA code.

*.xltx

The new template format, which can contain no VBA code

*.xltm

The 'macro-enabled' template format that can contain VBA code.

Let's go inside?

To look inside the file we first need to ensure our system is set to show file extensions. In both XP and Vista this is available through opening any folder, selecting the option in the menu that gives you 'folder options' and the 'view' tab. It's in that list.

zip iconxlsx iconOnce we can see the extensions, open a new Excel 2007 file and save it off as any name as a standard *.xlsx file. You will notice that, as expected, the operating system recognises the file extension and shows you an Excel icon as the file's image (left). Now, right-click the file and select 'Rename' and change just the extension from xlsx to zip. The operating system will warn that changing file extensions could cause harm...ignore it! You should now see a standard looking zip file where the Excel file used to be. (see right).

 

 zip window

If you now open this file in the usual way, it should open your zip program and show you a folder view of what's inside, including folders and XML files. My file innards' look like that below. In this basic file we can see just a few folders and one file, but already they sort of make sense. The [Content_Types].xml file has exactly that, the 'types' that are contained in the zip archive, mapped to their containing folders. (see below). You should be able to read that, for example, the detail of one of my worksheets (Sheet1) in my workbook are contained in the /xl/worksheets/ folder, in the Sheet1.xml file.

-<Types>
 <Override PartName="/xl/theme/theme1.xml" ContentType="application/vnd.openxmlformats-officedocument.theme+xml"/>
 <Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>
 <Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>
 <Default Extension="xml" ContentType="application/xml"/>
 <Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>
 <Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml"/>
 <Override PartName="/xl/worksheets/sheet2.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>
 <Override PartName="/xl/worksheets/sheet3.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>
 <Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>
 <Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/>
 </Types>
 
Now, this is where we will store our CustomUI RibbonX (XML) code. just to demonstrate this, below is the inside of a zipped (*.xlsm) file, with far more content, table, chart and most importantly, CustomUI. (Can you spot where it goes, below?).
 
 
If we look inside the same /xl/ folder we see much more content in this file. (below)
 
 
It is now easy to spot components of the workbook, comments, calcChain, styles, etc and lots of sub-folders which would naturally sit below the workbook object in VBA. worksheets, charts, tables, etc. This drills down and down in a not too unfamiliar fashion. (Notice the VBA Project is contained in a secure binary file.
 
If we open the xl/worksheets/sheet1.xml file, we can see within the xml, quite easily below, the values in the table on the sheet, the formulas, the addresses, etc. (One hidden benefit of this is corrupt files are far easier to fix, albeit by removing say charts, including any reference to them in the other files of course. These xml files are much better and more easily viewed in something like XML Notepad 2007.
 
-<worksheet>
 <sheetPr codeName="Sheet1"/>
 <dimension ref="A1:N11"/>
-<sheetViews>
-<sheetView tabSelected="1" workbookViewId="0">
 <selection activeCell="O17" sqref="O17"/>
 </sheetView>
 </sheetViews>
 <sheetFormatPr defaultRowHeight="15"/>
-<cols>
 <col min="1" max="13" width="9.42578125" customWidth="1"/>
 <col min="14" max="14" width="11.28515625" bestFit="1" customWidth="1"/>
 </cols>
-<sheetData>
-<row r="1" spans="1:14">
-<c r="A1" s="2" t="s">
 <v>12</v>
 </c>
-<c r="B1" s="2" t="s">
 <v>0</v>
 </c>
-<c r="C1" s="2" t="s">
 <v>1</v>
 </c>
-<c r="D1" s="2" t="s">
 <v>2</v>
 </c>
-<c r="E1" s="2" t="s">
 <v>3</v>
 </c>
-<c r="F1" s="2" t="s">
 <v>4</v>
 </c>
-<c r="G1" s="2" t="s">
 <v>5</v>
 </c>
-<c r="H1" s="2" t="s">
 <v>6</v>
 </c>
-<c r="I1" s="2" t="s">
 <v>7</v>
 </c>
-<c r="J1" s="2" t="s">
 <v>8</v>
 </c>
-<c r="K1" s="2" t="s">
 <v>9</v>
 </c>
-<c r="L1" s="2" t="s">
 <v>10</v>
 </c>
-<c r="M1" s="2" t="s">
 <v>11</v>
 </c>
-<c r="N1" s="2" t="s">
 <v>13</v>
 </c>
 </row>
-<row r="2" spans="1:14">
-<c r="A2" s="2">
 <v>1998</v>
 </c>
-<c r="B2" s="1">
 <v>5680</v>
 </c>
-<c r="C2" s="1">
 <v>3578</v>
 </c>
-<c r="D2" s="1">
 <v>6337</v>
 </c>
-<c r="E2" s="1">
 <v>5306</v>
 </c>
-<c r="F2" s="1">
 <v>4482</v>
 </c>
-<c r="G2" s="1">
 <v>2442</v>
 </c>
-<c r="H2" s="1">
 <v>9164</v>
 </c>
-<c r="I2" s="1">
 <v>8338</v>
 </c>
-<c r="J2" s="1">
 <v>989</v>
 </c>
-<c r="K2" s="1">
 <v>730</v>
 </c>
-<c r="L2" s="1">
 <v>3885</v>
 </c>
-<c r="M2" s="1">
 <v>9047</v>
 </c>
-<c r="N2" s="1">
 <f>SUM(Table1[[#This Row],[Year]:[Dec]])</f>
 <v>61976</v>
 </c>
 </row>
-<row r="3" spans="1:14">
-<c r="A3" s="2">
 <v>1999</v>
 </c>
-<c r="B3" s="1">
 <v>8125</v>
 </c>
-<c r="C3" s="1">
 <v>4329</v>
 </c>
-<c r="D3" s="1">
 <v>2914</v>
 </c>
-<c r="E3" s="1">
 <v>7376</v>
 </c>
-<c r="F3" s="1">
 <v>1199</v>
 </c>
-<c r="G3" s="1">
 <v>8451</v>
 </c>
-<c r="H3" s="1">
 <v>6775</v>
 </c>
-<c r="I3" s="1">
 <v>9322</v>
 </c>
-<c r="J3" s="1">
 <v>213</v>
 </c>
-<c r="K3" s="1">
 <v>1788</v>
 </c>
-<c r="L3" s="1">
 <v>3291</v>
 </c>
-<c r="M3" s="1">
 <v>1566</v>
 </c>
-<c r="N3" s="1">
 <f>SUM(Table1[[#This Row],[Year]:[Dec]])</f>
 <v>57348</v>
 </c>
 </row>
-<row r="4" spans="1:14">
-<c r="A4" s="2">
 <v>2000</v>
 </c>
-<c r="B4" s="1">
 <v>6104</v>
 </c>
-<c r="C4" s="1">
 <v>9757</v>
 </c>
-<c r="D4" s="1">
 <v>5022</v>
 </c>
-<c r="E4" s="1">
 <v>5780</v>
 </c>
-<c r="F4" s="1">
 <v>929</v>
 </c>
-<c r="G4" s="1">
 <v>7558</v>
 </c>
-<c r="H4" s="1">
 <v>8466</v>
 </c>
-<c r="I4" s="1">
 <v>72</v>
 </c>
-<c r="J4" s="1">
 <v>9013</v>
 </c>
-<c r="K4" s="1">
 <v>8067</v>
 </c>
-<c r="L4" s="1">
 <v>5050</v>
 </c>
-<c r="M4" s="1">
 <v>2720</v>
 </c>
-<c r="N4" s="1">
 <f>SUM(Table1[[#This Row],[Year]:[Dec]])</f>
 <v>70538</v>
 </c>
 </row>
-<row r="5" spans="1:14">
-<c r="A5" s="2">
 <v>2001</v>
 </c>
-<c r="B5" s="1">
 <v>6502</v>
 </c>
-<c r="C5" s="1">
 <v>6786</v>
 </c>
-<c r="D5" s="1">
 <v>397</v>
 </c>
-<c r="E5" s="1">
 <v>673</v>
 </c>
-<c r="F5" s="1">
 <v>171</v>
 </c>
-<c r="G5" s="1">
 <v>642</v>
 </c>
-<c r="H5" s="1">
 <v>3194</v>
 </c>
-<c r="I5" s="1">
 <v>9797</v>
 </c>
-<c r="J5" s="1">
 <v>356</v>
 </c>
-<c r="K5" s="1">
 <v>8515</v>
 </c>
-<c r="L5" s="1">
 <v>6670</v>
 </c>
-<c r="M5" s="1">
 <v>8893</v>
 </c>
-<c r="N5" s="1">
 <f>SUM(Table1[[#This Row],[Year]:[Dec]])</f>
 <v>54597</v>
 </c>
 </row>
-<row r="6" spans="1:14">
-<c r="A6" s="2">
 <v>2002</v>
 </c>
-<c r="B6" s="1">
 <v>6656</v>
 </c>
-<c r="C6" s="1">
 <v>3018</v>
 </c>
-<c r="D6" s="1">
 <v>5392</v>
 </c>
-<c r="E6" s="1">
 <v>5437</v>
 </c>
-<c r="F6" s="1">
 <v>9813</v>
 </c>
-<c r="G6" s="1">
 <v>7944</v>
 </c>
-<c r="H6" s="1">
 <v>5831</v>
 </c>
-<c r="I6" s="1">
 <v>4516</v>
 </c>
-<c r="J6" s="1">
 <v>7758</v>
 </c>
-<c r="K6" s="1">
 <v>7608</v>
 </c>
-<c r="L6" s="1">
 <v>1793</v>
 </c>
-<c r="M6" s="1">
 <v>9192</v>
 </c>
-<c r="N6" s="1">
 <f>SUM(Table1[[#This Row],[Year]:[Dec]])</f>
 <v>76960</v>
 </c>
 </row>
-<row r="7" spans="1:14">
-<c r="A7" s="2">
 <v>2003</v>
 </c>
-<c r="B7" s="1">
 <v>8951</v>
 </c>
-<c r="C7" s="1">
 <v>7954</v>
 </c>
-<c r="D7" s="1">
 <v>2820</v>
 </c>
-<c r="E7" s="1">
 <v>6241</v>
 </c>
-<c r="F7" s="1">
 <v>4270</v>
 </c>
-<c r="G7" s="1">
 <v>8163</v>
 </c>
-<c r="H7" s="1">
 <v>9049</v>
 </c>
-<c r="I7" s="1">
 <v>4286</v>
 </c>
-<c r="J7" s="1">
 <v>5572</v>
 </c>
-<c r="K7" s="1">
 <v>8940</v>
 </c>
-<c r="L7" s="1">
 <v>7292</v>
 </c>
-<c r="M7" s="1">
 <v>5533</v>
 </c>
-<c r="N7" s="1">
 <f>SUM(Table1[[#This Row],[Year]:[Dec]])</f>
 <v>81074</v>
 </c>
 </row>
-<row r="8" spans="1:14">
-<c r="A8" s="2">
 <v>2004</v>
 </c>
-<c r="B8" s="1">
 <v>4648</v>
 </c>
-<c r="C8" s="1">
 <v>8782</v>
 </c>
-<c r="D8" s="1">
 <v>9051</v>
 </c>
-<c r="E8" s="1">
 <v>8584</v>
 </c>
-<c r="F8" s="1">
 <v>2517</v>
 </c>
-<c r="G8" s="1">
 <v>4158</v>
 </c>
-<c r="H8" s="1">
 <v>3996</v>
 </c>
-<c r="I8" s="1">
 <v>8413</v>
 </c>
-<c r="J8" s="1">
 <v>4455</v>
 </c>
-<c r="K8" s="1">
 <v>8082</v>
 </c>
-<c r="L8" s="1">
 <v>6746</v>
 </c>
-<c r="M8" s="1">
 <v>3372</v>
 </c>
-<c r="N8" s="1">
 <f>SUM(Table1[[#This Row],[Year]:[Dec]])</f>
 <v>74808</v>
 </c>
 </row>
-<row r="9" spans="1:14">
-<c r="A9" s="2">
 <v>2005</v>
 </c>
-<c r="B9" s="1">
 <v>6521</v>
 </c>
-<c r="C9" s="1">
 <v>5937</v>
 </c>
-<c r="D9" s="1">
 <v>3750</v>
 </c>
-<c r="E9" s="1">
 <v>1111</v>
 </c>
-<c r="F9" s="1">
 <v>7682</v>
 </c>
-<c r="G9" s="1">
 <v>5074</v>
 </c>
-<c r="H9" s="1">
 <v>303</v>
 </c>
-<c r="I9" s="1">
 <v>9511</v>
 </c>
-<c r="J9" s="1">
 <v>3327</v>
 </c>
-<c r="K9" s="1">
 <v>5897</v>
 </c>
-<c r="L9" s="1">
 <v>3628</v>
 </c>
-<c r="M9" s="1">
 <v>1087</v>
 </c>
-<c r="N9" s="1">
 <f>SUM(Table1[[#This Row],[Year]:[Dec]])</f>
 <v>55833</v>
 </c>
 </row>
-<row r="10" spans="1:14">
-<c r="A10" s="2">
 <v>2006</v>
 </c>
-<c r="B10" s="1">
 <v>7390</v>
 </c>
-<c r="C10" s="1">
 <v>7715</v>
 </c>
-<c r="D10" s="1">
 <v>2889</v>
 </c>
-<c r="E10" s="1">
 <v>791</v>
 </c>
-<c r="F10" s="1">
 <v>431</v>
 </c>
-<c r="G10" s="1">
 <v>2430</v>
 </c>
-<c r="H10" s="1">
 <v>684</v>
 </c>
-<c r="I10" s="1">
 <v>6522</v>
 </c>
-<c r="J10" s="1">
 <v>4784</v<