Fix a corrupted Excel 2007 file

I was debugging some VBA in an Excel 2007 file, using f8 to step through the code, when I was distracted and when I returned I opened another Excel file.  Opening the other file while VBA wa in operation seems to have messed up the original file.

Now , when I try to open the latest saved version of the file, it hangs up or displays a corrupted screen with cells wiped out and characters that shouldn't be there.

I would like to know what is the most reliable way to recover.  The Excel spreadsheet content, an input data table, is not complicated and the VBA would take me a a few hours to recreate but I'd prefer to minimise the rework as far as possible.

Thanks
LVL 1
sjgreyAsked:
Who is Participating?
 
patrickabCommented:
sjgrey,

When you are in the FileOpen dialogue box and have selected the file, at the right hand end of the Open button there is a dropdown arrow, click on that and select 'Open and Repair', Hopefully that will repair the file - if not then try Open Office, as already suggested.

Patrick
0
 
DaveCommented:
Some years back I successfully recovered the data from a corrupt xl2003 file by using Open Office, http://openoffice.org-suite.com/. Back then I don't think VBA was supported

Worth trying as its free

Other tips - which I found while googling the open office fix - are at http://www.techrepublic.com/blog/10things/10-ways-to-recover-a-corrupted-excel-workbook/1002

Cheers

Dave
0
 
sjgreyAuthor Commented:
Midnight here now but I;ll look at both the solutions in the morning thanks
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
sjgreyAuthor Commented:
Following suggestion number 10 on http://www.techrepublic.com/blog/10things/10-ways-to-recover-a-corrupted-excel-workbook/1002 I was able to access the file and the macro text, which I've saved in a .txt file to be safe

Disabling macros allowed the file to open, which it wouldn't otherwise.  I was then able to save it under a new name.  I then created a new file, copied and pasted the main data input page into the new file, by copying the sheet content not using Moce or Copy on the sheet as a whole.  Then I created a new module in VBA and pasetd in the macro text.

As soon as I re-enabled macros in the new file the problem reoccurred.  Now wnehever I open Excel, the new file opens with a correputed  display (some regions blanked out) and hangs up.

Any ideas on how to clean this out?

0
 
patrickabCommented:
>Any ideas on how to clean this out?

Have you tried my suggestion - comment ID:34905536 ?

Patrick
0
 
sjgreyAuthor Commented:
Sorry I should have said - I did try the Repair option.  I tells me that the file caused a serious problem last time it was opened and asks if I want to proceed.  When I say yes the same issue arises.
0
 
patrickabCommented:
Fair enough - thanks for the response.
0
 
sjgreyAuthor Commented:
Any ideas how to clean out Excel's residual material that means the corrupted file pops up when I open Excel, before I try to open a file?  It's not consistent - happens a bout 50% of the time
0
 
DaveCommented:
A good way to "rebirth" a problematic file is to

- select all sheets
- right click the sheet tab "move and copy"
- to book. ..  pick .. (new book)

This copies sheet code, not the module code

Another option is to save the file in web format, then resave as Excel

Cheers

Dave
0
 
sjgreyAuthor Commented:
Solved!

Disabling macros was the key to being able to fix it

I found an error in the code that the copiler didn't seem to pick up and when I eliminated it the problem went away.  For future reference:
I have a block of global variables declared at the head of the module with Public statements
In a recent edit I'd inadvertently included  Dim among these when I'd inserted a new set of variables
At the same time, I'd inadvertently duplicated a variable declaration

I'm not sure what exactly was going on but replacing the Dim with Public and eliminating the duplicate declaration have made the rebuilt file work.

Thanks for ideas.
0
 
DaveCommented:
Thanks for the detailed explanation - it may help others in the future

Good to see you got there :)

Cheers

Dave
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.