Recent Files and Places Search

Posted on by Dick Kusleika

I love what Excel has done to the list of recently opened files. It went from up to nine files to up to 50 files. You can pin certain files so they never leave the list. Every item on the list has a keyboard shortcut. But is it every good enough? No. No it’s not.

I have my options set to show 50 recent files, buy they don’t all fit on the screen. And 50 is a bit much. In fact, 25 (the default setting) can be hard to look through. All of the recent files have keyboard shortcuts and all of the push pins for the files have keyboard shortcuts. By the time you get to about the 20th file, the keyboard shortcut is YY2. It gets a little unwieldy, I think.

What if we could filter the list by typing in a search box? What if indeed.

And I filter on 201303 (that’s the third quarter of 2013 for you non-accountants).

Now that’s a list I can get my head around. Let’s see how it works. First I fill the listboxes based on the Application.RecentFiles collection.

When the userform is initialized, this procedure is called without the argument. That ensures that every file and place gets picked. I loop through the RecentFiles collection and capture the file name (using Dir) and the folder. The file names and fullnames go into a 2d array. The paths go into a dictionary so I can get a unique list. I never used to use dictionaries because I didn’t like the reference dependency. But that’s just stupid because every computer I use will have Scripting Runtime installed. So now I’ve embraced them. If I could use For Each with a dictionary in a custom class module, I’d quit using collections altogether.

Recent files can have the same name, which causes a problem. I included a textbox at the bottom of the form so I could get the whole path. I didn’t want the whole path in the listbox because it looks too cluttered. When something is selected in the files listbox, the textbox gets updated with the full path and filename.

I’m using the Enter and Change events of the listbox to make sure that only a file or a place is selected and not both. I originally had a button for each listbox, but that seemed silly. Now I’m wondering if I should have files and places all in the same listbox.

The Open button’s Enabled property is updated based on what’s selected.

I start with disabling the button. Then if a place is selected, it becomes enabled. Places is a unique list, so there’s no conflicts that can happen or any other error checking that’s needed. If a file is selected, it gets a bit more complicated. You can’t have two files with the same name open at the same time, even if they are in different folders. I try to set a variable to an open workbook, wrapped in On Error. If wb is Nothing, that means there are no open workbooks with that same name, so I enable the Open button. If wb is something, I check to see if the Fullname is the same. If it is, I enable Open, but in reality Open will just activate the workbook. Finally, if there’s an open workbook with the same Name, but a different Fullname, I keep Open disabled and put a message in the textbox. It’s not a great system, but it’s what I’ve got so far.

The only other code of interest is actually opening the file or place.

If it’s a file, I do the same workbook variable trick to see if it’s already open. If it is, I activate it. I don’t have to worry about it being a different file with the same name, because if that was a problem, Open would be disabled. If it’s not open, open it. If it is open, activate it.

If it’s a place, I change the drive, change the directory, and show the File Open dialog. I just know that ChDrive is going to screw up at some point, like if I have UNC path in there. But this is just a first draft, so I’ll deal with those problems later.

Right now, I press Alt+F+R and look down the recent file list. If I don’t see the file, I ESC out and press Ctrl+O to navigate the old fashioned way. I’m thinking that this userform should replace all of that. I would press Ctrl+O and this userform would show up. I would start typing and the list gets filtered. If the list is filtered, I would click Open and it lets me navigate to the file – kind of like selecting a Recent Place that happens to be the current directory. That means I have to keep Open enabled all of the time and prevent workbook name conflicts some other way. Alternatively, I could just add another button to the form to navigate. So Ctrl+O to open the form then Alt+N to skip the recent part and just navigate to the file.

Update
I’ve been using this for a week and, with a few minor exceptions, it’s worked wonderfully. I added the Navigate button to go directly to File Open. And I assigned Ctrl+O to open this form. It’s a little disconcerting at first – expecting the file open dialog and getting this userform – but I quickly got used to it. I’ve only had to ‘navigate’ once and that’s because I wasn’t thinking. The folder I wanted was in the recent places list, but I tend to be thinking about file names when searching, not folders.

You can download URecent.zip

