views:

320

answers:

3

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.

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 numbers look correct action is purely an intuitive exercise, thus will not be coded in any way.

The simple solution was to prompt users prior to closing the specific workbook to verify that the data has been checked.

Using VSTO SE for Excel 2007, an Add-in was created which hooks into the WorkbookBeforeClose event which is initialised in the add-in ThisAddIn_Startup

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
        } 
    } 
}

I have found the following ThisApplication.WorkbookBeforeSave vs ThisWorkbook.Application.WorkbookBeforeSave which recommends that one should use the ThisApplication.WorkbookBeforeClose event which I think is what I am doing since will span all files opened.

The issue I have with the approach is that assuming that I have several files open, some of which are in my list, the event prevents Excel from closing all files sequentially. It now requires each file to be closed individually. Edit: this happens when Exit Excel is used from File menu.

Questions

  1. Am I using the WorkbookBeforeClose event correctly and is this effective & efficient use of the event?
  2. Should I use the Application level event or document level event?
  3. Is the behaviour described above normal?
  4. Any other suggestions are welcomed when using workbook events in an add-in

Update [30-Mar-2010]:

Tinkering around, I also tried the following which attempted to bind the BeforeClose event handler to every workbook that was opened as suggested from the link above.

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
}

The problem that I found with this approach is that is I try to close to all Excel Files (using the Exit Excel option) the event handler does not execute. From my observation, this happens when the document to be checked is not the active document.

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.

Update [07-Apr-2010]:

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.

I have also found this blog How to Get an Excel VSTO Workbook Closed Event 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 OnWorkbookClosed event).

Update [08-Apr-2010]:

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 WorkbookBeforeClose 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.

  1. Is the behaviour described above normal? Yes, but why? 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 ref bool cancel argument (cancel=false allows normal closing of the workbook(default), cancel=true prevents the workbook from closing)

VS 2005 with VSTO SE

+3  A: 

Since you are running into problems with this approach, and Excel may not let you do what you need, maybe you could consider another approach to solve the overall problem.

Could you build something into to the workbook that flags it as "Verified/Unverified", based on whether someone has verified the numbers? Depending on how the workbook is actually used (not clear from the question), the way to show it as unverified could vary... (visual) set the background color to a light shade of red, or (programmatic) change the used named range to point to a blank area so that other addins/macros won't find the data.

A user who is to verify the workbook would need to click a button (in the workbook or in a toolbar) to indicate that they have verified it. Your code would then undo the above changes and set a flag indicating that the workbook has been verified. This could be in a hidden cell somewhere in the book, or in the properties of the VBA module.

To recap... on open, check the flag. If not set, change the workbook to look unverified. If it is set, then no changes needed. When the user verifies the workbook, set the flag and save it permanently.

Hope this helps!

Glen Little
Please let me know where I have indicated where I am running into problems - i can clarify the question a bit more..
Ahmad
Hmm.. I like the idea of a Verified/Unverified status (this may be something I could in the future). I was considering a dashboard style approach(a temporary workbook) which is broken down per workbook/per worksheet for a complete status overview as opposed to a workbook flag. I cant however add/modify anything in the workbook as these are prescribed formats, i could remove or return to the orig state prior to saving, but i prefer not to.
Ahmad
For the "status", you could save the value into the VBA properties of the file. Or add a hidden worksheet. Or add a comment to a specific cell...
Glen Little
@glen, the formats are prescribed since the files are being used in another system. I have no supporting docs etc that tell me what impact updating doc properties or inserting hidden cells will do ( i suspect not much) and due to 3rd party involvement its not something I want to tackle right now.
Ahmad
@Ahmad: Glen is on the right track here. You're going to need to set up or look for a flag or pattern to determine if the workbook is indeed the one you want. For example, does it's file name have a pattern? Does it always have data in a certain location on a certain sheet? Something to tell you that this particular workbook should be checked for what you're looking to validate. Doc properties and hidden cells are good, but because you don't own these workbooks, find a different pattern as the flag.
Otaku
@otaku - i am using a `StringCollection` configuration settings that contains the names of all files that need checking, so no need for any pattern. If you look at the question `list.Contains(wbName)` is my check for the required workbook. I also mentioned the validation checks is NOT a programmatic exercise(2nd paragraph).
Ahmad
@Ahmad, do you have any say over the file names? If so, the user could be instructed to "Save As..." after verifying the numbers, and specify a name that indicates that the numbers have been verified.
Glen Little
@glen - no but I know upfront what they are, thus the StringCollection config settings. In addition, by using config settings I can always add/remove files as and when required. I have already implemented a selective `FileSave` function that does the saving for the user once files have been checked.
Ahmad
+1  A: 
  1. Am I using the WorkbookBeforeClose event correctly and is this effective & efficient use of the event?

    I cant find any better event to use. However, I think a another approach would be to have Save&Close function that uses the WorkbookBeforeSave event

  2. Should I use the Application level event or document level event?

    At the time of writing this, I would say yes. Since I am not able to created a document-level add-in with the tools I have, this is the best solution available. Had the tools been available, I would have modified the _AssemblyName and _AssemblyLocation document properties of the relevant files. However, I would have changed the solution strategy by using using templates and then saving the files as required. In addition prior to saving, having not explored this fully, to add/remove the correct document properties. The document-level event would have well suited using the approach described above.

  3. Is the behaviour described above normal?

    Yes, but why? 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 ref bool cancel argument (cancel=false allows normal closing of the workbook(default), cancel=true prevents the workbook from closing). If I am wrong here, please let me know.

  4. Any other suggestions are welcomed when using workbook events in an add-in

    See above answers for alternative approaches and use of different events.

Ahmad
+2  A: 

The application-level WorkbookBeforeClose is the one to use. The issue is that erratic behaviour is resulting from files not being saved when WorkbookBeforeClose is called. If they are not, the Save event will kick and you've effectively now lost the WorkbookBeforeClose event because it has already occured. Here's some VBA code that helps handle this.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Not Me.Saved Then
        NotSavedPrompt = Me.Name & " has not been saved. Would you like to save now?"
        SaveYesNo = MsgBox(NotSavedPrompt, vbQuestion + vbYesNoCancel)
        Select Case SaveYesNo
            Case vbYes
                Me.Save
            Case vbNo
                Me.Saved = True
            Case vbCancel
                Cancel = True
                Exit Sub
          End Select
    End If
    Call MyRoutine() //'this should be your sub that does what you want
End Sub
Otaku
imo the best answer so far that covers most of my questions..
Ahmad
@Ahmad: Thanks, let me know if you have any further questions or concerns.
Otaku
I just realised, that my C# method signature looks different to yours `Application_WorkbookBeforeClose(Xl.Workbook wb, ref bool cancel)` - Are you sure this is the application level event method in your code?
Ahmad
Yeah, mine is just the VBA way of writing it (which doesn't really have the same VSTO concept of `ThisAddin`, unless it's a global macro). Yours is correct for C#/VSTO.
Otaku
nice solution with Me.Saved :)
Anonymous Type