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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
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
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?
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
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.
Dear Mr. Kusleika,
This xlam file looks very promising. I also saw your post on http://dailydoseofexcel.com/archives/2013/08/06/recent-files-and-places-search/
I got that form to work, but for some reason I cannot get the .xlam file to work.
What should I do to use it?
Best regards,
Richard
@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.