Identify Pinned RecentFiles in (Excel 2007) VBA or other?

Question

Is there a way, other than scanning the registry, to determine that a RecentFile object is pinned to the "Recent Documents" list? From VBA for preference, something that can live in an add-in.

Background: I'm looking to persuade Excel to "float" pinned items to the top (or bottom) of the list, which could be achieved by re-Adding them to RecentFiles, if only I could identify the items of interest.

I can see in the Registry (within HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\File MRU) that the pinned item is identified there, e.g.:

[F00000001][T01CC04D632020F50]*C:\Files\ThisOneIsPinned.xlsx
[F00000000][T01CBFB8F14408960]*C:\Files\ThisOneIsNot.xlsx

... so that's a way to approach it, I suppose. I'd prefer something a little less, er, exciting..

Answer 1

To access the recentfile list in VBA use

Sub test()
Dim objAllRecentFiles As Object
  Set objAllRecentFiles = Application.recentfiles
End Sub

I don't believe there is an alternative way other than using the registry to determine if recent files are pinned or not. The example below allows you to cycle through all recent files, determine those that are not pinned and change registry to make them pinned. Commented out is a way to delete

Sub test2()
Dim objAllRecentFiles As Object
Dim WSHShell, RegKey, rKeyWord
Set WSHShell = CreateObject("WScript.Shell")
Set objAllRecentFiles = Application.recentfiles

RegKey = "HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\File MRU\"

For Each rFile In objAllRecentFiles

    rKeyWord = WSHShell.RegRead(RegKey & "Item " & rFile.Index)
        If InStr(1, rKeyWord, "[F00000000]") Then

        'Delete registry
        'rFile.Delete

        'Change registry setting to make recent file pinned
        strPinned = Replace(rKeyWord, "[F00000000]", "[F00000001]")
        WSHShell.Regwrite (RegKey & "Item " & rFile.Index), strPinned, "REG_SZ"

        End If
Next rFile

End Sub