Autosort the document recovery pane by date (descending)
The document recovery window can really be a life saver, but it can also be extremely frustrating. However the fix for this seems like it would be mind-numbingly easy for one of your office developers.
Please update the document recovery view so the files are sorted from most recent to oldest in the window. Today there is absolutely no logic to how the files appear in this window, and trying to find documents that I've worked with today can take a while if you aren't always clearing your recovery pane. I generally don't erase my recovery pane, in case I discover I will need one of those file later. Then when I do need to file, say excel just crashed, I'm scrolling through 200 files. Some from today are near the bottom, another is in the middle of the list, and yet another is near the top.
Please either add a sort option, or just by default sort newest to oldest for all office programs with the document recovery pane. Then when Excel crashes, the file I'm looking for is always going to be at the top of the document recover window list.
I have written a macro that creates my own document recovery list (sorted by date). Works perfectly. Saved my bacon many times. If they won't fix it, do it yourself - I never even look at the pane anymore :) macro code is below, wouldn't let me attach file. I have added a button on my ribbon that points to this macro in my Personal.xlsb. If you do not have a Personal.xlsb file, Google it, there is plenty of sites that explain how to create one and add macro buttons to your ribbon. I can't even work with raw native Excel anymore, I have customised it so much to fix the issues they obviously don't think are important but drive us crazy! Cheers :)
'creates a list of recovered documents currently sitting in Excel's autorecovery folder. Looks in subfolders as well (except XLSTART)
'need to create a folder in your Autorecovery folder called AUTOSAVED LISTS to save your file into if you want to keep the list
Dim ws, DOCLISTws As Worksheet
Dim wb As Workbook
Dim fs As Object, eFOLD As Object, Archivo As Object
Dim LOCfiles, LOCfilesAuto As String
Dim subarchivo As Object
'add new workbook
Set wb = Workbooks.Add
Set DOCLISTws = ActiveSheet
'set files location to Excel's autoRecover path
LOCfiles = Application.AutoRecover.Path
'name sheet and column headers
DOCLISTws.Name = "AUTOSAVED FILES_" & Format(Now, "yyyymmdd-hmm")
DOCLISTws.Cells(1, 1).Value = "LAST AUTOSAVE TIME"
DOCLISTws.Cells(1, 2).Value = "FILENAME"
DOCLISTws.Cells(1, 3).Value = LOCfiles
'hyperlink to autorecover path
ActiveSheet.Hyperlinks.Add Cells(1, 3), Trim(Cells(1, 3).Value)
DOCLISTws.Range("A2").Select 'starting point
Set fs = CreateObject("Scripting.FileSystemObject")
'if there is no LOCfiles, just exit sub
If LOCfiles = "" Then
'can't remember why I did this, but it's working so if it aint broke..
ElseIf Right(LOCfiles, 1) <> "" Then
LOCfiles = LOCfiles & ""
'set the folder to LOCfiles folder
Set eFOLD = fs.getfolder(LOCfiles)
'loop through files in eFOLD, ignoring '.xar'. Add name/path in new row each time
For Each Archivo In eFOLD.Files
If Right(Archivo.Name, 3) <> "xar" Then
ActiveCell.Value = Archivo.Datecreated
ActiveCell.Offset(0, 1).Value = Archivo.Name
ActiveCell.Offset(0, 2).Value = Replace(Archivo.Path, Archivo.Name, "")
'do same in subfolders (ignoring XLSTART and Autosaved lists folders)
For Each subf In eFOLD.SUBFOLDERS
If subf.Name <> "AUTOSAVED LISTS" And subf.Name <> "XLSTART" Then
For Each subarchivo In subf.Files
ActiveCell.Value = subarchivo.Datecreated
ActiveCell.Offset(0, 1).Value = subarchivo.Name
ActiveCell.Offset(0, 2).Value = Replace(subarchivo.Path, "\" & subarchivo.Name, "")
'Add hyperlink to each file folder location (will open to folder level, not actual file)
For r = 2 To ActiveSheet.UsedRange.Rows.Count
ActiveSheet.Hyperlinks.Add Cells(r, 3), Trim(Cells(r, 3).Value)
'add formatting and autofilter
.Font.Name = "Calibri"
.Font.Size = 10
.Font.ColorIndex = xlAutomatic
.Rows(1).Interior.Pattern = xlSolid
.Rows(1).Interior.Color = RGB(239, 239, 244)
.Rows(1).Font.Bold = True
.Sort key1:=Range("A1"), Order1:=xlDescending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'prompt user to save file or not (just comment this bit out if you don't ever want to save the file)
ASK = MsgBox("Do you want to save this file??", vbYesNo + vbExclamation, "SAVE OR NOT?")
If ASK = 6 Then
'if yes, save into AUTOSAVED LISTS folder you have created
Fname = "AUTOSAVEDfiles_" & Format(Now, "yyyymmdd-hmm") & ".xlsx"
wb.SaveAs Filename:=LOCfiles & "AUTOSAVED LISTS\" & Fname
I have written a macro that creates my own document recovery list (sorted by date). Works perfectly. Saved my bacon many times. If they won't fix it, do it yourself - I never even look at the pane anymore :) It is the only macro in the attached file. I have added a button on my ribbon that points to this macro in my Personal.xlsb. If you do not have a Personal.xlsb file, Google it, there is plenty of sites that explain how to create one and add macro buttons to your ribbon. I can't even work with raw native Excel anymore, I have customised it so much to fix the issues they obviously don't think are important but drive us crazy! Cheers :)
Yes please! So many times I'm confronted with a list of unsorted, files with ridiculously formatted names. Finding the ones you want and being sure which you definitely don't is needlessly hard!!!
Yes!!! This would save so much time! So frustrating, and frankly stupid that it hasn't been changed yet.
Josef Block commented
Glad to see someone else pointing out Microsoft sheer stupidity. Seems almost as if the files are intentionally arranged in an order that would make it most difficult for someone to clean up their auto recovered files leaving them the choice of keeping them and allowing the number to keep growing to the point that the number of auto recovered documents is greater than the number of "real" Excel files on your hard drive.
Until one day, you make that decision to wipe all the files clean only to find out within a few hours you really needed that one file that was on that list sitting in the middle of a stack of thousands.
Seriously, how is this not already a thing? It's a no-brainer.
further i did a successful search of "auto save" in c: and i got my documents, but wanting to save this search "path" to my desktop for future, i see a string [me as] User, then something called App Data but i simply cannot find this "path" sorry very low tech ... so how can I find this function and bookmark it so i can just click on it in future to sort, and yes BTW what a great tip !
Anonymous - what is the exact name of the folder? having heck of time searching name, looong search loops, must have wrong "name" thnx
Nils Kangas commented
I agree! Micro$oft MUST fix this NOW!
My Excel crashes almost daily and the autorecover funtion fails very often.
I also don't delete the files until I am confident never to need them anymore.
Also, autorecover does not guarantee saving of all the VBA code written.
What's the purpose of a flawed autosave function?
- if MS is unable to create a functional solution, AT LEAST save the VBA code on disk in a CACHE in some readable format (text).
This illustrates how fundamentally MS still don't get customer service.
brian554xx - YOU are a ROCK STAR!!! THANK YOU for sharing this tip...you just saved me sooooooooooo much time...
Did ya'll READ THIS??? This tip alone is worth a million bucks. Somebody pay the man. :-)
Microsoft - Please fix this, but in the meantime, you might wanna hire this guy.
Thanks, Anonymous Dude!
Here is how to find the folder:
File > Options > Save > AutoRecover file location
Anonymous Dude commented
Until Microsoft updates the recovery pane to sort by mod date one solution is to use the folder on your computer which is used to store the auto-saved files. You can then sort by mod date in the file explorer window. Hope that helps you for now
Really Microsoft! This needs to be done!
Craig Williams commented
Seriously, why is this not done? This makes life with Excel and Word extremely frustrating. It's bad enough when it crashes and causes me to have to go through the recovery pane to find files needed, but when it's a big list and there are 3 versions of the same document in multiple places within the list it is mind numbing. I know, I could keep it smaller by clearing it out. It might also be easier if Excel didn't pick the worst possible time to crash.
cj Jones commented
I agree! 100% This is a really good feature but it could be an absolute GREAT feature, if you'd just add the function to sort by date!!! and sort by filename!
This is a no-brainer
Yesss, pleeease let us sort the autosaved files!!
Yes! Yes! Yes!
Yes, why not be able to sort? I have the same exact issue.