Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How can I recover my VBA code from a corrupt workbook that won't open
Excel How To
[color purple]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):[/color]
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:
[color green]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.[/color]
Hope this helps more people to find this excellent piece of code that can quite rightly be called a lifesaver !!![[flowerface]](/data/assets/smilies/flowerface.gif "[flowerface] [flowerface]")
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:
[color green]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.[/color]
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
[color green]'change this to be the corrupted file path and name[/color]
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 SubHope this helps more people to find this excellent piece of code that can quite rightly be called a lifesaver !!