Need help on How to programatically Import VBA Project Excel Objects, Forms, Modules

[JMMach]

I have an Excel 2000 workbook that I have worked on for two days.
There was no indication that there was any problem when saving. And I made a
backup copy.
Problem is this:
When I try to open the file now it will not open, and
Windows 2000 gives the following error message:
"
EXCEL.EXE - Application Error
The instruction at "0x3004c359" referenced memory at "0x00bc1384". The
memory could not be "read".
Click on OK to terminate the program
Click on Cancel to debug the program
"
I click on OK and the program crashes.
Same thing with my backup copy.

This is the second time it has happened with this file, and I've had to
recreate it. I do not know what is the cause of this problem.
Is there a way programatically to import the VBA project Excel Objects,
Forms, and Modules without having to formally open the file?
I'm hoping that the file contents can be accessed much like linking is able
to access the contents without formally opening the file.
So I'm thinking that if I can successfully import one object at a time, I
will be able to recover a lot of my work, and save a bunch of time, and
perhaps isolate where the problem is coming from.
I'm also open to any other suggestions someone might have.
Thanks in advance

 

[steve]

See if you can open the file from Excel: File > Open
but hold down the Shift key to prevent the macros from engaging.

Now go to the VBE and comment out all your macros. (providing you
were able to open the file) You can customize the toolbar and add a
'Comment Block' and an 'Uncomment Block' button from the Edit catagory.
This way you can do a Ctrl + A to select all the code in a module and just
click once on the button.

If you were able to get this far - you are halfway there.

Save the workbook and reopen it. (provided ALL the code is commented out.).
Remember to look at all Standard modules, Sheet modules, This-
Workbook module, form modules.

Now things to look for and fix:
Module names, macro names, variable names that use words reserved
by Excel - such as Sum, Cells, Range ..... If unsure, click on the
word
and hit the F1 key. An unreserved word will not result in finding
any
help.
Module names and code names that are the same.
Variables set as Public and also Dimmed in a macro. Use one but not
the other. The easiest way to find these is to find all instances
of
Public in the project and note the variables. Than find all
instances of
Dim or Static and compare what you find.

These are the top 3 reason's that have crashed my Excel.

Recently I had some defined names that used an Offset function to create
self-expanding ranges. For some reason these were causing my Excel to
crash. (Spent several weeks building and rebuilding before I found this)

Also - test your code (macro by macro) in a dummy book before committing
them to your book...

Best of Luck!!! Post back on your progress!!!

 

[Tom Ogilvy]

Some have suggested using Open Office (formerly Star Office). It is a free
(though large) download and I have seen it stated that it will preserve the
VBA (although I haven't tried it myself).

 

[JMMach]

No go Steve. Holding the Shift key while reopening did not alter the result.
I do not have any macros running on startup; there is some problem with
Excel attempting to open the file.
I can create links back to that file to get the contents of every cell -
that means the hard data and the results of formulas, but not the formulas
themselves.
That's why I was thinking that if I could import from that file various
objects, then I could reconstruct the file.
Do you know if that is doable?
Any other suggestions?
Thanks
TTFN
JMMach

 

[JMMach]

Thanks Tom, that worked ... partially!
I was able to open the file with StarOffice 5.2 spreadsheet, and save it
again in Excel'97 format.
Then I was able to open the file with Excel 2000.
What I recovered where the following:
spreadsheets & formulas
UserForms in VBE
What I lost were the following:
DataValidation in the spreadsheets
VBA code behind the UserForm
All the VBA Modules
So the lesson here is to export the Modules and Userforms because they can
be so easily imported again.
Is it possible that the DataValidation is causing the files to become
corrupted?
Do you know if there is a way programatically to import the VBA project
Excel Objects, Forms, and Modules without having to formally open the file?
Is that do-able?

TTFN
JMMach

 

[Tom Ogilvy]

On april 23, 2003, Chip Pearson said:

From: Chip Pearson ([email protected])
Subject: Re: Errors Occured During load


View this article only
Newsgroups: microsoft.public.excel.programming
Date: 2003-04-23 04:21:54 PST

Will OpenOffice preserve the macro code and forms etc?

Yes, it does.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)

I can't find the post, but I believe I recall from a post that you have to
specify something.

As far as I know, you can't work with VBA on a closed file, either importing
or exporting.

Now, if you could program the compound document format, that might not be
correct.

 

[JMMach]

I used StarOffice 6.0 at work to open the troublesome Excel file, and saved
it.
When I opened that file in Excel, I found that the VBA Code was preserved,
and so were the (Group &) Outlines.
The DataValidation was still lost. And a Toggle Button in a spreadsheet was
changed to the Button of the Forms toolbar variety.
So the version of StarOffice does make a difference.
Bottom line: a ton of time was saved & a lot of additional frustration was
averted. Thanks to you!!
It sure would be nice if Microsoft fix that Win2000 "could not be "read"
'bug' or provide a recovery utility for such instances.

 

[Tom Ogilvy]

Well, they did provide a recovery utility built into Excel in xl2002 (assume
it is still there in xl2003). If you have that at work, you might give that
a whirl as well.