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

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,730
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

jassi2006

New Member
Joined
May 3, 2020
Messages
21
Office Version
  1. 2019
  2. 2016
  3. 2007
Platform
  1. Windows
  2. Mobile
  3. Web
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

Board Regular
Joined
Sep 21, 2021
Messages
243
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 7, 2011
Messages
2,103
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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?
temp.PNG
That looks like where you would set your save preferences.
 

cmowla

Board Regular
Joined
Sep 21, 2021
Messages
243
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
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.
VBA 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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,236
Messages
5,768,955
Members
425,506
Latest member
AndreaWorkPlace

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top