Here are two links that deal with corrupted workbooks.
http://groups.google.com/groups?hl=...off&selm=eTSTbZZrCHA.1656@TK2MSFTNGP09&rnum=3
http://groups.google.com/groups?as_umsgid=u4yjYc$v$GA.207@cppssbbsa03
----------------------------------------------------------------------------------------------------
The following is from a posting April 30, 1999 by Rob Bruce. Run this
macro from Word VBA to recover modules from corrupt Excel files.
'\for this macro to run you need to establish a reference to the
'\Microsoft Excel 8.0 Object Library
'\also, if you get a File Open error message, hit Debug, then Continue
Sub Recover_Excel_VBA_modules()
Dim XL As Excel.Application
Dim XLVBE As Object
Dim i As Integer, j As Integer
Set XL = New Excel.Application
XL.Workbooks.Open FileName:="h:\CR - Portfolio Template.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:="C:\temp\vbe_"
& (100 + i) & ".txt"
Next
XL.Quit
Set XL = Nothing
End Sub
-----------------------------------------------------------------------------------------------------------------
Watch for line wrap. Once you've figured out how to recover your code,
use the following subroutine to back up your code. This subroutine
will collect all your hard work in appropriate folders for that time
when Excel junks another file. I backup each time I make changes to my
code.
You need to set a reference to "Microsoft Visual Basic for
Applications Extensibility" library.
Sub ExportAllVBA(Optional varName As Variant)
'' Exports all Modules, etc. to folder named the same as the Workbook.
Dim VBComp As VBIDE.VBComponent
Dim PartPath As String
Dim NextPartPath As String
Dim TotalPath As String
Dim Sfx As String
Dim d As Integer
If IsMissing(varName) Then varName = ActiveWorkbook.Name Else
varName = CStr(varName)
PartPath = "C:\Money Files\Computer Helpers\Modules\"
NextPartPath = Left$(ActiveWorkbook.Name, Len(ActiveWorkbook.Name)
- 4)
On Error Resume Next
TotalPath = PartPath & NextPartPath
ChDir TotalPath
If Err.Number = 76 Then MkDir TotalPath
On Error GoTo ErrExport
For Each VBComp In ActiveWorkbook.VBProject.VBComponents
Select Case VBComp.Type
Case vbext_ct_ClassModule, vbext_ct_Document
Sfx = ".cls"
Case vbext_ct_MSForm
Sfx = ".frm"
Case vbext_ct_StdModule
Sfx = ".bas"
Case Else
Sfx = ""
End Select
If Sfx <> "" Then
VBComp.Export _
Filename:=PartPath & NextPartPath & "\" & VBComp.Name &
Sfx
End If
Next VBComp
Exit Sub
ErrExport:
MsgBox "The reason for this message:" & vbCrLf & "The Error Number
is: " & Err.Number _
& vbCrLf & "The Error Description is: " & Err.Description
End Sub
