Impossible to open excel workbook

[Enk]

Hi everybody.
I have an enormous problem.
I've built an excel 2000 workbook with a lot of vba code, modules and userforms.
All of a sudden, one day, I tried to open it and NOTHING!!!!
A message appeared (and still appears each time I open the wb), saying something like this: "an error occurred on module......not valid page...." and so on.... (the message is not exactly like that because I translated it here in english as my excel version is not in english, but I hope you understood what message it is).
So: does anybody give me any suggestion about how to open or recover my file (or, at least, how to recover the vba modules and the userforms, which are the most important part of the wb).
Thank you all by now for your kind replies.
Bye
Nick

 

[stevexff]

Restore it from your most recent backup?

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[PHV]

Have you tried to open it with the Shift key depressed ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Enk]

Hi guys.
Steve, unfortunately my most recent back up is not so recent....
PH, I've tried to open it both with macro enabled and disabled.
Moreover, I tried to open it from I.E. as an htm page, from word and as a sylk file.
In all cases it didn't work and, anyway it wouldn't recover the vba code and forms even if it worked.
I tried as well with the shift key depressed but...nothing to do...
Thank you very much anyway.
More suggestions?
Thanks

 

[N1GHTEYES]

See faq707-5899.

I had this problem and this solved it for me.

Tony

 

[Enk]

Thanks a lot Tony. I'm trying your method as soon as I get back home to my computer. I'll let you know if it works for me too (I do hop so..).
Thank you
Bye
Nick

 

[N1GHTEYES]

I wish I could claim this idea as mine. I'm not that good however. You have xlbo to thank for posting the FAQ and RobBroekhuis for the original idea.

This method ought to allow you to retrieve your code. It may not help you to retrieve the spreadsheet itself. However, if the prob is associated with the code, I think there may be a way to strip the code part from the spreadsheet and thus allow you to get at the sheet too. I've a feeling I managed to do that eventually when I had this problem. Unfortunately, my remaining brain cell is on strike this morning and I can't remember how.

Perhaps some of the clever chaps and chapesses around here could suggest a method?

Tony

 

[Enk]

Hi Tony.
I tried the method and, unfortunately, it didn't work.
By the way, what I need to recover is the vba modules and vba userforms (code and controls or, at least, the code) , while there is no problem about the sheets contents.
I hope somebody knows a way.
Please help me.
Thank you

 

[N1GHTEYES]

Can you clarify a couple of points?

1 Does your workbook open, but you can't get at the modules? Or does the workbook just not open at all?

2 When you tried Rob's solution, you say it did not work. Do you mean you could not get it to run properly, or that it ran OK, but did not extract your module text?

Tony

 

[Enk]

Here I am Tony.
1) My workbook doesn't open at all neither with macros enabled nor with macros disabled. ( A message informes me that there has been an error in module Excel.exe...).
2) Trying Rob's solution the problem was that it didn't run properly.

Here's Rob's macro

Sub Recover_Excel_VBA_modules()

Dim XL As Excel.Application
Dim XLVBE As Object
Dim i As Integer, j As Integer
Dim XLSFileName As String, RecoverPath as string

'change this to be the corrupted file path and name
XLSFileName = "c:\windows\desktop\publication catalog"
RecoverPath="C:\temp\"

Set XL = New Excel.Application
XL.Workbooks.Open FileName:=XLSFileName + ".xls"
Set XLVBE = XL.VBE



j = XLVBE.VBProjects(1).VBComponents.Count



For i = 1 To j
Debug.Print XLVBE.VBProjects(1).VBComponents(i).Name
XLVBE.VBProjects(1).VBComponents(i).Export _
FileName:=recoverpath & _
XLVBE.VBProjects(1).VBComponents(i).Name & ".txt"
Next
XL.Quit
Set XL = Nothing
End Sub



When the code stops the highlighted string is

j = XLVBE.VBProjects(1).VBComponents.Count

and the error message says that the index is not included

Any idea?

Thanks a lot

 

[N1GHTEYES]

Nick,

A while back, I put this in a word doc, but with some slight mods, together with a few words of explanation on how to use it, and a command button to run it. I really ought to have included a file dialogue box to allow the user to browse for the corrupted file, but I was too lazy or rushed at the time and didn't, then forgot about it, so it still requires the user change to the hard-coded path.

