Excel 2002: Several days' work is tied up in an .xls file that
mysteriously got corrupted and now I can't open it without Microsoft
"repairing" it by stripping out all the VBA code and the embedded
controls.

I have medium-level macro security control turned on, and I disable
macros when opening the file, but it still comes up with "Microsoft
Excel has encountered a problem and needs to close". Then if I take
the option to "recover and restart", all the VBA code is stripped out.

Is there any way to bypass opening the file with Excel and extract the
VBA modules?

Thanks,
Ken Dahlberg

I've sometimes been able to recover corrupted workbooks using
OpenOffice (which you can set to retain the VBA code):

http://www.openoffice.org


In article ,
(Ken Dahlberg) wrote:

Excel 2002: Several days' work is tied up in an .xls file that
mysteriously got corrupted and now I can't open it without Microsoft
"repairing" it by stripping out all the VBA code and the embedded
controls.

I have medium-level macro security control turned on, and I disable
macros when opening the file, but it still comes up with "Microsoft
Excel has encountered a problem and needs to close". Then if I take
the option to "recover and restart", all the VBA code is stripped out.

Is there any way to bypass opening the file with Excel and extract the
VBA modules?

Here are two links that deal with corrupted workbooks.

http://groups.google.com/groups?hl=e...NGP09&rn um=3
http://groups.google.com/groups?as_u...07@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

-------------------------------------------------------------------------------------------------------------
HTH
Paul
--------------------------------------------------------------------------------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------------------------------------------
Excel 2002: Several days' work is tied up in an .xls file that
mysteriously got corrupted and now I can't open it without Microsoft
"repairing" it by stripping out all the VBA code and the embedded
controls.

I have medium-level macro security control turned on, and I disable
macros when opening the file, but it still comes up with "Microsoft
Excel has encountered a problem and needs to close". Then if I take
the option to "recover and restart", all the VBA code is stripped out.

Is there any way to bypass opening the file with Excel and extract the
VBA modules?

Thanks,
Ken Dahlberg

Paul,
Thank you! This is going to be very helpful.
Ken