24 thoughts on “Recent Files and Places Search”

  1. I love the idea, but I’m getting an error on the Dir(rf.Path) when my recent file comes from sharepoint workspace.

  2. Hmmm, that stinks. I don’t have Sharepoint. What do the paths look like? I guess we’ll have to split the path rather than using Dir().

  3. Dick,
    To use a ‘for each’ loop for a dictionary inside of a custom class, just use (assuming pdct is the private name of the dictionary inside the class)

    Public Property Get Keys() As Variant()
    Keys = pdct.Keys
    End Property
    Public Property Get Items() As Variant()
    Items = pdct.Items
    End Property

  4. It also seems not to give me the ‘Read Only’ prompt when someone else has it open. It just opens it read-only.

  6. I liked the idea of filling a listbox with just the dictionary keys array so much that I decided to start to use dictionaries rather than collections (again) – only to find that the first time I tried to use dictionaries, I wanted to create the list in a sorted order and dictionaries do not allow the before and after keyword – bummer – back to collections for me unless someone knows a slick workaround

  7. Dictionaries are just arrays under the covers. There should be a Sort method right in the class. But alas, you’re right. The only thing I’ve ever seen on it is to dump it to an array, sort it, and put it back. Blech.

  8. If you want the list to be sorted you should use another library:

    Public Sub FillFilesAndPlaces(Optional sFilter As String = vbNullString)
    With CreateObject("System.Collections.ArrayList")
    For Each rf In Application.RecentFiles
    If Dir(rf.Path) <> "" And InStr(rf.Name, sFilter) Then .Add rf.Path
    Next

    .Sort
    If .Count > 0 Then
    lbxPlaces.List = Application.Transpose(.toarray())
    lbxFiles.List = lbxPlaces.List
    For j = 0 To UBound(lbxFiles.List)
    lbxFiles.List(j) = Dir(lbxFiles.List(j))
    Next
    Else
    lbxPlaces.Clear
    lbxFiles.Clear
    End If
    End With
    End Sub

  11. I also had the SharePoint files issue… whenever I expanded my list of rf’s to include one SharePoint file (that happens to not exist anymore anyway) I’d get Error 53 File Not Found. I threw a little error capture like this:

    and everything worked fine. Not very elegant, but…

    I’m sure the unusable files will get written off of the list eventually.

  12. I also had problems with the cmdOpen_Click sub. If the file was on a network the ChDrive line would error out. I changed the code a little to use FileDialog:

    Problem solved.

  13. Now my problem is that 50 recent files isn’t nearly enough. I’ve made some updates to be posted soon, but now I’m thinking I need to maintain my own recent files list with, I don’t know, no limit. That can’t work, can it?

  14. Yes, like
    Sub M_snb()
    MsgBox Replace(CreateObject("wscript.shell").exec("cmd /c Dir """ & CreateObject("wscript.shell").specialfolders(13) & """\*.xls* /b").stdout.readall, ".lnk", "")
    End Sub

  15. Nice code. I can’t figure out how many files windows keeps. It appears to be 31 days on my machine. And the Excel recent files and Windows recent files oddly don’t mesh.

  16. There’s also
    Sub M_snb()
    MsgBox CreateObject("wscript.shell").exec("cmd /c dir """ & CreateObject("wscript.shell").specialfolders(5) & "\Microsoft\Office\Recent\*.*" & """ /b").stdout.readall
    End Sub

    But I have no idea where Excel stores the 'recent files'

  17. It appears to be in the registry

  18. Yes I found that just now:

    Sub M_register()
    For j = 1 To 50
    c00 = c00 & vbLf & Split(CreateObject("Wscript.shell").regread("HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\File MRU\Item " & j), "*")(1)
    Next

    MsgBox c00
    End Sub

  19. Well, I think combining those three would be better than keeping my own list. I’m still not convinced it’s a complete history.

    If I did keep my own list, should I keep it in the registry? What’s a realistic limit for how much data to store in the registry? If I kept 1,000 files in there would I be labeled a registry hog?

  20. I have no idea what happens if you add an item 51, item 52 etc. in the registry.
    But I would prefer to load the registry as little as possible.

  21. I love this form! Now I’m Finally able to see more than 20 or 30 recent files.
    The Dir() commands did work for me, although quite slowly. For each letter I typed in the Search box, it takes a second to load.

    I’ve changed the lines
    sFile = Dir(rf.Name); and
    sPlace = Replace(rf.Path, Dir(rf.Path), vbNullString)

    with

    sFile = Right(rf.Name, Len(rf.Name) – InStrRev(rf.Name, “\”)); and
    sPlace = Replace(rf.Path, sFile, vbNullString)

    which is much faster. Now I’m wondering if my work around is more error prone…

  22. No, I don’t think it’s more error prone. The only advantage to Dir is that returns a zero length string if the file doesn’t exist. I have an updated version almost ready. I’ll be interested if that’s slow and, if so, maybe I should reduce the use of Dir.

  23. Hey Dick…I’d like to try this out. When I click on the download link, I get a couple of files called URecent.frm and URecent.frx . What do I do with those?

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.