Mac – Locating Autorecover files Excel 2011 doesn’t find

macmicrosoft-excel-2011

When Excel 2011 crashes, it often does not automatically re-open the latest version from the auto-recover files. Instead Excel may bring up the last user saved version that may not have the latest changes.

How can I locate and use the latest auto-recovery files, and hence recover the most recent changes?

Version information:
Excel 2011 for Mac version 14.3.1 (130117)
Mac OSX 10.8.2

Best Answer

Found 'em! Here are the solution steps:

Go to ~\Library\Application Support\Microsoft\Office\Office 2011 AutoRecovery in Finder (on OSX10.7+ this may first require making the Library directory visible using one of the methods described here or here)
Change the Extension of the Excel autorecover files of interest from .xlsx to .xlsb
Open the files with Excel (either double click or using Excel's File>Open menu)
Enjoy!

Discussion:

As described in this post, if you are lucky, a newer auto-recovery version can often be found in ~\Library\Application Support\Microsoft\Office\Office 2011 AutoRecovery. Files in this directory often have somewhat munged/cryptic names, but you can always open them up to find the best one.

However, in the current version of Excel for Mac 2011 (14.3.1, maybe others) there is a bug that prevents easily opening these backup files. Specifically, the filenames are listed with an .xlsx extension (corresponding to Excel XML format), but are actually in a different format. As a result attempting to open them in Finder or through the Open menu fails, claiming the files are corrupt. The exact error is

Microsoft cannot open this file The file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.

Changing the file extension to .xlsb (Excel binary format) or .xlk (older Excel backup) will allow Excel to open the file.

Related Solutions

Excel 2011 Mac – Recover Lost Auto-Recover File

Finally we found the file in the folder:

~/Library/Application Support/Microsoft/Office/Office 2011 AutoRecovery

As the ~/Library folder is a hidden folder since OS X 10.7 and not by default searched by Spotlight it's incredibly stupid that this folder is auto-suggested when saving a recovered file.

Also since there IS a folder with the same name under Documents/Microsoft userdata/ that is empty this was hard to find!

To go to this folder, open Finder and in the menu click Go » Go to Folder… or press ⇧⌘G. Then, paste the path from above.

Or, to make the ~/Library folder always visible, open Terminal.app from /Applications/Utilities and enter:

chflags nohidden ~/Library

Excel – link images using a relative path

You have 2 basic questions here, rendering images in Excel, and relative paths.

Rendering Images in Excel

There's a script to accomplish this on SO:

Dim url_column As Range
Dim image_column As Range

Set url_column = Worksheets(1).UsedRange.Columns("A")
Set image_column = Worksheets(1).UsedRange.Columns("B")

Dim i As Long
For i = 1 To url_column.Cells.Count

  With image_column.Worksheet.Pictures.Insert(url_column.Cells(i).Value)
    .Left = image_column.Cells(i).Left
    .Top = image_column.Cells(i).Top
    image_column.Cells(i).EntireRow.RowHeight = .Height
  End With

Next

Relative Paths

I don't know of a way to specify relative paths in an Excel doc, but there's an easy workaround for this. Create a folder for you Excel doc and all of the supporting images, and keep them together. If you need to move this to another location, you just move the whole folder. This also makes it easy to zip up & send to others.

Best Answer

Related Solutions

Excel 2011 Mac – Recover Lost Auto-Recover File

Excel – link images using a relative path

Related Question