I wrote a KwikOpen addin that I use about a million times a day. I ran into a little nagging problem. When I Save As’d a file from the addin, it never showed up on the recently opened list. I finally decided to track down the bug. A while back, I switched my custom class storage method from Collection to Dictionary. I don’t remember why, but I’m sure it was a fine reason. I ended up with this Add method
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
Public Sub Add(clsRcntFile As CRcntFile, Optional ByVal bToTop As Boolean = False) ' If clsRcntFile.RcntFileID = 0 Then ' clsRcntFile.RcntFileID = Me.Count + 1 ' End If If Not mdcRcntFiles.Exists(clsRcntFile.FullName) Then mdcRcntFiles.Add clsRcntFile.FullName, clsRcntFile End If 'Set clsRcntFile.Parent = Me ' If bToTop Then ' ' mcolRcntFiles.Add clsRcntFile, CStr(clsRcntFile.RcntFileID), 1 ' Else ' mcolRcntFiles.Add clsRcntFile, CStr(clsRcntFile.RcntFileID) ' End If ' End Sub |
I have this optional argument, bToTop, so I can add it to the front of the list. But as you can see from the commented code at the bottom, that argument is basically ignored. Dictionaries don’t allow you to insert values into specific locations and that code no longer works.
So why a bug? Because I only store the most recent 2,000 files, and I’m at that limit, any Save As’d file would become 2,001 and not written to disk. When I’d go to open a file, it would read in from the file again and, of course, that recently saved file was not there.
Surely there’s a quick and easy method for pushing something to the top. Nope. All I could find was rewriting the whole Dictionary.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
Public Sub Add(clsRcntFile As CRcntFile, Optional ByVal bToTop As Boolean = False) Dim dcTemp As Scripting.Dictionary Dim i As Long If Not mdcRcntFiles.Exists(clsRcntFile.FullName) Then If bToTop Then Set dcTemp = New Scripting.Dictionary dcTemp.Add clsRcntFile.FullName, clsRcntFile For i = 0 To mdcRcntFiles.Count - 1 dcTemp.Add mdcRcntFiles.Keys(i), mdcRcntFiles.Items(i) Next i Set mdcRcntFiles = dcTemp Else mdcRcntFiles.Add clsRcntFile.FullName, clsRcntFile End If End If End Sub |
In that code, I create a temporary Dictionary, dcTemp, put my Save As’d file in first, then fill in the rest, finally replacing the old Dictionary with the temporary one. That’s not exactly elegant, but it gets the job done. I tested it and found that the recently saved file was not on the top of the list. It was near the top, but I inserted it first, it should be at the top. Then I remembered that I read in Excel’s MRU before I read in my file. That means there are 50 files ahead of the one I just saved. No biggie, but it gave me an idea.
Instead of recreating the Dictionary, why don’t I just add it to the MRU? There are some websites about adding entries to the registry but that won’t work. Excel reads the registry when it opens and I wasn’t about to close and reopen the app. Another way to add a file to the MRU are to specify the arguments in the Open and SaveAs methods. I am saving a file. Now my Add method looks like this
|
1 2 3 4 5 6 7 |
Public Sub Add(clsRcntFile As CRcntFile) If Not mdcRcntFiles.Exists(clsRcntFile.FullName) Then mdcRcntFiles.Add clsRcntFile.FullName, clsRcntFile End If End Sub |
The heavy lifting is done when I save the file
|
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 |
Dim fd As FileDialog Dim clsRcntFile As CRcntFile Dim clsError As CError If Not gbDebug Then On Error GoTo ErrHandler Set clsError = New CError: clsError.SetLoc "USaveAs", "cmdOpen_Click" Set fd = Application.FileDialog(msoFileDialogSaveAs) fd.InitialFileName = Me.lbxPlaces.Value & ActiveWorkbook.Name Select Case ActiveWorkbook.FileFormat Case 50: fd.FilterIndex = 3 Case 52: fd.FilterIndex = 2 Case 56: fd.FilterIndex = 4 Case Else: fd.FilterIndex = 1 End Select fd.Show 'fd.Execute If fd.SelectedItems.Count > 0 Then Application.DisplayAlerts = False ActiveWorkbook.SaveAs fd.SelectedItems(1), , , , , , , , True Application.DisplayAlerts = True ' Set clsRcntFile = New CRcntFile ' clsRcntFile.FullName = fd.SelectedItems(1) ' Me.RcntFiles.Add clsRcntFile, True End If |
That lone True out there is the AddToMru argument. By getting rid of the .Execute method and doing the SaveAs myself, I also got rid of a problem where overwriting an existing file caused two warning prompts. Now there’s no need for me to add it to my list (the commented out code at the bottom) because Excel adds it to its list and that’s what I read first.
I can think of a few possible reasons:
Dictionary keys can be almost any data type, while collection keys are only strings
Dictionaries allow you to make string keys case sensitive, if you’re into that sort of thing
A dictionary object gives you access to the keys as well as the items. A collection object does not give you access to the keys–you have to already “know” what they are
Dictionaries can be faster than collections
:)
would you be able to post an updated version of the XLAM file you posted a (long) while ago?
Thank you.
Joe
Northville, MI
Here you go Joe
Dick – that’s an XLSM with no forms in it. Is this what you intended to post?
Nope, that’s not right at all. I just uploaded the right one, so the link should work now. Thanks for checking that.
Thanks for that. I see from the code that you run it with Ctrl + O (for Open) and Ctrl + Shift + S (for Save). If I run the Open routine, and hit Close straight away, I get a “File not found” error on UserForm_QueryClose . I get the same error if I use the Navigate button, once I dismiss the native dialog.
This looks really handy. What would be cool is if when you double-click on a folder in the Recent Places pane you could drill down somehow to see the subfolders and files there. I guess a TreeMap would be the only way to handle that kind of drilldown.
The error is in the class, but you need to use Tools – Options – Break in Class Module otherwise the VBE just breaks that the class call. No doubt, though, the error is because the mru file doesn’t exist yet.
It doesn’t have a proper installer so there is no recently used file – you have to create it manually. It wouldn’t be that hard to create it if it doesn’t exist, but I haven’t really spent any time on making this user friendly from an installation standpoint. There is a sharepoint problem that prevents me from releasing more broadly.
If you put a file named KwikOpenFiles.txt in your my docs folder, that should take care of the error.
Thank you for the cool tool!