Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Fix a corrupted Excel 2007 file

Posted on 2011-02-16
11
Medium Priority
?
395 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 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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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 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 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

783 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