Strange SaveAs behavior

Shankar_R · February 11th, 2010, 12:47 PM

I am developing an application and have run into a weird problem. Basically it boils down to something given below-

When I close a file, I want it to be automatically saved under a different name.

In ThisWorkbook module, if I have the following code,

Code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Msgbox "Closing file" 
SaveAs "c:\Newname.xlsm"
End Sub

then it works properly if I use the Close command of the application or the 'x' button of the workbook window.

But if I have the following sub in a module,

Code:

Sub VBA_Close()
    ThisWorkbook.Close
End Sub

and call this, it triggers the previous sub (as confirmed by the message box), and the file closes, but no new file gets created.

Why is this?

My code is not the same as the above. It is an add-in and the workbook to be closed is a different workbook from the add-in, and the second sub corresponds to a RibbonX command. But I am observing the same behavior.

I guess I can include the SaveAs in the second sub instead of relying on it to trigger the event for the first, but then my code will be more complicated.

Thanks.

Shankar

Shasur · February 11th, 2010, 11:07 PM

Hi Shankar

Do you mean to save the .close method is executing on a different workbook?

If that is the case you need to have a look at the module which calls ThisWorkbook.Close. ThisWorkbook is the workbook in which the module resides. If this not you intended then you can use

Workbooks(1).Close

or

Workbooks("Sample.xls").Close etc

Cheers
Shasur

Shankar_R · February 12th, 2010, 02:15 AM

Yes, I am doing it the way you have indicated in my add-in. The example I have provided is for illustration only. Thanks.

Shasur · February 12th, 2010, 02:22 AM

Is your problem solved now or are you still facing some issues

Cheers
Shasur

Shankar_R · February 12th, 2010, 02:29 AM

I have a workaround. But I am just curious to know what's the reason for this behavior.