List of ALL files opened by excel

[jaschro]

It's great that Excel now gives you up to 50 recently used files. This is up from 9. But I find that even 50 isn't enough and I'd like to have a list of all files opened. I just spent an hour tonight trying to find a file that I opened three days ago but couldn't remember where on my hard drive I had it stored.

Basically, each time that Excel opens a file, I'd like it to make an entry in another workbook that is a list with three columns:

Column 1: timestamp in Excel date/time format.
Column 2: Filename
Column 3: Full path with filename, with a hyperlink.

I wrote something like this years ago as a VBA class module, so I know that it's possible, but I've forgotten how to write class module VBA macros on auto-open.

Ideally, the macro could write out to a different file (call it "FilesOpened.xlsx") or simply make an entry in an excel sheet within a personal.xls macro workbook.

 

[svendiamond]

I'd like to try to offer a (possibly) easier solution: open windows explorer and instead of going through all the folders, just search for *.xls or *.xlsm to find all excel files... then sort by date modified. What do you think?

 

[jaschro]

Thanks for the suggestion. That might work if the files were all on my local machine, but I'm constantly connecting to different servers. Sometimes at home, sometimes at work, sometimes dropbox, box, onedrive. Files are in lots of places. There's probably also a registry tweak but I'd like to avoid that.