Improve company productivity with a Business Account.Sign Up

x
?
Solved

Fix a corrupted Excel 2007 file

Posted on 2011-02-16
11
Medium Priority
?
402 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
Dave earned 1332 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 668 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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
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
Dave earned 1332 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
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.

Join & Write a Comment

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Debits & Credits have been the foundation of financial record keeping since 1494 - over 500 years. Excel is a brilliant tool for leveraging this ancient power - not least with Pivot Tables, sorting and filtering.  This article seeks by illustration …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

608 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