Excel X "service release 1" document no longer saves

[duncan]

I'm getting a "Document not saved" message when I try saving a
particular workbook. This has not been a problem before, and I have
not changed its file path (Microsoft's support site says the message
appears if the file path is longer than 254 characters .. not the case
here.)

I believe the problem is that the file has grown too big - the only
change is that I added 25 (yes, 25!) worksheets. But I can't delete
them, because it won't save the changes.

There is another symptom - I can no longer view the workbook's
extensive macros, although they still run. This means that I can save
the worksheets to a new document .. but can't move the macros over.

Help, please!

 

[JE McGimpsey]

I'm getting a "Document not saved" message when I try saving a
particular workbook. This has not been a problem before, and I have
not changed its file path (Microsoft's support site says the message
appears if the file path is longer than 254 characters .. not the case
here.)

I believe the problem is that the file has grown too big - the only
change is that I added 25 (yes, 25!) worksheets. But I can't delete
them, because it won't save the changes.

There is another symptom - I can no longer view the workbook's
extensive macros, although they still run. This means that I can save
the worksheets to a new document .. but can't move the macros over.

This sometimes happens to me when the VB run-time gets confused
(generally, but not always when I've included event macros). Sometimes
simply going into the VBE and clicking the "Reset" button is enough to
free things up to allow a save.

Not sure what you mean by "can no longer view the workbook's extensive
macros". Have the modules disappeared? Do you get blank modules when you
try to open them?

 

[duncan]

This sometimes happens to me when the VB run-time gets confused
(generally, but not always when I've included event macros). Sometimes
simply going into the VBE and clicking the "Reset" button is enough to
free things up to allow a save.

Not sure what you mean by "can no longer view the workbook's extensive
macros". Have the modules disappeared? Do you get blank modules when you
try to open them?

I tried "Reset" but no luck. When I say I can't see the macros, I mean
when I got Tools> Macros> Macros... The resulting Macro window shows
only the macros in other open worksheets - i.e. "Personal Macro
Worksheet" - not for the worksheet that is causing me grief. The
Projects window in VB Editor shows "sheets" and "modules" relating to
the workbook, but the sheet/module windows are indeed blank, or if I
click on their pop-up Worksheet options, I get "File not found".

 

[JE McGimpsey]

I tried "Reset" but no luck. When I say I can't see the macros, I mean
when I got Tools> Macros> Macros... The resulting Macro window shows
only the macros in other open worksheets - i.e. "Personal Macro
Worksheet" - not for the worksheet that is causing me grief. The
Projects window in VB Editor shows "sheets" and "modules" relating to
the workbook, but the sheet/module windows are indeed blank, or if I
click on their pop-up Worksheet options, I get "File not found".

Sounds like a truly corrupted workbook. If you have access to a PC,
XL2003 has a more sophisticated file recovery capability than MacXL04,
so you might try that. Rob Bovey's Code Cleaner

http://appspro.com

would then be in order, but I'm not particularly hopeful.

Obviously, if you have backups of the workbook, you could try to get the
macros out of that workbook instead.

I know it's shutting the barn door after the horse has bolted, but ANY
important XL-based application should not only be regularly backed up,
but very serious consideration should be given to completely separating
the data and code by putting the code in an add-in, and using it to
generate or manipulate data workbooks. There's far less chance of
corruption in that case. Most non-trivial commercial apps do this -
there's no code at all in the data workbooks, and the add-ins provide
menu bar and toolbar controls for manipulating the data.

 

[duncan]

Yes, I've decided the file must have got corrupted. I did indeed have
a back-up, although a little out of date. In the end I used it,
though, to build up a new workbook. I shall back-up more often though!
I'm intrigued by the idea of using an add-in, and shall look into
that.

Thanks for your help/advice!