asked on

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

SOLUTION

Dave

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

patrickab

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sjgrey

ASKER

Midnight here now but I;ll look at both the solutions in the morning thanks

sjgrey

ASKER

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?

patrickab

>Any ideas on how to clean this out?

Have you tried my suggestion - comment ID:34905536 ?

Patrick

sjgrey

ASKER

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.

patrickab

Fair enough - thanks for the response.

sjgrey

ASKER

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

SOLUTION

Dave

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sjgrey

ASKER

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.

Dave

Thanks for the detailed explanation - it may help others in the future

Good to see you got there :)

Cheers

Dave