17 thoughts on “Dynamic Favorites”

1. Much simpler might be the ability to specify a favourite in the form pathfile*.xls, then VBA could load the latest file in that path which matched that string. This would seem to do all of the above without needing to specify any parameters at all.

2. Maybe I am missing something but…Wouldn’t it be a lot simpler to open the folder of interest and double-click on the file of interest?

3. Per Tushar’s comment, I keep my shortcuts to key favorite folders in the Quick Link bar. In addition, my favorite folders always have an “Archive” subfolder where last month’s (and older) file lives. My favorite folders have only the most current working files in their root.

   I also have a directory of links to favorite working folders.

4. dbb: Good point. I’m not sure I can get away with an asterisk because I would need to determine the latest date in the file name. One file name might be 20081231 and another 12312008. Should I just try all common combinations or should I have the use tell me the format. Definitely using the latest date works, though. At least in every one of my cases.

   Tushar: Like with a mouse? Have we met?

   Me opening a favorite – Alt+F+F+E time 100ms

   Me opening the same file via folder – Windows+E, Down arrow x 4, Right arrow, Down arrow, right arrow, down arrow, right arrow, R, right arrow, tab, C, Enter

   With a mouse – Windows+E, Click * 6, Scroll wheel * 2, Double click, spend five seconds complaining about the mouse

   AlexJ: Are you the only one that works on these files? I think my work mates might be a little ticked if I kept moving files around.

5. If the favorite file will always have a root name, then an alternative might be to save the file root name as a favorite.

   Then, on the other end, when you click on it to open the file, have VBA do a quick iteration to pick up any instance of a file containing the root name, throw them into a listbox, and then double-click on the one you want.

   So, if the file will always be InvRec[something.something.something], then the code would save the favorite as InvRec, and the retrieval would cycle through all files in the folder that have InvRec in the file name. Then add them to a popup listbox and you choose.

6. Dick wrote:

   Tushar: Like with a mouse? Have we met?

   LOL! So very true! I apologize for forgetting about your TMS. {grin}

7. Scott How about a fly-out menu instead of a listbox. You say InvRec*.xls, you get this

   

8. I have a macro that auto-creates a new version of my files by incrementing the version number in the filename (“my file v1.02.xlsb”). The file is added to the RecentFiles collection.
   In Excel 2007 you can ‘pin’ a recent file to the list, but I can’t find how I can pin the file from VBA.
   any ideas?

9. I have a macro that auto-increments the file version number (in the filename, like ‘my file v1.02.xslb’) and adds it to the RecentFiles collection.
   In Excel 2007 you can ‘pin’ a file in the recent documents list, any idea on how you can do this from VBA?

10. oops, sorry for double posting. Received an error, something like ‘post could not be found’ and posted again but it seemed it got through the first time.

11. Jurgen: I don’t think you can do it from VBA. Maybe next version. :) (I doubt it)

12. Oh, now that’s nifty! Didn’t occur to me to use menus to control it. I like it! Do you specify each individual file, or does it loop and add all the ones containing the “root”? And what does “Manage” do? Open up the original form you’d built?

    Do you think it works better from the top menu bar or from the shortcut menu? Shortcut menu might make it even quicker to access.

    Do you think you’ll run into any menu item restrictions with this? I don’t know how many favorites you have, but if you end up with, say, 50 files (ridiculous thought, I know) that are favorites, does it get too hard to manager/work with them from a menu?

    (Maybe I’m not helping with all of these questions…)

13. Jurgen –

    You can’t pin a file to the MRU list, but you could certainly add a menu item above the MRU list for a workbook.

14. Scott: For that example I hardcoded the file names, but in the real implementation it will loop through the folder and find the matching files dynamically. I think I’d do it at startup only. Newly added files wouldn’t show, but I wouldn’t be able to stand a one second delay in my favorites menu.

    Right now “Manage” doesn’t do anything. :) But someday it will open a userform that will let you add/delete/reorder everything. I wanted to avoid the Manage aspect and just have add and delete, but I think people want the option even if they don’t use it.

    I’m always hesitant to put something on the top menu. It seem presumptuous for me to think that my utility is so important that it deserves the same billing as File and Edit. But Walkenbach does it with PUP, so maybe it’s OK (or maybe PUP is that important). I definitely like the shortcut menu option.

    Yeah, 50 frequently used files is probably a stretch on the word “frequently”. I was going to limit the list to 26 entries (one for each letter of the alphabet). I’ve reconsidered. I think I’ll make it unlimited, but only provide shortcuts for the first 26.

    I also want to implement nesting, which would be done via the Manage userform. That way someone could have 100 files and put them into subfolders.

    I love the questions, keep ’em coming.

15. In a way, the thing to emulate (and I hesitate to say it) might be the way IE handles favorites. The menu option (Top or Shortcut, either way) just lists the favorites in order of addition, unless you want to create specific folders to house them. It’s convienent and (most) IE users already know how to Manage their browser favorites, so maybe constructing this in the same way might be a way to go.

    The (big) challenge that I can see is how you specify which files go in which folders. Since the folder don’t neccesarily need to be directory folders (it could just be “Scott’s Folder of Junk”), how do you create them so that they are not tied to a directory, yet contain the files’ directories so that you can open them?

    I’m thinking you would want to use the registry and simply create (or delete) folders as you “manage” the favorites. You could also store a sequence number in there, which would sequence and allow you to re-sequence favorites as you see fit.

    Then again, all this might be WAY more work then you really want to put into this project… :-)

16. Scott: That’s kind of what I had in mind. It would add new favorites to the bottom, but give you a userform to “manage” favorites. A top-level menu would be consistent with IE, but a File level menu would be consistent with the Most Recently Used less. I’m not sure which is more intuitive.

    I’ll probably store all the settings in an ini or xml file. No, the “folders” wouldn’t be tied to file-system folders, they would only exists in my app. I’m not sure what the file format will look like, but I’ll definitely need some way to order them so the user can change the order. Either I’ll sequence them or I’ll just re-write the file in the correct order every time.

17. And if you want to get REALLY fancy, and presuming you’re “managing” these with a listbox, you can finally find a use for Harold’s listbox Drag n Drop code!

---

Posting code? Use <pre> tags for VBA and <code> tags for inline.