Can I make Excel save recovered files to their original folder?

[JenniferMurphy]

Something just happened that caused Excel to hang. I had 6 workbooks open at the time. I waited about 10 minutes before using Alt+Esc to break the hang. When I did, I got a recovered files panel with 2 copies of each workbook that was open. One was the original saying it was as last saved. The other was an xlsb file saying that it was recovered or repaired.

Two questions:

1. How can I see the differences between the last saved and the recovered workbooks? There is no Compare option under the Review tab as there is in Word. It looks like m$ft has a separate Spreadhseet Compare tool, but there were several other download options, including SoufceForge and CNet. Are these all the same thing? If not, which one should I try?

2. If I try to save the repaired/recovered book (.xlsb file), it starts in \Documents, so I have to navigate to the folder where the original is stored. Is there some way I can get it to default to the original folder?

Thanks

 

[jassi2006]

Unfortunately a Big Nooo.

However for future you can do two things
Either use less timing for Autosave in excel Option Settings

Or

Create a VBA function to make a copy before closing your excel workbook in Workbook before close event of your macro.

 

[cmowla]

1. How can I see the differences between the last saved and the recovered workbooks?

What are the crucial changes that you want to see if they were made? If it is just the cell values (and/or formulas) in specific sheets (and not formatting, cell height/width, etc.), it is really easy to write a comparison tool to tell you the differences. I saw Basic tasks in Spreadsheet Compare , but that doesn't show up in Microsoft 365 Enterprise for me!

 

[johnnyL]

2. If I try to save the repaired/recovered book (.xlsb file), it starts in \Documents, so I have to navigate to the folder where the original is stored. Is there some way I can get it to default to the original folder?

That looks like where you would set your save preferences.

 

[cmowla]

Create a VBA function to make a copy before closing your excel workbook in Workbook before close event of your macro.

And just in case she wants it, here's some code I (partially) wrote to do just that.

This code goes in the ThisWorkbook code module.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'(Other code goes here, if present.)
Call Test__CreateBackupOfThisWorkbook
End Sub

And this code goes into a standard code module (e.g. "module 1"). Just change the name of the Backup folder (currently Backups) in the 2nd line of code.

• If the folder exists already, it will leave it and all files already in it alone.
• If you want to create more than one back up of the Workbook with the same file name, it's fine.
  • You can have as many backups of the same file in the Backup Folder as you wish, as this "Backup Program" will tag on the current date and Time (down to the second)
• By default, it creates the Backup folder (again, if it doesn't already exist) within the same folder (or Desktop, Documents, etc.) that the Excel Workbook is in, but you can simply use Windows Explorer, navigate to the folder that you want, double click on the "search bar" at the top, copy that file path, and paste it in place of ThisWorkbook.Path on the second line of code.

Sub Test__CreateBackupOfThisWorkbook()
Call CreateBackupOfThisWorkbook(ThisWorkbook.Path, "Backups")
End Sub
Sub CreateBackupOfThisWorkbook(desiredLocation As String, desiredFolderName As String)
Dim fileExtension As String
fileExtension = SubString(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, "."), Len(ThisWorkbook.Name))
Call CreateDirectory(desiredLocation & "\" & desiredFolderName)
Dim backupName As String
backupName = SubString(ThisWorkbook.Name, 1, InStrRev(ThisWorkbook.Name, ".") - 1) & " (" & Replace(Replace(Now(), "/", "-"), ":", " ") & ")" & fileExtension
Call Copy_This_File_From_This_Dir_to_This_Dir(ThisWorkbook.Name, desiredLocation, backupName, desiredLocation & "\" & desiredFolderName & "\")
End Sub


Sub Test__CreateDirectory()
Call CreateDirectory(ThisWorkbook.Path & "\Backups")
End Sub
Sub CreateDirectory(strPath As String)
'https://stackoverflow.com/a/33671329

    Dim elm As Variant
    Dim strCheckPath As String

    strCheckPath = ""
    For Each elm In Split(strPath, "\")
        strCheckPath = strCheckPath & elm & "\"
        If Len(Dir(strCheckPath, vbDirectory)) = 0 Then MkDir strCheckPath
    Next
End Sub


Sub Test__Copy_This_File_From_This_Dir_to_This_Dir()
Call CreateDirectory(ThisWorkbook.Path & "\Backups")
Dim backupName As String
backupName = SubString(ThisWorkbook.Name, 1, InStrRev(ThisWorkbook.Name, ".") - 1) & " (" & Replace(Replace(Now(), "/", "-"), ":", " ") & ")" & ".xlsb"
Call Copy_This_File_From_This_Dir_to_This_Dir(ThisWorkbook.Name, ThisWorkbook.Path, backupName, ThisWorkbook.Path & "\Backups\")
End Sub
Sub Copy_This_File_From_This_Dir_to_This_Dir(fileName As String, source_Directory As String, copyName As String, destination_Directory As String)
'https://stackoverflow.com/a/16943127
Dim fso As Object
Set fso = VBA.CreateObject("Scripting.FileSystemObject")
Call fso.CopyFile(source_Directory & "\" & fileName, destination_Directory & copyName)
End Sub


Sub Test__SubString()
MsgBox SubString("ABCDEF", 3, 5)
End Sub
Function SubString(inputString As String, start As Integer, Finish As Integer)
On Error GoTo Quit
SubString = Mid(inputString, start, Finish - start + 1)
Quit:
End Function