Results 1 to 9 of 9
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Retrieve VBA from corrupted workbook (Excel 2000)

    I've been toiling over a big project on a 6MB workbook for over a week and was 90% done when (of course) the workbook decides it doesn't want to open. Even disabling macros doesn't work, it just generates an Excel crash. It's been about 3 days since I saved off the VBA code (exporting the modules/forms) so my copy of the code isn't up to date.

    I have nothing in xlStart and no Add-ins, Excel opens fine for other workbooks just this one. <img src=/S/weep.gif border=0 alt=weep width=21 height=16> I even went so far as to uninstall/re-install Office 2000, no luck.

    This is a 6 MB workbook with 33 sheets but I don't care about retrieving the worksheets (I've got that covered), just the code behind it all.

    Is there code I can write from a new workbook to export/copy the code modules out of ths corrupted workbook? The workbook is protected but I do know the password. This isn't the first time Excel has puked <img src=/S/puke.gif border=0 alt=puke width=60 height=15> on me in a critical hour. The lesson is - Making back ups isn't enough, ya gotta save the code separately too. I know you can write VBA with VBA but don't know if you can look into a corrupted workbook.

    I also tried opening the workbook in xl97 as sometimes that works, but not this time.

    I'm thoroughly depressed and disgusted having wasted an entire day.
    Deb <img src=/S/bwaaah.gif border=0 alt=bwaaah width=123 height=15>

  2. #2
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieve VBA from corrupted workbook (Excel 2000)

    I've been through this ordeal recently but with a Publisher file. I found (via Woody) http://www.officerecovery.com. They just might have what you need if spending a little <img src=/S/money.gif border=0 alt=money width=17 height=15> is in order.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieve VBA from corrupted workbook (Excel 2000)

    Yes, that's a good idea. I went and downloaded their Excel demo program, installed the add-in and then ran it. Result??? Failed. I got an error 'wrong password' partly through Pass 2. It never asked me for any password. The workbook is protected from being opened as well as the workbook itself. I know both passwords.

    I sent the company email asking them what that message could mean (other than my file is too corrupted). Even though I need the VBA not the data, if I could just open the file, I can get the VBA out myself.

    Thanks for the link... might come in handy in other cases.

    Deb <img src=/S/disappointed.gif border=0 alt=disappointed width=15 height=15>

  4. #4
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieve VBA from corrupted workbook (Excel 2000)

    Here's another idea: open the workbook in a good text editor (one with a Hexidecimal mode) and see if you can find the code. I'm not sure if Excel "hashes" the VBA into unreadable text or if it just leaves it as text. If it does, you should see it in a text editor. JUST DON'T SAVE THE FILE!!
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  5. #5
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieve VBA from corrupted workbook (Excel 2000)

    Deb,

    I've SOMETIMES been able to recover a corrupt workbook by opening it in IE.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  6. #6
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieve VBA from corrupted workbook (Excel 2000)

    I'll try anything now (although I've already started re-creating the code). <img src=/S/nuts.gif border=0 alt=nuts width=15 height=15>

    I did re-run the Excel Recovery program that Kevin suggested and got a bit further but it too crashed (running it from Start/Programs failed sooner than running the Add-in from Excel).

    When I opened IE and pointed it to my corrupted .xls file (from File/Open in IE), it launched Excel which suprised me. I figured it would open it in IE as I've seen it do before.Oh, well the GOOD news is that it worked enough for me to export those VBA modules I wanted!!!

    I then tried doing SaveAs and it crashed but that's ok, I got what I wanted.

    <img src=/S/megashout.gif border=0 alt=megashout width=33 height=17> LESSON LEARNED - back up your VBA modules separate from your workbook after every significant code change and before you save the file.

    Thanks for all the ideas! <img src=/S/thankyou.gif border=0 alt=thankyou width=36 height=24>
    Deb

  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Retrieve VBA from corrupted workbook (Excel 2000)

    Hi Deb,

    Take a look at this, it might help. It's from http://www.erlandsendata.no/english/downloads/tools.htm

    Cheers
    Attached Files Attached Files
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. #8
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieve VBA from corrupted workbook (Excel 2000)

    You might be able to try a save as HTML. Under Excel 2000 +, the HTML is sufficiently good enough to keep most things (including macros). Then open the HTML and save it again as an Excel spreadsheet.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  9. #9
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieve VBA from corrupted workbook (Excel 2000)

    Thanks for that idea, but Excel crashes before the file is completely open. The progress bar doesn't make it but half way across and then Excel crashes. I was able to open it in IE which then opened Excel and somehow it managed to make it past whatever was corrupted. I then exported out my VBA modules and closed the file. <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21> <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>

    I tried a File/SaveAs from Excel but it crashed too. Oh, well I didn't care since I had the code files and will always save them out before exiting my workbook. Just saving a copy of the workbook isn't enough if you can't open it to get the code modules. Lesson Learned!

    I've had these files go corrupt on me many times so Excel must have some internal time bomb counter that goes off to courrupt itself after so many viewings. It only seems to the case where there's code though, since macro-less spreadsheets don't seem to crash like this. I've had projects that took over a year to complete so you can imagine the amount of code changes that took place and the level of internal crap that builds up.

    The internal garbage collection is garbage itself. <img src=/S/puke.gif border=0 alt=puke width=60 height=15>

    Thnx, Deb <img src=/S/thankyou.gif border=0 alt=thankyou width=36 height=24>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •