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.
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 !!