Handling the WorkbookAfterSave Event in VBA
in [Programming]
|
From: Steve Flaum on 16 May 2010 15:00 I can handle the Excel WorkbookAfterSave event in VB 2010 as explained at http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.appevents_event.workbookaftersave(office.14).aspx. I can also handle built-in events of the Excel application in VBA as described at http://msdn.microsoft.com/en-us/library/aa140934(v=office.10).aspx. However, I cannot figure out how to handle the WorkbookAfterSave event in VBA. If I use a statement such as: Private WithEvents ExcelApp as Application a number of application events are available, but WorkbookAfterSave isn't one of them. I suppose I could create a VB 2010 component which catches and rethrows the event, but there must be a better way. Can anyone tell me what it is? Thanks.
From: Chip Pearson on 16 May 2010 18:29 Because application events are for all open workbooks, you need to use the WorkbookAfterSave event, which is called when any workbook is saved. The WB parameter references the workbook that was saved. E.g., in Class1, Public WithEvents XLApp As Excel.Application Private Sub Class_Initialize() Set XLApp = Application End Sub Private Sub Class_Terminate() Set XLApp = Nothing End Sub Private Sub XLApp_WorkbookAfterSave(ByVal Wb As Workbook, _ ByVal Success As Boolean) MsgBox "AfterSave: " & Wb.Name End Sub Then, in ThisWorkbook: Private XLEvents As Class1 Private Sub Workbook_Open() Set XLEvents = New Class1 End Sub As long as the workbook containing this code remains open, you'll get WorkbookAfterSave events when any workbook is saved. Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel, 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com On Sun, 16 May 2010 15:00:33 -0400, "Steve Flaum" <sflaum(a)flauminc.com> wrote: >I can handle the Excel WorkbookAfterSave event in VB 2010 as explained at >http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.appevents_event.workbookaftersave(office.14).aspx. I >can also handle built-in events of the Excel application in VBA as described >at http://msdn.microsoft.com/en-us/library/aa140934(v=office.10).aspx. >However, I cannot figure out how to handle the WorkbookAfterSave event in >VBA. If I use a statement such as: > > Private WithEvents ExcelApp as Application > >a number of application events are available, but WorkbookAfterSave isn't >one of them. > >I suppose I could create a VB 2010 component which catches and rethrows the >event, but there must be a better way. Can anyone tell me what it is? > >Thanks.
From: Chip Pearson on 16 May 2010 18:32 I somewhat misread your post. My previous reply is of limited relevance. Where are you putting the Private WithEvents ExcelApp As Application declaration and where are you initializing the ExcelApp variable? The code I posted in my previous reply works as expected, so it might not be completely useless. In that code, I use a separate class module to handle the XLApp and its events, but this code could be placed directly in ThisWorkbook if desired. Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel, 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com On Sun, 16 May 2010 15:00:33 -0400, "Steve Flaum" <sflaum(a)flauminc.com> wrote: >I can handle the Excel WorkbookAfterSave event in VB 2010 as explained at >http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.appevents_event.workbookaftersave(office.14).aspx. I >can also handle built-in events of the Excel application in VBA as described >at http://msdn.microsoft.com/en-us/library/aa140934(v=office.10).aspx. >However, I cannot figure out how to handle the WorkbookAfterSave event in >VBA. If I use a statement such as: > > Private WithEvents ExcelApp as Application > >a number of application events are available, but WorkbookAfterSave isn't >one of them. > >I suppose I could create a VB 2010 component which catches and rethrows the >event, but there must be a better way. Can anyone tell me what it is? > >Thanks.
From: Steve Flaum on 17 May 2010 17:38 Thanks very much, Chip. I think I may be able to adapt your suggestion to my requirement. However, since you asked for a clarification -- and since I need all the help I can get -- here it is. My original post simplified my problem somewhat. I thought that would make it easier to respond, and I could extract the info I need from that response. Here's a fuller description. I wrote a VB 2008 exe which instantiates Excel & reads a workbook. Lets call this the "maaster workbook". This master workbook includes code like the following: Private mCCalc As Object Public Sub Workbook_Open() 'Open a DLL written in VB 6. Set mCCalc = CreateObject("SomeName", "") 'Pass the DLL a reference to the Excel application. mCCalc.Init Application End Sub Later the DLL creates & manipulates new workbooks. Because there can be a lot of them (e.g. 1,000 workbooks), it pages some of them out to disk and removes them from RAM (ie. from the Workbooks collection) to save Excel resources when it isn't using them. It then reads them back when it needs them, perhaps paging other workbooks to disk. Unfortunately, although the program tries to predict which workbooks won't be needed soon, it cannot do this accurately. Therefore, a workbook might be written, deleted, and read in quick succession. This write/delete/read cycle can crash Excel. I believe that this is caused by reading a workbook before the last save is finished. My reason for this belief is that adding a 5 second time delay before reading each workbook prevents the crashes. However, a fixed time delay isn't satisfactory because: (1) I don't know if 5 seconds will always be enough. 1 seconds is too short. (2) The 5 second delay makes the program run too slowly (i.e. hours, perhaps days. I gave up waiting) and usually isn't necessary. Therefore, I thought I'd replace the fixed time delay with a variable delay loop which exits when the save finishes. I could make a collection of the names of the workbooks I'm saving and remove names from that collection in a WorkbookAfterSave event handler. Thus, the natural place to handle theWorkbookAfterSave event would be the VB 6 DLL, where all this saving and reading is going on. Since the master workbook always stays open and always keeps its initial reference to the DLL, the DLL stays open too. Alternatively, I could add it to VBA in the master workbook. Since I couldn't figure out how to do either of these things (although perhaps I can now, with your suggestions), I tried handling the WorkbookAfterSave event in the VB 2008 exe that started everything, but couldn't get that working because I couldn't get VB to recognize the reference to AppEvents_WorkbookAfterSaveEventHandler and AppEvents_Event. (Yes, I know that that should be the easy part.) If I can catch the event anywhere, I can pass the information to the point at which it's needed, but the VB 6 DLL would be the best place to put it. Well, that's probably a longer answer than you expected. I hope I'm not wearing out my welcome. Anyway, I appreciate the info you've already provided. Thanks again. "Chip Pearson" <chip(a)cpearson.com> wrote in message news:1fs0v5pphlihsv920s1tm4p58b1tgrudc0(a)4ax.com... >I somewhat misread your post. My previous reply is of limited > relevance. Where are you putting the > > Private WithEvents ExcelApp As Application > > declaration and where are you initializing the ExcelApp variable? The > code I posted in my previous reply works as expected, so it might not > be completely useless. In that code, I use a separate class module to > handle the XLApp and its events, but this code could be placed > directly in ThisWorkbook if desired. > > Cordially, > Chip Pearson > Microsoft Most Valuable Professional, > Excel, 1998 - 2010 > Pearson Software Consulting, LLC > www.cpearson.com > > > > On Sun, 16 May 2010 15:00:33 -0400, "Steve Flaum" > <sflaum(a)flauminc.com> wrote: > >>I can handle the Excel WorkbookAfterSave event in VB 2010 as explained at >>http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.appevents_event.workbookaftersave(office.14).aspx. >>I >>can also handle built-in events of the Excel application in VBA as >>described >>at http://msdn.microsoft.com/en-us/library/aa140934(v=office.10).aspx. >>However, I cannot figure out how to handle the WorkbookAfterSave event in >>VBA. If I use a statement such as: >> >> Private WithEvents ExcelApp as Application >> >>a number of application events are available, but WorkbookAfterSave isn't >>one of them. >> >>I suppose I could create a VB 2010 component which catches and rethrows >>the >>event, but there must be a better way. Can anyone tell me what it is? >> >>Thanks.
From: Steve Flaum on 18 May 2010 14:14 Hi Chip, I tried this, but WorkbookAfterSave isn't listed in the dropdown list of events for XLApp. I see WorkbookBeforeSave, WorkbookAfterXmlImport, and a few dozen other events, but not WorkbookAfterSave. I'm using Excel 2007. Do I need Excel 2010? Thanks. Steve "Chip Pearson" <chip(a)cpearson.com> wrote in message news:r4s0v5hg6fa1qg3gsli6jmtfljd67dc67b(a)4ax.com... > Because application events are for all open workbooks, you need to use > the WorkbookAfterSave event, which is called when any workbook is > saved. The WB parameter references the workbook that was saved. > > E.g., in Class1, > > Public WithEvents XLApp As Excel.Application > > Private Sub Class_Initialize() > Set XLApp = Application > End Sub > > Private Sub Class_Terminate() > Set XLApp = Nothing > End Sub > > Private Sub XLApp_WorkbookAfterSave(ByVal Wb As Workbook, _ > ByVal Success As Boolean) > MsgBox "AfterSave: " & Wb.Name > End Sub > > Then, in ThisWorkbook: > > Private XLEvents As Class1 > > Private Sub Workbook_Open() > Set XLEvents = New Class1 > End Sub > > As long as the workbook containing this code remains open, you'll get > WorkbookAfterSave events when any workbook is saved. > > > Cordially, > Chip Pearson > Microsoft Most Valuable Professional, > Excel, 1998 - 2010 > Pearson Software Consulting, LLC > www.cpearson.com > > > > > > On Sun, 16 May 2010 15:00:33 -0400, "Steve Flaum" > <sflaum(a)flauminc.com> wrote: > >>I can handle the Excel WorkbookAfterSave event in VB 2010 as explained at >>http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.appevents_event.workbookaftersave(office.14).aspx. >>I >>can also handle built-in events of the Excel application in VBA as >>described >>at http://msdn.microsoft.com/en-us/library/aa140934(v=office.10).aspx. >>However, I cannot figure out how to handle the WorkbookAfterSave event in >>VBA. If I use a statement such as: >> >> Private WithEvents ExcelApp as Application >> >>a number of application events are available, but WorkbookAfterSave isn't >>one of them. >> >>I suppose I could create a VB 2010 component which catches and rethrows >>the >>event, but there must be a better way. Can anyone tell me what it is? >> >>Thanks.
|
Pages: 1 Prev: Comparing data between files Next: Arrow-keys don't work after deleting OLE-object |