Why doesn't Workbook.Save work?

[Guest]

I'm working on an Excel add-in and found some unexpected behavior that I hope
someone might be able to explain to me.

The add-in handles the WorkbookBeforeClose event, and possibly saves the
Workbook. This works fine if the close is done manually -- that is, by
clicking the 'X' button. But if the close is done programmatically through a
call to Workbook.Close, then saving doesn't work.

I've written some VBA that replicates the problem (though to be clear, the
add-in is more complicated, doing more than just a simple save).

First is a class called XLEvents.

Code:

Private WithEvents xlApp As Excel.Application

Private Sub Class_Initialize()
Set xlApp = Application
End Sub

Private Sub xlApp_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As
Boolean)
MsgBox "Closing"
Wb.Save
End Sub

I also have a macro that simply closes the Workbook. This isn't part of my
add-in's behavior -- it just illustrates the issue, and is something that
could happen if the user has their own macro, another add-in, etc.

Code:

Sub CloseWorkbook()
Application.ActiveWorkbook.Close
End Sub

This is the behavior I see:

Manual

1. Open the workbook.
2. Make a small edit.
3. Close the workbook by clicking the 'X'.
4. The "Closing" message box appears; click OK.
5. The workbook closes.

Programmatic

1. Open the workbook.
2. Make a small edit.
3. Run the "CloseWorkbook" macro.
4. The "Closing" message box appears; click OK.
5. Excel asks if I want to save.

#5 in the programmatic case is a symptom that the save didn't do anything in
this case. I can also verify that nothing got written to disk. Why would this
be? Is there a way to cause the programmatic behavior to match the manual
behavior (such that Save works)?

Thanks,
Eric

 

[Guest]

ActiveWorkbook.Save
Workbooks(1).Save
Workbooks("Book1").Save

 

[Tim Zych]

There's probably a more robust way to do this, but this should provide a
starting point. The key is to set Cancel = True.

Private Sub xlApp_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As
Boolean)

Cancel = True
MsgBox "Saving"
Wb.Save
xlApp.EnableEvents = False
Wb.Close False
xlApp.EnableEvents = True

End Sub

 

[Guest]

Are you suggesting that one of these other ways of getting the workbook
object will allow the save to work? Experimentally that doesn't seem to be
the case. None of these saves work either, in the programmatic scenario I
described. Do you mind sharing your thinking?

Thanks,
Eric

 

[Tim Zych]

I missed the part about programmatically closing the workbook. Will see if I
can find a way to get this to work.