I’m going to give you the cold, hard truth. Sometimes you can corrupt an Excel file while playing with advanced VBA features, like customizing your ribbon. Most of the time Microsoft’s auto-repair feature is good enough to repair your Excel files.
But what if that doesn’t work? Fortunately, the folks over at Cimaware have developed some GREAT Microsoft Office data recovery software!
I’m here to share my experience with one in particular: the ExcelFIX recovery software.
Let me tell you a story about what happened to me and how ExcelFIX recovered most of my data. I still had to perform some manual recovery actions to recover my charts and macros, but ExcelFIX did most of the heavy lifting.
How I Corrupted my Excel File
One day, I was working on my Species List spreadsheet. You know, the one where I made the awesome Excel Splash Screen?
I wanted to customize the top ribbon. One way to do that, is to add a “.zip” to the extension of your .xlsm file, like this:
Once you open the zip file, you’ll see a bunch of folders.
My Excel file already had a custom ribbon, so that’s why you see the
Normally, the built in Excel repair scripts do a great job at recovering your spreadsheet when you mess things up inside your customized ribbon subfolder. Normally, but not always.
Anyway, I had an extracted copy of the userCustomization folder saved elsewhere, so I wanted delete the folder insize the zip file and start from scratch with a completely overhauled ribbon.
Well, guess what I did? I accidentally deleted the
Programmer’s Note:If you accidentally delete your
I backed out and removed the .zip extension on my file and attempted to open the spreadsheet. I got this message:
I was feeling good. I was used to seeing this message when playing with my customized ribbons. Of course I trust this workbook and want to recover the contents, I thought. So I clicked Yes.
I was not prepared for the next message.
I tried again, and got the same message. After panicking for a bit, I’m here to tell you all was not lost.
Enter Cimaware ExcelFIX.
How ExcelFIX Saved my Spreadsheet Data
Like I said, I panicked for a bit. It’s natural. When I started thinking clearly, I downloaded the Cimaware ExcelFIX pro software and launched it.
Let me show you what happened.
When you launch ExcelFIX, it defaults to the Quick Recovery Wizard. The first thing I did was click “Select File” and navigate to my file.
After I selected my file, I clicked “Recover” and I was brought to a reassuring summary screen that made me confident this piece of software was going to at least recover my spreadsheet data.
I thought it was interesting that my original spreadsheet had 1 chart, but the summary screen implied it didn’t recover the chart. On the other end of the spectrum, I was recalled my spreadsheet had 2 visible sheets and 3 xlVeryHidden sheets (hidden via VBA). I was pleased to see the summary screen indicated ExcelFIX was able to recover all 5 sheets.
When I got to the save screen, I noticed that by default the wizard wants you to save your file as a .xlsx file, even if the original corrupted spreadsheet had macros. I manually changed the extension of the file to a .xlsm at the Save prompt, making sure not to overwrite my original corrupted file. ExcelFIX is smart enough to know that and it won’t let you overwrite your file. You don’t want to save your recovered workbook over your corrupted workbook. You also don’t want to delete your corrupted workbook! I’ll explain in the next section.
When I opened my workbook, all my data was there, but my 3 xlVeryHidden sheets were now fully visible.
Unfortunately, my charts and VBA macros were missing.
ExcelFIX did’t recover my workbook perfectly, but it certainly got my important spreadsheet data. I worked hard on those macros. I knew there had to be a way to recover my VBA work.
How I Manually Recovered my Macros
Remember how I said you don’t want to overwrite your original corrupted workbook? Here’s why.
I exited my recovered spreadsheet and renamed it with a .zip extension, just to poke around inside. I noticed a
That’s not why I wanted to poke around, though. When I went into the
This gave me hope that all my chart and VBA data was still there somewhere - they just weren’t properly linked anymore.
Here’s what I did to recovery my macros and charts:
- I deleted everything but the _rels folder in the recovered spreadsheet zip file that ExcelFIX created.
- I exited the recovered spreadsheet zip file and renamed the corrupted spreadsheet zip file with the .zip extension.
- I extracted the contents of the corrupted spreadsheet zip file to a folder and I copied them into to the recovered spreadsheet zip file.
- I removed the .zip extension on the recovered spreadsheet zip file and opened up the spreadsheet.
When I opened the recovered spreadsheet, I was greeted with this wonderful security notice:
By displaying this security notice, I knew my manual macro recovery method worked. I clicked “Enable Macros” and my VBA splash screen started right up.
It gets better. Not only did my macros work, but my xlVeryHidden spreadsheets were hidden again and my Chart was back!
Everything was back to normal! The only thing missing was my custom ribbon, and that’s a simple xml fix in the _rels folder.
Final ExcelFIX Verdict
I was a skeptic, but now I have to admit - ExcelFIX is a great product. It proved to me that it has the ability to recover corrupted files, and it does so in a graceful manner.
The intuitive, natural progression of the ExcelFIX recovery wizard is a relief. When you corrupt an important file, the last thing you want to do is deal with the stress of a cumbersome interface. You really don’t have to worry about that with ExcelFIX.
It didn’t restore everything exactly back to how it was, but it did recover enough data to allow me to manually recover my macros and charts. All it took was replacing folders and files in the zip version of my spreadsheet.
I’d call this experiment a success. Cimaware did a great job developing this product. ExcelFIX really CAN recover your corrupted spreadsheets, but it still may require some effort on your part if you want to recover your embedded macros.
Disclosure: Please note that some of the links in this post may be affiliate links. At no additional cost to you, wellsr.com will earn a commission if you decide to make a purchase clicking through those links. Earning revenue through affiliate sales is a necessary practice to maintain the website and continue to provide our users with free content. As the author of The VBA Tutorials Blog, Ryan Wells believe in transparency and honesty.
Coming Fall 2017
Auto-Import Macros Directly from wellsr.com
Absolutely FREE when you join the waitlist
About Ryan Wells
Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA applications, including PDF VBA - a leading Excel Add-in for exporting Excel Objects, like charts and tables, to PDFs. Discover more of his popular Excel Add-ins, including Mouse To Macro and CF Shapes, at his dedicated Excel Add-ins page.Follow