Solved

How to recover data in spread sheet from xlsm file damaged by coding error

Posted on 2014-12-15
2
406 Views
Last Modified: 2014-12-16
I made a coding error in changing a contant (string) to a simple public variable and corrupted my file.
The current version of data in one spread sheet is not backed up, and I seek to recover it
All developer code Is backed up

When I attempted to open the xlsm file, the Excel announcement screen opened, and reported the correct name of the file it was opening. After a long delay, it reported that it was "Opening and repairing" the file as "Attempt#1".
After 15 minutes, no progress had been achieved.

For example, can I recover this spreadsheet data, say by converting the xlsm file to xlsx (without opening it)?

Thanks!
Kelvin4
0
Comment
Question by:Kelvin4
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 36

Accepted Solution

by:
Kimputer earned 500 total points
ID: 40502101
If you are really sure the code is responsible, you can easily fix it.
Make backup of the original file (even though it's corrupt)
Rename xlsm to zip, open zip file as usual (though I'd prefer you to use 7zip), navigate to the folder xl, delete the file vbaProject.bin
Rename back, open file. If the corruption was  really only inside the code, it should start normally (without any working code, it will be empty), and all sheet data should be there.
If still error, the error must have sneaked into the sheet data. Then use the zip method again, remove sheet1.xml from the folder xl\worksheets, test file (acknowledge warnings, try to fix it). If it starts now, you will only be missing sheet1. If still doesn't start, copy file from backup, start over, delete sheet2, repeat till all sheets are tried.
If still nothing, start over again, now delete the theme1.xml from the xl\theme folder (obviously you will lose a lot of your layout/fonts etc). Then if still error, try removing styles.xml.
After that, I'm out of ideas.
0
 

Author Closing Comment

by:Kelvin4
ID: 40502136
Thanks for speedy help to worried questioner!

Actually in the short interval, i opened a blank xl file and in Options, set the recalculate option to Manual (selection '4' as it happened).

Then uploaded offending xlsm file. It opened, and reported an ambiguous Constant .
By closing the reporter window using the top right red 'close X' interaction, the offending code remained visible and editable.

Thus the error was commented out, and the file seems to be alive and well with the rest of the code.

But, again thanks for your method which is more 'hands on' and flexible.
Kelvin

Kelvin
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question