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
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
Last Comment
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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?
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?
>Any ideas on how to clean this out?
Have you tried my suggestion - comment ID:34905536 ?
Patrick
Have you tried my suggestion - comment ID:34905536 ?
Patrick
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.
Fair enough - thanks for the response.
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
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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'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.
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
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.
Thanks for the detailed explanation - it may help others in the future
Good to see you got there :)
Cheers
Dave
Good to see you got there :)
Cheers
Dave
Microsoft Excel
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
ASKER