Solved

Fix a corrupted Excel 2007 file

Posted on 2011-02-16
11
376 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:sjgrey
  • 5
  • 3
  • 3
11 Comments
 
LVL 50

Assisted Solution

by:Dave Brett
Dave Brett earned 333 total points
ID: 34905204
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
 
LVL 45

Accepted Solution

by:
patrickab earned 167 total points
ID: 34905536
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
 
LVL 1

Author Comment

by:sjgrey
ID: 34906160
Midnight here now but I;ll look at both the solutions in the morning thanks
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 1

Author Comment

by:sjgrey
ID: 34911762
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
 
LVL 45

Expert Comment

by:patrickab
ID: 34911795
>Any ideas on how to clean this out?

Have you tried my suggestion - comment ID:34905536 ?

Patrick
0
 
LVL 1

Author Comment

by:sjgrey
ID: 34911884
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
 
LVL 45

Expert Comment

by:patrickab
ID: 34911899
Fair enough - thanks for the response.
0
 
LVL 1

Author Comment

by:sjgrey
ID: 34911924
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
 
LVL 50

Assisted Solution

by:Dave Brett
Dave Brett earned 333 total points
ID: 34911926
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
 
LVL 1

Author Comment

by:sjgrey
ID: 34912164
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
 
LVL 50

Expert Comment

by:Dave Brett
ID: 34912203
Thanks for the detailed explanation - it may help others in the future

Good to see you got there :)

Cheers

Dave
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
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…

821 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