Solved

Fix a corrupted Excel 2007 file

Posted on 2011-02-16
11
382 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Suggested Solutions

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…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
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 using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

738 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