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
