Excel File corrupted, rescue VBA modules? 6

[VBAjedi]

Hi all!

Not sure what I did, but the workbook I've been developing is seriously corrupted or something - it hangs Excel to even try opening it. My last backup of that file was about five (very inspired!) work-hours ago.

Is it possible to extract/export the VBA modules from a workbook without opening it?

Thanks!

VBAjedi

 

[Zathras]

Try setting security to ask whether to open with macros, and then open it with macros disabled. You should then be able to Alt-F11 to get the macros.

If you don't know how to do this, post back and say which version of Excel (Excel 97 is different from Excel 2K in how this is done.)

 

[VBAjedi]

Zathras,

Good idea! I think if it was my VBA code that was causing things to freeze, that would work. Unfortunately, this file is REALLY busted. I enabled virus protection, got the enable/disable macros prompt, chose "disable", and had Excel promptly freeze anyway. Menu bars visible, nothing but white in the workbook area (no column/row headers, nothing). File size is correct (around 1000k) so the file still exists.

Nasty!


VBAjedi

 

[Zathras]

Sorry to hear that, old buddy. I think you're hosed. I just took a look at a hex dump of a workbook with macros and it appears that Excel stores the code tokenized. Even my Const declarations appear to have been string compressed.

Are you sure it's just not taking a long time to load?

There may be some file recovery experts around that can slice out some of the middle of the workbook in such a way that it could load. The macro stuff did seem to be at the end. Last time I looked at the storage format for spreadsheets was with Lotus 1-2-3. It used variable length records and I suppose Excel is similar. Once you find out the record format, you might be able to write a program to read the file up to a point, skip over a few thousand records and pick up again before the macro code starts. But it's a long shot, and sure to take more than 5 hours, inspired or no.

My sincere condolences.

 

[VBAjedi]

Thanks again! I agree, it's not worth the time to attempt a recovery. Hopefully redoing my work won't take 5 hours the second time around. I wish I knew what caused it though. I hate to write it off as "gotta love the Windows OS" if it was in fact something I caused (which it probably was).
LOL
Oh well!


VBAjedi

 

[ETID]

Have you tried opening the file by highliting it in the explorer, then holding the shift down and press enter?


....This may be the same as disabling macros by letting excel ask you via the security,...but it's free and worth a shot!

 

[RobBroekhuis]

I've had pretty good success with recovering VBA modules from corrupted workbooks. If you're still interested in recovering (rather than redoing), let me know.
Rob

 

[VBAjedi]

ETID:
Shift-Enter is a no-go. . . thanks for the suggestion, though!

Rob,
I'd very much LIKE to be able to recover my modules. . . but only if it doesn't take longer than an hour or so. What are you suggesting I do?

Thanks!
VBAjedi

 

[RobBroekhuis]

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:

Copy the code into a normal word VBA module. Change the filename in the code to something you want. Establish a reference to the Microsoft Excel 9.0 Object Library:
Tools, References, check the box next to this entry.
Execute the code (cursor in module; press F5).
if you get an File Open error message, click Debug, then Continue.
When the code has executed, look in the RecoverPath folder. 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.

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

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

Rob

 

[VBAjedi]

Once again, Rob is a VBA HERO!

Not only did your code extract my modules, the way it accessed my workbook somehow triggered my sheet1_change code, which had a debugging Stop in it. Of course, the VBA project window opened in break mode. From there, I was able to export all my modules, forms, and sheet object code. Then, (since I figured it was my coding that corrupted the book somehow), I disabled my macros, did a Save As, and "Ta-da!", I got my workbook open again! Copied all of my sheets to a backup workbook, and I didn't even lose any DATA!

Thanks, Rob!
VBAjedi

 

[VBAjedi]

Just curious:

I just noticed that I accidentally named one of the Modules the same name as a Sub it contained. Think that's what corrupted my Workbook?

VBAjedi

 

[RobBroekhuis]

Glad to hear it helped you recover things! I've had workbooks corrupt for smaller sins, so your guess about what caused it is as good as mine...
Rob

 

[ajcarr]

I wish I had seen Rob's solution when I had this problem! I spent many hours rebuilding my Vba.

The problem that appeared to cause my corruption was doing a lot of line inserts using Vba on a very large worksheet - 50,000+ lines. This apparently caused Excel to go into overflow blocks to manage its memory. When the file is saved, these are tidied up into contiguous memory but blew Excel's internal table sizes on reload, caused Excel to either crash or hang. I had been carefully saving several previous versions as backup when developing the program and all the saved versions caused this problem,although the version which was loaded at the time worked perfectly.

 

[CEN7272]

Last Friday at 4:30 I encountered this exact problem (not a good way to start the weekend). I posted on another forum and and someone referred me to this thread. I copied the code that Rob listed, changed the file name and set up the reference for Excel 9.0 objects. When I tried to run it, it displayed Compile Error User-Defined type not defined and it highlighted the Sub line line and selected Dim XL As Excel.Application. I'm still fairly new to VBA and I'm not sure if there is something I should be changing. BTW, I'm using Word & Excel 2000 (if that matters). Any help on this is appreciated.

-Clay
claysdays.blogspot.com

 

[N1GHTEYES]

I know this thread is a bit old, but I just wanted to let Rob know how gratefull I am for his suggestion. It saved me having to re-do three day's work, and a lot of embarassment. I had given this spreadsheet to someone else to use, and he was not impressed when it would not open.

It seems like I'm not the only one who get's this problem (I've had it quite a few times before) and it is such a useful idea - so should it be in a FAQ?

ps - if this is preceded by a "blank" post I'm sorry - I have "morning fingers" at the moment.

 

[xlbo]

N1GHTEYES - from reading through this thread, I am convinced that it certainly should be a FAQ. If you would like to create it, please feel free, otherwise post back here and I will write it.

Rgds, Geoff

Well, he's kind of had it in for DPlank ever since he accidentally ran over his dog. Actually, replace "accidentally" with "repeatedly," and replace "dog" with "son."

Please read FAQ222-2244 before you ask a question

 

[shetlandbob]

agreed this defo should be a FAQ,

 

[N1GHTEYES]

xlbo,

Sounds to me like you just volunteered...

I would write it myself, but my spare time at home is currently being consumed by building a deck, and my spare time at work does not exist right now.

Besides, I'm shy. <grin>

 

[xlbo]

no probs - I have written 1 or 2 before....

Will just copy the code and give credit to Rob

Rgds, Geoff

A file that big? It might be very useful. But now it is gone

Please read FAQ222-2244 before you ask a question