INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

VBA Visual Basic for Applications (Microsoft) FAQ

Excel How To

How can I recover my VBA code from a corrupt workbook that won't open by xlbo
Posted: 7 Jun 05

This was posted by RobBroekhuis a while ago and has received several plaudits. It is a way of extracting the VBA code from a workbook that is corrupted and will not open. In the absence of Rob, I am copying his posting wholesale into the FAQs section so that it is preserved and easier to find. So, without further ado, here's Rob's post (all comments / highlighting are mine):

This either works or it doesn't (depending on how corrupted things are), so it won't take more than a few minutes.  It's macro code to run from within Word VBA.  I don't recall where I found this method, and I've modified it since first finding it on the web.  Here's what to do:

1) Copy the code into a normal word VBA module.
2) Change the filename in the code to something you want.  3) Establish a reference to the Microsoft Excel 9.0 Object 4) Library:  Tools, References, check the box next to this entry.
5) Execute the code (cursor in module; press F5).
6) If you get an File Open error message, click Debug, then Continue.
7) When the code has executed, look in the RecoverPath folder.  
8) Import each of the *.txt files into your VBA project. Excel will rename the txt files to the original module name, although you may have to copy the contents of ThisWorkbook from a class module into the actual ThisWorkbook folder.

CODE

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

Hope this helps more people to find this excellent piece of code that can quite rightly be called a lifesaver !!

Back to VBA Visual Basic for Applications (Microsoft) FAQ Index
Back to VBA Visual Basic for Applications (Microsoft) Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close