Handling the WorkbookAfterSave Event in VBA

[Steve Flaum]

I can handle the Excel WorkbookAfterSave event in VB 2010 as explained at
http://msdn.microsoft.com/en-us/lib...vents_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.

 

[Chip Pearson]

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

 

[Chip Pearson]

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

 

[Steve Flaum]

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.

 

[Steve Flaum]

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