KwikOpen Addin

I’ve been using this recent files userform for quite a while now and I like it. I haven’t added Eric’s comment yet (and for no good reason), but I’m going to in the next version. I don’t know if that solves the Sharepoint problem or just the ChDrive problem as neither are problems for me.

Here are my two problems:
Not enough recent files. I’m shocked – SHOCKED – at how often 50 recent files is not enough. It’s usually when I have to open a whole bunch of very similar files that I will never open again, but that clean out my recent files list. I decided to, sort of, maintain my own list. Because I use class modules *ahem* changing things to maintain my own list was pretty easy. I had to change how I fill the CRcntFiles class, but everything that consumes that class downstream just works. Here’s the new Fill method in CRnctFiles.

Public Sub Fill()
   
    Dim rf As RecentFile
    Dim clsRcntFile As CRcntFile
    Dim sFile As String, lFile As Long
    Dim vaFiles As Variant
    Dim i As Long
   
    For Each rf In Application.RecentFiles
        Set clsRcntFile = New CRcntFile
        clsRcntFile.FullName = rf.Path
        Me.Add clsRcntFile
    Next rf
   
    sFile = ThisWorkbook.Path & Application.PathSeparator & msMRUFILE
    lFile = FreeFile
    Open sFile For Input As lFile
   
    vaFiles = Split(Input$(LOF(lFile), lFile), vbNewLine)
   
    Close lFile
   
    For i = LBound(vaFiles) To UBound(vaFiles)
        If Len(vaFiles(i)) > 0 Then
            Set clsRcntFile = Nothing
            Set clsRcntFile = Me.RcntFileByFullName(vaFiles(i))
           
            If clsRcntFile Is Nothing Then
                Set clsRcntFile = New CRcntFile
                clsRcntFile.FullName = vaFiles(i)
                Me.Add clsRcntFile
            End If
        End If
    Next i
       
End Sub

First, I read in the 50 Excel most recently used files. Then I read in the 1,000 most recently used files that I store in a text file, weeding out the duplicates as I go. The advantage of continuing to use the Excel MRU is that I can leverage its pinning feature. I don’t have to write my own pinning bullshit – if you want to pin something, do it via Excel and it will always be in the MRU. Awesome.

Why 1,000 files? I don’t know. We’ll see how the performance holds up. I’ve been using it for three days and my text file is only up to 58 files – the 50 Excel stores plus eight additional. I guess it will take a bit longer to get to 1,000 than I thought, but I think it will be clear when their are too many and I can pare it down.

Next I need a way to write the files back to the text file. When the userform closes, the CRcntFiles.WriteToDisk method is called.

Public Sub WriteToDisk()
   
    Dim sFile As String
    Dim lFile As Long
    Dim clsRcntFile As CRcntFile
    Dim aFiles(1 To 1000) As String
    Dim lCnt As Long
   
    lCnt = 0
    For Each clsRcntFile In Me
        lCnt = lCnt + 1
        If lCnt > UBound(aFiles) Then Exit For
        aFiles(lCnt) = clsRcntFile.FullName
    Next clsRcntFile
   
    sFile = ThisWorkbook.Path & Application.PathSeparator & msMRUFILE
    lFile = FreeFile
   
    Open sFile For Output As lFile
    Print #lFile, Join(aFiles, vbNewLine)
    Close lFile
   
End Sub

I lazily write 1,000 lines to the disk even if I don’t have that many. I mean efficiently, not lazily. The text file is 6KB, so I’m not losing sleep over it. I would be pretty trivial to Redim Preserve that after I’ve filled it up, so I supposed I’ll do that after the alpha test.

And other than a few minor tweaks, that’s the only changes I had to make. If that’s not a case for using class modules, I don’t know what is. My userform consumes a CRcntFiles class. It doesn’t care how that class gets filled up or where the list comes from. I could change to storing those recent files in the registry, in an XML file, or tattooed to my back. As long as I can get them into a CRcntFiles instance, the rest of the code is happy.

Save As is jealous of Open. My next problem is that while I can quickly open a recent file, I can’t quickly save a file to a recent place. This is primarily a problem when I open attachments in Outlook. It stores an opened attachment in the Temp folder and when I choose Save As, that’s the folder it starts me in. Nuts to that. If you download this add-in, you’ll also see that I’ve hooked up a SaveAs userform to Ctrl+Shift+S. It’s got a few problems too (it prompts to replace a file twice), but you can try it if you like.

You can download KwikOpen.zip

3 Comments

  1. jkpieterse says:

    Hi Dick,

    Sounds useful. You might go for a treeview and group similarly named files (default to collapse) to overcome the “Opened lots of similarly named files” problem.
    Also, perhaps rather than having a numeric limit, a limit on how long ago a file was accessed is a way to manage the # of entries?

  2. Charlie says:

    Hi Dick,

    Never noticed the PinThis icons in Open Recent – thanks for sharing that one.

    The double prompt during SaveAs may be because you are not setting Cancel to true after you run your own SaveAs – just a guess since I was too lazy to download the workbook.

    –Charlie

  3. Dick Kusleika says:

    After 8 days, only 78 files. I should have date stamped them to see how fast it grows or if it grows smoothly or in fits and starts.

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: