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>
 </c>
-<c r="K10" s="1">
 <v>2501</v>
 </c>
-<c r="L10" s="1">
 <v>2976</v>
 </c>
-<c r="M10" s="1">
 <v>2771</v>
 </c>
-<c r="N10" s="1">
 <f>SUM(Table1[[#This Row],[Year]:[Dec]])</f>
 <v>43890</v>
 </c>
 </row>
-<row r="11" spans="1:14">
-<c r="A11" s="2">
 <v>2007</v>
 </c>
-<c r="B11" s="1">
 <v>4057</v>
 </c>
-<c r="C11" s="1">
 <v>6927</v>
 </c>
-<c r="D11" s="1">
 <v>6108</v>
 </c>
-<c r="E11" s="1">
 <v>9970</v>
 </c>
-<c r="F11" s="1">
 <v>1271</v>
 </c>
-<c r="G11" s="1">
 <v>8788</v>
 </c>
-<c r="H11" s="1">
 <v>5890</v>
 </c>
-<c r="I11" s="1">
 <v>6816</v>
 </c>
-<c r="J11" s="1">
 <v>2361</v>
 </c>
-<c r="K11" s="1">
 <v>6558</v>
 </c>
-<c r="L11" s="1">
 <v>4594</v>
 </c>
-<c r="M11" s="1">
 <v>4850</v>
 </c>
-<c r="N11" s="1">
 <f>SUM(Table1[[#This Row],[Year]:[Dec]])</f>
 <v>70197</v>
 </c>
 </row>
 </sheetData>
-<conditionalFormatting sqref="N2:N11">
-<cfRule type="dataBar" priority="1">
-<dataBar>
 <cfvo type="min" val="0"/>
 <cfvo type="max" val="0"/>
 <color rgb="FFD6007B"/>
 </dataBar>
 </cfRule>
 </conditionalFormatting>
 <pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>
 <pageSetup orientation="portrait" r:id="rId1"/>
 <drawing r:id="rId2"/>
 <legacyDrawing r:id="rId3"/>
-<tableParts count="1">
 <tablePart r:id="rId4"/>
 </tableParts>
 </worksheet>

 

That's probably all I want to force in this time and we haven't really done anything with the Ribbon, but this grounding is very important and will hold you in great stead for the topics to come.

One final thing, as with most development, we could re-write these by hand, in Notepad or similar, but there are tools available, specifically for the task and for customising the Ribbon, we will be using the Office 2007 Custom UI Editor, you can download it now, for free and be ready for that topic to come next. (To use this tool you will need at least the .NET framework 2.0 loaded on your development machine.

Until next time...

Posted Jan 30 2008, 09:39 PM by Nick Hodge
Filed under: , , ,

Comments

hrlngrv wrote re: Ribbon, Step-by-Step. Part 1 (File Formats)
on Wed, Jan 30 2008 6:38 PM

You do make a persuasive case that the ribbon is an attractive sow's ear to those who fancy sows' ears. To the rest of us, it's still a sow's ear.

Nick Hodge wrote re: Ribbon, Step-by-Step. Part 1 (File Formats)
on Thu, Jan 31 2008 3:40 AM

Harlan

How did I know that was coming ;-)

I worked in the British Motorcycle industry when people were saying we had the best motorcycles in the World and nothing needed to change. Japanese stuff was rubbish when it first appeared, but now look at them.

Change is inevitable, good and bad. This sure is change and nowhere near all of it good, we agree there, but if we see this as V1.2, then with MSFT's money, likelyhood is, the silk purse will appear over time.

I am just trying to get people looking at the possibilities, in my own little way, hoping that v.next will offer far greater possibilities.

Harlan Grove wrote re: Ribbon, Step-by-Step. Part 1 (File Formats)
on Thu, Jan 31 2008 2:48 PM

Where I work (a large financial services company), IT upgraded me to Office 2003 in mid-2003, and only last year made sure everyone in the company had upgraded to the 'standard platform', which includes Office 2003. Most of us had been using Office 97 before that, so 5-6 years using it (since IT wasn't installing Office 97 before 2000). 5-6 years using Office 2003 would be about right. Next upgrade between 2011 and 2012, so maybe I'll be lucky enough to miss both Excel 2007 and Excel 14. Perhaps by then Microsoft will have figured out something a bit more useful.

The most heavily used workbooks in my unit involve Excel running under a VB.Net front-end. Only Excel's document window shows through. None of Excel's UI, not even right-clicking (but, interestingly, [Alt]+[F11] pops up VBE). If more applications move toward VB.Net front-ends, the ribbon will become a non-event.

Part of what irks is that I went through the transition to Gnome 2.0 under Linux when its equivalent of Windows' Start Menu changed to an XML implementation. The Gnome team didn't provide a menu editor either, so lots of people gained experience screwing up their menus by editing the XML menu layout file. It was a very stupid decision (or act of indifference or arrogance) by the Gnome team back then, and it's just as stupid/indifferent/arrogant for Microsoft to have done the same thing with Office 2007.

That said, I find the ribbon itself less objectionable than the stupid oversized Office logo button. As a replacement for the File menu it's stupendously nonobvious.

Believe me, I wish Microsoft all the success with the ribbon that Coca Cola had with New Coke. Change may be inevitable, but some forms of change are mistakes.

Nick Hodge wrote re: Ribbon, Step-by-Step. Part 1 (File Formats)
on Thu, Jan 31 2008 3:15 PM

What really makes me laugh about the Office button, is that it was so 'hidden' that when you first open Excel and registry key is set that now flashes it!

One wish I have with the office button is that you could replace the icon, but that's only because our company has a round logo.

Nick Hodge's Excel Blog wrote Ribbon, Step-by-Step. Part 2 (Begin Customisation)
on Thu, Jan 31 2008 6:52 PM

In the last part of this series , we went through the different file formats as a precursor to starting

open xlsx files in excel 2000 wrote open xlsx files in excel 2000
on Sat, May 31 2008 6:55 AM

Pingback from  open xlsx files in excel 2000

EXCEL 2007 OPENING IN COMPATIBILITY MODE | keyongtech wrote EXCEL 2007 OPENING IN COMPATIBILITY MODE | keyongtech
on Sun, Jan 18 2009 11:45 AM

Pingback from  EXCEL 2007 OPENING IN COMPATIBILITY MODE | keyongtech

Copyright Excel User Group and the relevant contributors, 2009. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.