Pin Me Up, Pin Me Down
27 April 2011 3 Comments
(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 use1 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 registry2. 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.
1 Inevitably, there will be any number of comments telling me precisely where I should have looked.
2 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.
Grumpy Old Programmer 