Databases
StackOverflow2013
Postsdbo
POHow to effectively use WorkbookBeforeClose event correctly?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to effectively use WorkbookBeforeClose event correctly?
    primarykey
    data
    text
    <p>On a daily basis, a person needs to check that specific workbooks have been correctly updated with Bloomberg and Reuters market data; i.e. all data has pulled through and that the 'numbers look correct'. In the past, people were not checking the 'numbers' which led to inaccurate uploads to other systems.</p> <p>The idea is that 'something' needs to be developed to prevent the use from closing/saving the workbook unless he/she has checked that the updates are correct/accurate. The <code>numbers look correct</code> action is purely an intuitive exercise, thus will not be coded in any way. </p> <p>The simple solution was to prompt users prior to closing the specific workbook to verify that the data has been checked.</p> <p>Using VSTO SE for Excel 2007, an Add-in was created which hooks into the <code>WorkbookBeforeClose</code> event which is initialised in the add-in <code>ThisAddIn_Startup</code></p> <pre><code>private void wb_BeforeClose(Xl.Workbook wb, ref bool cancel) { //.... snip ... if (list.Contains(wb.Name)) { DailogResult result = MessageBox.Show("some message", "sometitle", MessageBoxButtons.YesNo); if (result != DialogResult.Yes) { cancel = true; // i think this prevents the whole application from closing } } } </code></pre> <p>I have found the following <a href="http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/4d74aeb0-40bd-4df4-b03f-e387bd9551f6/" rel="nofollow noreferrer"><code>ThisApplication.WorkbookBeforeSave</code> vs <code>ThisWorkbook.Application.WorkbookBeforeSave</code></a> which recommends that one should use the <code>ThisApplication.WorkbookBeforeClose</code> event which I think is what I am doing since will span all files opened.</p> <p>The issue I have with the approach is that assuming that I have several files open, some of which are in my <code>list</code>, the event prevents Excel from closing all files sequentially. It now requires each file to be closed individually. <strong><em>Edit</em></strong>: this happens when <em>Exit Excel</em> is used from File menu.</p> <p><strong>Questions</strong></p> <ol> <li>Am I using the <code>WorkbookBeforeClose</code> event correctly and is this effective &amp; efficient use of the event?</li> <li>Should I use the Application level event <strike>or document level event</strike>?</li> <li><em>Is the behaviour described above normal</em>? </li> <li>Any other suggestions are welcomed when using workbook events in an add-in</li> </ol> <p><strong>Update [30-Mar-2010]:</strong></p> <p>Tinkering around, I also tried the following which attempted to bind the <code>BeforeClose</code> event handler to every workbook that was opened as suggested from the link above.</p> <pre><code>private void ThisAddIn_Startup(...) { // snip Globals.ThisAddin.Application.WorkbookOpen += Application_Open; } private void Application_Open(XL.Workbook wb) { wb.BeforeClose += Document_WorkbookBeforeClose; // method does the same as above } </code></pre> <p>The problem that I found with this approach is that is I try to close to all Excel Files (using the <em>Exit Excel</em> option) the event handler does not execute. From my observation, this happens when the document to be checked is not the active document.</p> <p>This method seems erratic when compared to my initial approach. The one thing I am not certain about or feel comfortable with is binding the event every time a document is opened.</p> <p><strong>Update [07-Apr-2010]:</strong></p> <p>Glen's suggested answer is useful but does not tackle the immediate questions at hand, I have thus clarified the last question a bit further.</p> <p>I have also found this blog <a href="http://exceptionalcode.wordpress.com/2010/02/11/how-to-get-an-excel-vsto-workbook-closed-event/" rel="nofollow noreferrer">How to Get an Excel VSTO Workbook Closed Event</a> which is somewhat relevant to my issue as it could be used within an alternative approach to my solution using a monitor-type approach to handling the workbooks (and possibly also use the newly introduced <code>OnWorkbookClosed</code> event). </p> <p><strong>Update [08-Apr-2010]:</strong></p> <p>There seems to be some confusion, I am not concerned about any validation on the workbooks themselves but rather whether the method I am using (i.e. using the Application-level <code>WorkbookBeforeClose</code> event) is correct. @Mathias' comment below shows the correct understanding of part of the problem in relation to question 3, I think that this is default excel behaviour though. The solution to overcome this was to create a close function that closes only my specific files.</p> <blockquote> <ol start="3"> <li><em>Is the behaviour described above normal</em>? <strong>Yes, but why?</strong> Because the add-in hooks into the application-level event, the checks and cancellation of the event blocks the application from closing any further workbooks. The key here is the <code>ref bool cancel</code> argument (<code>cancel=false</code> allows normal closing of the workbook(default), <code>cancel=true</code> prevents the workbook from closing)</li> </ol> </blockquote> <hr> <p>VS 2005 with VSTO SE</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USTodd Main
    1. USAhmad
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to effectively use WorkbookBeforeClose event correctly?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow to effectively use WorkbookBeforeClose event correctly?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POHow to effectively use WorkbookBeforeClose event correctly?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COJust to make sure I understand your problem correctly, is this the scenario you have in mind: the user has files A, B, C open > closes Excel (Exit Excel) > says yes to close A, no to file B. What you have currently: Excel cancels closing A and B and C. What you would like: Excel closes A, keeps B open, and continues asking if C should be closed. Do I understand your problem correctly? i.e. your problem is that when a user exits Excel, you would like to be able to close files that can be closed, and only keep open the ones that need validation.
      singulars
      1. POHow to effectively use WorkbookBeforeClose event correctly?
      1. USMathias
    2. COAlso I started playing with this, and the 2 approaches (workbook closed catched at the app level or the workbook level) behave exactly the same way on my machine.
      singulars
      1. POHow to effectively use WorkbookBeforeClose event correctly?
      1. USMathias
    3. CO@mathias - exactly, I need to update the question further because I have managed to achieve what you are describing
      singulars
      1. POHow to effectively use WorkbookBeforeClose event correctly?
      1. USAhmad
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload