Solved

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

Posted on 2014-12-15
2
344 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
2 Comments
 
LVL 35

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

863 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now