Reclaiming VBA Code in a corrupted file

  • Thread starter Thread starter Ion Chalmers Freeman
  • Start date Start date
I

Ion Chalmers Freeman

Well, thank you, J.E. McGimpsey!
I badly corrupted an excel file attempting to set a chart's series'
values to a dynamic array. I couldn't open in in Excel 2000, in which
I'd created it, or in Excel XP, which I have on another computer.
The sneaky opening via automation in word and exporting through the
VBE object worked about as well as I expected.
I despaired!
But, I read your posting, downloaded and installed OpenOffice, opened
my file in OpenCalc and reclaimed my code. That's quite a relief.
 
I'm glad that you were able to recover your code. You might want to
try the following code to periodically back up your VBA. Watch for
line wrap.

I adapted the following code from Chip Pearson's WebSite to export
code to text files. I attached it to a toolbar button so that I can
make backups after revisions.

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 errHandler

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

errHandler:

MsgBox "The reason for this message:" & vbCrLf & "The Error Number
is: " & Err.Number _
& vbCrLf & "The Error Description is: " & Err.Description

End Sub


Tested using Excel 97SR2 on Windows 98SE,

HTH
Paul
 
Back
Top