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

5 thoughts on “KwikOpen Addin

  1. 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. 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.


  3. 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.

  4. @Richard. Unzip it an place the xlam file anywhere. In Excel, File – Excel Options – Addins – Manage Excel Addins – Go. Browse to the file and install the add-in. Once installed, Ctrl+O should open the custom File Open dialog.

Leave a Reply

Your email address will not be published. Required fields are marked *