Pin Me Up, Pin Me Down

(in which we return to VBA for a Small But Useful macro-writing session)

My friend Jens comes up with interesting questions. Even better, he doesn’t come up with them too often.

Today’s poser refers to Excel 2007, a technological marvel that has only reached our work PCs relatively recently. Jens had discovered the Usefulness that is the “pin” tool on the “Recent Documents” list:

So the pinned documents don’t go away, but they do move around on the list, rising up when used, drifting down as lesser workbooks are opened. What Jens wanted was to have his pinned files stay at the top. Or the bottom. Either, really, providing they hung around together.

We couldn’t find anything in Options, Advanced or otherwise, and some Googling didn’t turn up much of use¹ and at time of writing (admittedly not too long ago) I hadn’t got any useful feedback on Stackoverflow. Time to apply a different set of talents.

Excel has Application.RecentFiles, which does pretty much what it suggests. Even better, the “.Add” method, when called with a file that’s already on the list, promotes that file to the top. If we could call that method for the pinned files, we’d be laughing. If we could identify them. It turns out we can.

Excel has to store the list somewhere so it can find it when restarting. Where do “modern” Windows programs store that kind of information? The registry². Specifically, “HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\File MRU”, where MRU stands, most likely, for Most Recently Used. My registry has 50 entries here, probably reflecting that Advanced Excel Options allows me to display up to 50 documents, screen real estate permitting.

So looking at the entries, we see that there’s one string for each of our MRU entries and that each string has some structure to it:

That first part: “[F00000001]”, is particularly interesting: the last digit is a “1” for all the files we’ve marked as pinned. So we can identify pinned files. All we need now is to be able to read the registry from VBA. We can’t use “GetSetting” because it’s limited in scope. More Googling and we discover that the Windows Scripting Host knows about registries so we add a reference to “Windows Script Host Object model” in order to be able to early-bind to WshShell.

So here’s the first attempt. Probably heaps of unhandled situations, but right now it works on my machine…

Public Sub PromotePinnedFiles()

Const KEY_ROOT = "HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\File MRU\Item "

Dim itemIndex As Long
Dim itemData As String
Dim itemPath As String

On Error Resume Next ' general-purpose error-ignoring

With New WshShell ' Or use CreateObject("WScript.Shell") if reference not set
 For itemIndex = 1 To 50 ' I think 50 is the most Excel will store
  itemData = .RegRead(KEY_ROOT & itemIndex) ' get the item (ignoring errors if fewer than 50 present)
  If Len(itemData) > 0 Then
   If InStr(itemData, "*") > 0 Then
    If Mid(itemData, 10, 1) = "1" Then
     itemPath = Mid(itemData, InStr(itemData, "*") + 1)
     Application.RecentFiles.Add itemPath ' "promotes" the item to #1 in the list
    End If
   End If
  End If
 Next
End With

End Sub

The effect of which is this:

It just remains to decide how best to package it for my learned colleague.

---

¹ Inevitably, there will be any number of comments telling me precisely where I should have looked.
² Readers who answered “INI files” are invited to remove themselves to the back of the classroom, where the machines are still running Excel 3.0.

Tie an Office 2007 Ribbon…

Credit’s still a-crunching in bank-land, although for once it’s just possible that I find myself in a relatively secure part. But I’ve felt secure in the face of crisis before: wrongly as it transpired, so we’ll see.

I can’t say I was ever much of a fan of the rather clunky way in which we used VBA to add and extend menus, toolbars and (in particular) categorise user functions (who’d have thought we’d still be calling Excel 4 macros in the 21st century?).

But xlUnit needs to expose its (minimal) UI into Excel 2007 in an appropriate fashion, so I need to bite the bullet and figure out how to tie me a ribbon. in Office Fluent UI. Gulp.

It’s times like these that turn a grumpy old man’s thoughts to How-tos. I need some step-by-step FLuent UI For Dummies education. Google time. Oho – MSDN have Office 2007 Visual How Tos. How convenient. Time to save a draft and peruse…

Let’s start with Adding Custom Buttons to the 2007 Office Fluent User Interface. Ooh, a video. Doesn’t work. And the XML is nonsense. And to be honest, for the first time in well over a decade I don’t actually have a Microsoft development environment loaded on my laptop – and I don’t seem to be missing it. There must be an easier way. Back to Google.

Now this is more like it. Run a little utility to insert the XML – and the example code is valid this time – and hook up to a VBA routine. Not only that, but it explains how the process works so that you can work without the utility completely.

WIth the buttons and bows taken care of, I’d better take a step back and figure out what’s got to go on them…