deleting worksheets corrupts files

I have various modules which open several workbooks (one by one), make
changes, then save & close them. Whenever I run a module in which
sheets are deleted via...

Application.DisplayAlerts = False
Worksheets(strMySheet).Delete
Application.DisplayAlerts = True

the file becomes corrupt. It saves and closes ok, but when trying to
open the file later, Excel crashes, giving the message:

Excel has caused an error in EXCEL.EXE. Excel will now close.

I've tried code cleaner, and even pasting the code into notepad, then
back into a new module, but neither has worked. I'm having to open the
files using OpenOffice, then saving in a different format then
re-opening with Excel. But I'm working with over a few hundred files
so I don't really have time to do this.

I'm not positive it's the sheet deletion that does it, but I've pretty
much narrowed it down to that because it's common among all the problem
mods and absent from all the others. Has anyone ever had trouble with
deleting sheets / file corruption? Anyone have any suggestions?
Thanks.

Hi

I came across the same problem this morning. My macro saves a copy of my
'master' workbook and then turns it into a report to send out, (doing things
like copy-paste values, delete sheets, etc.). As you described, the files
save, and close fine, but then Excel 2000 crashes when I try to re-open.

However, Excel 2002 is able to open the offending files. I've been into
one, and gone into the VBE. in the project explorer pane, I can see the
problem. Under the subfolder "Microsoft Excel Objects" are the two sheets I
intended to be there, and a "ThisWorkbook" module. Bizzarrely though, there
is also one of the sheets that my macro deleted ("Data"), and the icon next
to it is like that of "Thisworkbook".

Presumably, Excel 2000 sees two "ThisWorkbook" type modules and doesn't know
which one is real. Excel 2002 copes.

What I don't know is why the "Data" sheet in particular was not deleted
properly. The line of code making sheet deletions used an array of sheet
names:
sheets(array("sht1","sht2","data","sht4")).delete

Any help much appreciated
Thanks
Rob




People using Office 2002 were able to open the file

"[email protected]" wrote:

> I have various modules which open several workbooks (one by one), make
> changes, then save & close them. Whenever I run a module in which
> sheets are deleted via...
>
> Application.DisplayAlerts = False
> Worksheets(strMySheet).Delete
> Application.DisplayAlerts = True
>
> the file becomes corrupt. It saves and closes ok, but when trying to
> open the file later, Excel crashes, giving the message:
>
> Excel has caused an error in EXCEL.EXE. Excel will now close.
>
> I've tried code cleaner, and even pasting the code into notepad, then
> back into a new module, but neither has worked. I'm having to open the
> files using OpenOffice, then saving in a different format then
> re-opening with Excel. But I'm working with over a few hundred files
> so I don't really have time to do this.
>
> I'm not positive it's the sheet deletion that does it, but I've pretty
> much narrowed it down to that because it's common among all the problem
> mods and absent from all the others. Has anyone ever had trouble with
> deleting sheets / file corruption? Anyone have any suggestions?
> Thanks.
>
>