Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why doesn't Workbook.Save work?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why doesn't Workbook.Save work?
ActiveWorkbook.Save
Workbooks(1).Save Workbooks("Book1").Save "Eric Smith" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why doesn't Workbook.Save work?
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 "Eric Smith" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why doesn't Workbook.Save work?
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 "JLGWhiz" wrote: ActiveWorkbook.Save Workbooks(1).Save Workbooks("Book1").Save "Eric Smith" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why doesn't Workbook.Save work?
I missed the part about programmatically closing the workbook. Will see if I
can find a way to get this to work. "Tim Zych" <tzy---ch@NOSp@mE@RTHLINKDOTNET wrote in message ... 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 "Eric Smith" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Save As and Save Buttons Don't Work | Excel Discussion (Misc queries) | |||
Workbook.Save triggers event Workbook_BeforeSave and things don't work! | Excel Programming | |||
Everytime I save my workbook, my hyperlinks will no longer work. | Excel Discussion (Misc queries) | |||
I wish to save my Excell work in my work sheets | Excel Worksheet Functions | |||
Is there away to keep "auto save" from jumping to the first work sheet in the work book? | New Users to Excel |