Sorry for the long post but this issue has me at wits end.
I have a rather large (600mb) Excel spreadsheet in MyDocuments which first started crashing about a week ago. The workbook is made up of five main sheets, each sheet is named after a year going back to 2007 and these sheets each contain approx 450,000 rows
and maybe 20 columns of data. There are also 4 pivot tables each with a couple of fields and a few small misc sheets (one sheet hidden not password protected), a single small form and maybe a half dozen vba modules several of which are approx between 100-200k.
Problem first manifested when I imported some new data, closed the form, saved the workbook, copied the workbook off to an external hd (as a backup) and then renamed the source file. When subsequently trying to open the renamed file I received the appcrash
on vbe7.dll. Continued attempts to open the file and/ or repair the file upon opening fail. My only choice is to then delete the file and restore a backup copy of the file -- this known good backup will open. I have tried all sorts of suggestions but none
seem to work for very long, inevitably the appcrash rears its head.
I thought I was onto something last night when I had the file in the unusable state where it would not open without crashing. I then uninstalled SP1 for Office 2010 and uninstalled update kb 2553385 which pertains to vbe7.dll. After the uninstallation of
those items the file could be opened without issue. I spent several hours working with the file extensively last night, saving and re-opening without any issue whatsoever. Then this morning I copied the file off as a backup satisfied that I finally had another
known good backup. I renamed the source file and tried to open it, again appcrash on vbe7.dll!
Any ideas? Obviously something about uninstalling SP1 temporarily 'fixed' things but only temporarily. Does this point to some type of registry issue? Like maybe a portion of the registry is cleaned out or reset and then some series of actions with this
large file causes some 'bad' registry entries?
I know that not everyone has this option, but I use Windows 7/Excel 2010 on a virtual machine so that I can use the full 4Gb of memory on the machine not the 2Gb that XP limited me to. I still have access to WinXP too though.
I have about 20 workbooks saved on a network drive each of which are opened, refreshed and closed by a 'Master' workbook, residing on the virtual machine, overnight. Most of those 'Child' workbooks are simple xlsx ones but a few have their own macros for
self serving a limited amount of data retrieved from the database should anyone not have been running on Win 7 at the time those macros were created.
Anyway, after 2 years of running in this scenario I suddenly started (May 2014) getting the dreaded AppCrash error.
It only affected the xlsm extension files, not the xlsx. It also affected them in different ways.
Some would crash upon opening and others would crash after their update but whilst saving.
The VBA in the master workbook could not pick up any error code in order to debug when this AppCrash happened either.
Luckily I still had Excel 2007 running on WinXP so all I had to do to resolve the situation was open the file on the WinXP box, save it as another file, rename it to the original and open again in Win 7.
As I don't allow automatic updates of Windows, I know that there were no updates that had introduced this problem for me.
Whatever the cause, once the file had been opened and saved within XP the AppCrash event stopped. What this tells me is that there is something going on within these files, not within Excel itself. I can prove this, as by saving the crashing files in their
'untouched by WinXP' state, I can re-create the AppCrash issue simply by opening those files back up again.
I've tried running Excel in compatibility mode (Vista SP2, Vista SP1 and Vista) and opening the 'crashing' files but they crash the same as if in Win 7.
Hopefully someone may find this info useful and find a way to help people who encounter this and who don'y have XP running!