I have an Excel file under development which causes
a GPF every time I open the VBE and want to look
at the macros. (GPF happens at compile
time when opening a module).
I finally could export the UFs and Sheets out of this
file, now I want to extract the code itself as raw text.
The file is unprotected, and I already tried disactivating
the macros, opening via VBA, etc ... I am out of idea !
Please help !
Patrick
I keep this code in my Personal.xls workbook. I created a toolbar button
that activates the subroutine. It cycles through each component in the
ActiveWorkBook and exports the code to text files in my desired
subdirectory. I believe that you need a reference to the VBA
Extensibility library to use it. Each time that I make changes to
programs, I click the toolbar button and save the changes to text files.
Good luck
Paul
-----------------------------
Sub ExportAllVBA()
Dim VBComp As VBIDE.VBComponent
Dim PartPath As String
Dim NextPartPath As String
Dim Sfx As String
PartPath = "\\Pentium iii\illusive\Money Files\Computer
Helpers\Modules\"
NextPartPath = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4)
For Each VBComp In ActiveWorkbook.VBProject.VBComponents
Select Case VBComp.Type
Case vbext_ct_ClassModule, vbext_ct_Document
Sfx = ".cls"
Case vbext_ct_MSForm
Sfx = ".frm"
Case vbext_ct_StdModule
Sfx = ".bas"
Case Else
Sfx = ""
End Select
If Sfx <> "" Then
VBComp.Export _
FileName:=PartPath & NextPartPath & "\" & VBComp.Name & Sfx
End If
Next VBComp
End Sub
--------------------
Thanks for the help, but it failed...
GPF still occurs.
Any other idea ?
Is there any tool that can prevent Excel from
compiling the file in the VBE ?
:(
Patrick
You may (among truckloads of other possibilities) have a troublesome entry
in your registry. Try logging on as another user and see if it opens.
HTH. Best wishes Harald
Patrick Penet <php...@club-internet.fr> skrev i
news:#dV4sJ63AHA.1064@tkmsftngp05...
Thanks, Harald but no chance...
I tried opening the file on another PC with no success.
I am longing for "truckloads of other possibilities"
:(
Patrick
> I am longing for "truckloads of other possibilities"
I wish I had them (possibilities as in "may possibly go wrong when Microsoft
Excel interacts with Microsoft Windows", not in "possible solutions"...).
Did you try opening windows in safe-mode ?
Or see if you can find a binary editor with a pane that shows text in a
copy-able window. The result will probably look quite messy, so this is a
very-last-chance-option.
Best of luck from Harald
Date: Wed, 7 Jun 2000 09:28:21 -0400
Reply-To: Microsoft Excel Developers List <EXC...@PEACH.EASE.LSOFT.COM>
Sender: Microsoft Excel Developers List <EXC...@PEACH.EASE.LSOFT.COM>
From: Brian Murphy <bmu...@XLROTOR.COM>
Subject: recovering Modules from corrupt files
The following is from a posting April 30, 1999 by Rob Bruce. I thought it
was worth repeating. Run this macro from Word VBA to recover modules from
corrupt Excel files.
Cheers,
Brian Murphy
Austin, Texas
'\for this macro to run you need to establish a reference to the
'\Microsoft Excel 8.0 Object Library
'\also, if you get a File Open error message, hit Debug, then Continue
Sub Recover_Excel_VBA_modules()
Dim XL As Excel.Application
Dim XLVBE As Object
Dim i As Integer, j As Integer
Set XL = New Excel.Application
XL.Workbooks.Open FileName:="h:\CR - Portfolio Template.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:="C:\temp\vbe_"
& (100 + i) & ".txt"
Next
XL.Quit
Set XL = Nothing
End Sub
--------------------------------------------------------------------------
The EXCEL-L list is hosted on a Windows NT(TM) machine running L-Soft
international's LISTSERV(R) software. For subscription/signoff info
and archives, see http://peach.ease.lsoft.com/archives/excel-l.html .
Robert also provided these comments in a discourse on this:
"Bruce, Robert (London)" <RBr...@uk.imshealth.com> wrote in message
news:9A23092BA4DBD111816D...@imsuklndsx1.lnd.uk.imshealth.c
om...
> Hi Tom,
>
> I originally developed this in desperation as a quick and dirty routine
> and was so surprised that it worked that I posted it to the EXCEL-L
> mailing list (this was sometime around a year and a quarter ago). I
> pointed out at the time that it only works in a pretty specific
> situation where something corrupt in the file is causing Excel to be
> unable to _display_ it - hence the use of a non-visible instance of
> Excel created from a COM client application. You might be surprised by
> the number of 'corrupt' files that open successfully if they don't need
> to be drawn on the screen (this may be related to the fact that Mac
> users can often open files that refuse to open in Excel for Windows).
>
> Rob
"Harald Staff" <harald...@nrk.no> wrote in message
news:OyPJnf63AHA.1064@tkmsftngp05...
I think this the case. Some chunky code is sending an
error into an API call...
Thanks, Tom but it doesn't work either...
even from Excel or Word, still a GPF comes when
the macro is reading the code into a module.
Even if I put the code into a variable (because
Export is actually compiling before exporting)
...sigh
I now will try to open W98 in safe mode...
Any other idea ?
Patrick
John
"Patrick Penet" <php...@club-internet.fr> wrote in message
news:uZSqSq63AHA.2056@tkmsftngp05...
I tried all that was suggested above, unsuccessfully,
but I thank anyhow everyone for helping.
I am getting to the point where I have to rewrite all the
code, before and for those who would add some other tips,
here is a detailed description :
-- I dont have any addin in Excel, except the fabulous
Smart Indent.
-- The file is not protected, and the macros are not protected
and is not that big (200ko).
-- Although it takes a unusual long time, I can open the
file with or without macros activated, both by clicking on the
filename or by opening from Excel.
-- Once opened, the behaviour of Excel is normal (calculation).
-- Once opened, some simple macros are working properly and
some others cause the GPF.
-- I can open the VBE but jumping into a module (to see the code)
will cause a GPF.
-- The code of this project is simply inviewable causing immediate
GPF, even trying to export the code (export the UFs and Sheets was
successfull, even from an external sub (as suggested), even from Word.
Since I am programming Excel I had a lot of GPFs in the past, but
always I could cure corrupt codes and files. This time, unless some
of you MVPs give me the right trick, I think I will fail...
Patrick.
Delete them.
re-import them.
Did that do anything? I guess the modules don't always clean up after
themselves and leave a lot of detritus hanging around. The exporting/importing
cleans them up. (Once they're exported, they're just ASCII text.)
If you do this a lot, try Rob Bovey's Code Cleaner from
(You may want to copy|paste your worksheets to a new workbook, too.)
Failing that, if you know what routine causes the GPF, you could post it and see
some real suggestions.
--
Dave Peterson
ec3...@msn.com
I thank you for giving your contribution to this old thread.
But the point is that the GPF comes when I try to export
the modules...
I think I'd need 'something' that may not exist : ie something
that would force the VBE to act as simple editor, without
interpreting or compliling any code... I may be dreaming...
Patrick
As I just discovered there is a specialized ng for
GPFs, even if it is a bit 'cross posting', I will start
a new thread there... (cuz I really hate re-typing
code !)
:)
Cheers and regards to all
Patrick