However, it does seem to work with some pretty messed-up workbooks. The changes I made to the code were basically just to tell it to ignore any errors found and bull on regardless. That may or may not solve your problem.

Here is my variant of the code:

Option Explicit

Private Sub CommandButton1_Click()
Recover_Excel_VBA_modules
End Sub

Sub Recover_Excel_VBA_modules()
    On Error Resume Next
    Dim XL As Excel.Application
    Dim XLVBE As Object
    Dim i As Integer, j As Integer
    Dim XLSFileName As String, RecoverPath As String
    
    'change this to be the corrupted file path and name
    XLSFileName = "c:\MyBrokenSpreadsheet"
    
    'change this to the required output path if different
    RecoverPath = "c:\recovered\codemodules\"

    Set XL = New Excel.Application
    XL.Workbooks.Open FileName:=XLSFileName + ".xls"
    If Err.Number <> 0 Then Err.Clear
    Set XLVBE = XL.VBE

    j = XLVBE.VBProjects(1).VBComponents.Count
    For i = 1 To j
       Debug.Print XLVBE.VBProjects(1).VBComponents(i).Name
       XLVBE.VBProjects(1).VBComponents(i).Export _
          FileName:=RecoverPath & _
          XLVBE.VBProjects(1).VBComponents(i).Name & ".txt"
          If Err.Number <> 0 Then Err.Clear
    Next
    XL.Quit
    Set XL = Nothing
    If Err.Number <> 0 Then Err.Clear
End Sub

Try it. It may help. I'd like to know how you get on with it, but don't expect any quick response, as I will be away on holiday for the next week.

There are some much better VB/VBA experts on here than me - if you're still having trouble, maybe they can suggest something.

Tony

 

[N1GHTEYES]

Sorry, I meant to clarify this before sending the last message, but hit the wrong button. Oops.

When you run it, set the error handling option to "Break on Unhandled Errors". If you're not sure how, just wait until it pauses at an error then hit F5. If that does not work, hit F8 if it pauses at an error, step by step, until it completes.

Good luck.

Tony

 

[mscallisto]

Read these posts quickly and have some questions

Can you open other excel files?
Have you tried opening your file on a different PC?

 

[Enk]

Hi Tony.
Thank you, but... it doesn'work.
The code runs completely but no file is recovered in
my "RecoverPath ".
Perhaps is it possible that all my vba code (modules and
userforms) have been deleted or not saved by excel during last exit? (it would represent a very serious bug for excel but i don't think it's possible).
Hi Callisto.
Yes. I can open any excel file in my pc except for this one.
Besides, I've tried to open my wb in another pc and it didn't work. The problem is my wb.

Does anybody have some further suggestion to fix my problem?
Please.
It took me 6 months to develop it and I don't know what to do now.
Thank you all in advance
Nick

 

[MiggyD]

Does your OS have "system restore" turned on by any chance?

--MiggyD

--> It's a bird! It's a plane! No, it's an OS update patch! Ahh!! <--

 

[Enk]

Hi Miggy. Thanks for replying.
No. "System restore" is not turned on.
Nick

 

[MiggyD]

I think you may be able to salvage some of the code but uncertain of the forms.

Using a copy of the wb, change the extension from xls to wri. then using wordpad, scroll down till you see some code you recognize and paste it to another location.

you may have to rebuild some but at least you have something to go by.

Good Luck.
--MiggyD

--> It's a bird! It's a plane! No, it's an OS update patch! Ahh!! <--

 

[Enk]

Hi MiggyD.
Thank you.
I know that method, but unfortunately what I need is to recover the userforms (all the wb is made by modules and userforms and the sheets are empty (no formulas or data).
So, what I'm hopelessly looking for a method that allow me to recover (or extract or copy) the userforms.
I cannot believe that there is no way to do it. I 've heard about some programs that fix this problem (excelfix i.e.) but they are expensive.
So I wondered if there's a way to do it (code, macro...).
Any help will be much appreciated.
Thank you all
Nick

 

[PHV]

I cannot believe that there is no way to do it
The standard and professional way is to restore the backup.

 

[mscallisto]

I agree about the backup but we've all been there and learned a good lesson.

My first (no backup lesson) took place about 40 years ago and I can't tell ya how many times I ask others the same old question.....did ja make a backup YET???

I do, however, hope you get to salvage some if not all of your hard work.

sam