Microsoft KB Archive/928283

= Error message when you use a VBA macro to save a shared Excel 2007 workbook: &quot;Application-defined or object-defined error&quot; =

Article ID: 928283

Article Last Modified on 10/15/2007

-

APPLIES TO


 * Microsoft Office Excel 2007

-



SYMPTOMS
When you try to run a Microsoft Visual Basic for Applications (VBA) macro that saves the current workbook as a shared workbook in Microsoft Office Excel 2007, you receive the following error message:

Run-time error '1004'

Application-defined or object-defined error.



CAUSE
This issue may occur if the following conditions are true:
 * The default save format is set to Excel 97-2003. Additionally, a new workbook is added, and the macro uses the ProtectSharing method or the SaveAs method against the new workbook in the Excel 97-2003 file format.
 * The ProtectSharing method uses the FileFormat property set to xlExcel8.



WORKAROUND
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure. However, they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

To work around this issue, use one of the following methods.

Method 1: Set the default file format to xlWorkbookNormal
To use either the ProtectSharing method or the SaveAs method in a VBA macro, set the default file format to ExcelWokbookNormal early in your VBA macro.

The following VBA code is an example of how to use the DefaultSaveFormat method to set the default file format to xlWorkbookNormal. Application.DefaultSaveFormat = xlWorkbookNormal Note To manually set the DefaultSaveFormat method, follow these steps:
 * 1) Click the Microsoft Office Button, and then click Excel Options.
 * 2) Click Save.
 * 3) In the Save files in this format box, click the format in which you want workbooks to be saved by default.
 * 4) Click OK to close the Excel Options dialog box.

Method 2: Use the SaveAs method with the AccessMode parameter
To use the SaveAs method, use the AccessMode parameter.

The following VBA code is an example of how to use the SaveAs method with the AccessMode parameter. ActiveWorkbook.SaveAs FileName:=&quot;filename.xls&quot;, AccessMode:=xlShared, FileFormat:=xlExcel8 Note The value of the FileFormat property must be the same value as the DefaultSaveFormat method.

Additional query words: excel2007 excel2k7 excel12 xl2007 xl2k7 xl12

Keywords: kberrmsg kbtshoot kbmacro kbprogramming kbautomation kbprb kbvba kbpubtypekc kbexpertisebeginner KB928283

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.