Saving workbook in VBA Excel causes errors

[sickbay]

I am using Excel 2000.
I am trying to do something very simple. I am using application.saveas to save a workbook. This saves ok but when I try and open it up again, I get Excel.exe has generated errors.....
The workbook will however open in Excel XP!
If I save the same sheet manually using file/save as there are no problems.

This is driving me round the bend.

Any ideas anyone?

 

[RobBroekhuis]

You say you use
application.saveas
Do you mean
activeworkbook.saveas?
As far as I know, the saveas method does not apply to the application object. Do you use any parameters (besides the filename) as arguments to the method?
Rob

 

[sickbay]

Appologies! I am using activeworkbook.saveas (told you it was doing my head in!)

I have now figured it out.

The problem was, I delete several sheets from the workbook before it is saved. The deleted sheets are still listed in the VBA project as objects (although they are grayed out) when the workbook is saved.
I believe that VBA is looking for these deleted sheets next time the saved book is opened.

My solution is to hide the sheets and not delete them!

Does anyone know if my reasoning is correct?

Thanks for the assistance

 

[RobBroekhuis]

Sounds strange. The VBAproject objects should be deleted along with the sheets. But if it works for you now, that's what matters
Rob

 

[mrrawdeal]

microsoft knowledgebase article Q265305 may give you the answer.


XL2000: Workbook Corrupted If You Delete, Copy, and Save in Macro Procedure