OfficeJS.dialogs Updated (v1.0.5)

I have been working hard on my OfficeJS.Dialogs library and just published version 1.0.5. You can get it from NPM and GitHub. See my previous post for more information on how to do this.

I have added a few new features:

  • A simple Alert.Show() method that displays a simple OK box. For those times you want to just simply pop up a quick notification to the user.
  • A Progress.Show() that displays a progress bar. This allows for you to show the progress bar and then issue Progress.Update() to move the progress bar along. When you are done you call Progress.Complete().
  • A Wait.Show() dialog that will show an indeterminate spinner. This form will remain up until you issue a Wait.CloseDialog().
  • New UpdateMessage() and Update() methods were added to the MessageBox. This was done to allow you to quickly ask a lot of questions of the user in one instance of the dialog, without giving the user back to the application for a second while the new dialog is rendered. UpdateMessage() will just update the message but keep all the buttons the same, but you will specify a new callback. Update() will allow you to fundamentally change all the settings the MessageBox (buttons, icon, caption, text and all), plus a new callback function.
  • Behind the scenes I made some improvements/bug fixes:
    • If you try to show two dialogs too quickly, nothing will happen. So I added a half-second delay between dialog displays to make sure you never get an overlap.
    • You will get an error message in your callback if more than one dialog is attempted to be opened at once.
    • “Window Messaging” has been setup with Progress and MessageBox to allow the parent and the dialog to pass messages back and forth. It involves using setTimeout().

For those interested in the last item, here is what that look like:

        /**
        * Handles messages coming from the parent
        */
        function startMessageHandler() {
            setTimeout(function() {
                var message = localStorage.getItem("dialogMessage");
                localStorage.setItem("dialogMessage", ""); // clear the message
                if(message !== undefined && message !== null && message != "")
                {
                    var msg = JSON.parse(message);
                    if(msg.message == "update") {
                        // update the form
                        updateForm(msg.settings);
                    } else if(msg.message == "close") {
                        // do nothing special here
                        return; // stops the message pump
                    } else if(msg.message == "progress") {
                        if(msg.settings.Number > 100) return;
                        $("#bar").prop("value",msg.settings.Number);
                    }
                }
                startMessageHandler(); // call again
            }, 0);
        }

Here is an example of how to use the Progress dialog. The method signature is like this:

Progress.Show( [message], [start], [max], [completeCallback], [cancelCallback] )

  • The message is the message the user see’s when the dialog is opened.
  • The start is the number you want the progress bar to start at. Usually this should just be zero (0).
  • The max is the number you want the Progress bar to end at. Usually this should be 100. But it can be any number you want. If you have 5 steps to perform in the background while this dialog is up, you can set this to 5.
  • The completeCallback is your callback function to be called when your code calls the Progress.Compelte().
  • The cancelCallback is what gets called when the user presses the Cancel button on the form.

By itself, this will do nothing. You will have to call the Progress.Update() command in order to move the progress bar, or update the message to the user. Here is the method signature for the Update method:

Progress.Update( [amount], [message] )

  • The amount is how much you want the progress bar to move. If you do not specify an amount, an amount of 1 is assumed.
  • The message is a new message to provide the progress bar. If you want to update the message and do not want to increment the progress bar, specify an amount of zero (0).

Once you are all done with the Progress dialog, you issue a Progress.Complete() call. There are no parameters to it. Once called, your completeCallback in the Progress.Show() call will then be executed.

Here is an example:

// reset first to make sure we get a fresh object
Progress.Reset();
// display a progress bar form and set it from 0 to 100
Progress.Show("Please wait while this happens...", 0, 100, function() {
    // once we are done - when your code
    // calls Progress.Complete()
    Alert.Show("All done folks!");
  }, function() {
    // this is only going to be called if the user cancels
    Alert.Show("The user cancelled");
});
doProgress();

function doProgress() {
  // increment by one, the result that comes back is
  // two pieces of information: Cancelled and Value
  var result = Progress.Update(1);
  // if we are not cancelled and the value is not 100%
  // we will keep going, but in your code you will
  // likely just be incrementing and making sure
  // at each stage that the user has not cancelled
  if(!result.Cancelled && result.Value <= 100) {     setTimeout(function() {       // this is only for our example to       // cause the progress bar to move       doProgress();     },100);   } else if(result.Value >= 100) {
    Progress.Compelte(); // done
  }
};

That example also uses the new Alert dialog. This one is very simple:

Alert.Show ( [message] )

This next example uses the new Wait dialog, which is much simpler to implement. Here is the method signature:

Wait.Show( [message], [showCancel], [cancelCallback] )

  • The message is the message you want to show the user. If you specify null, it will appear as simply “Please wait…”
  • The showCancel flag if set will allow the user to see a Cancel button.
  • The cancelCallback function is only valid if the showCancel option is true. When the user presses cancel, this function gets called.

When you are ready to close the Wait dialog, you issues a Wait.CloseDialog(). Here is an example:

  Wait.Show(null, true, function() {
    Alert.Show("The user cancelled.");
  });
  setTimeout(function(){
    Wait.CloseDialog();
    Alert.Show("Done!");
  }, 15000);

If you have some suggestions for some things you would like to see added to this library, please add a comment below or reach out to me on LinkedIn or Twitter. Some ideas I will be working on:

  • Allow you to call another dialog type without having the close the dialog.
  • A selection dialog, where you have a dropdown list of a listbox where you wan select (or multi-select) items.
  • An option to resize forms.
  • An option to use the message handler in your own custom form – minimal code

Office JavaScript API Code Explorers

Recently while preparing an internal Chalk Talk on Office Web Add-in Development, a co-worker presented me with two links I had not seen before and I wanted to share them with everyone:

These Code Explorers are pretty cool in that they contain some common use code patterns that you might find useful in your projects.

excelCodeExplorerCapture

Unfortunately, there does not appear to be one for PowerPoint and or Outlook yet. But the fact they are there for Excel and Word is pretty cool.

[UPDATED] Word After Save Event

When I wrote my first Word AfterSave Event entry, it was designed for Word 2007, and was – as it turns out – not a catch all. So I have updated it here (thanks for the catch go to Pat Lemm).

When the document was closed, you never got access to the Saved filename. So, I have updated the code here and it now works in all conditions and has been tested in Word 2013.

Here is how it works:

  1. Upon initialization you pass it your Word object.
  2. It attaches to the Before Save Event.
  3. When any save event occurs, it kicks off a thread that loops until the Background Save is complete.
  4. Once the background save is done, it checks to see if the document Saved == true:
  • If Saved == true: then a regular save did occur.
  • If Saved == false: then it had to be an AutoSave

In each case it will fire a unique event:

  • AfterSaveUiEvent
  • AfterSaveEvent
  • AfterAutoSaveEvent

Additionally, if the document being saved is also being closed, we catch the filename on the WindowDeactivate event on the way out. This can now be accessed by the caller (as you can see in the example below), to get the full filename of the closed document.

Here is the code to the class:

public class WordSaveHandler
{
    public delegate void AfterSaveDelegate(Word.Document doc, bool isClosed);
    // public events
    public event AfterSaveDelegate AfterUiSaveEvent;
    public event AfterSaveDelegate AfterAutoSaveEvent;
    public event AfterSaveDelegate AfterSaveEvent;

    // module level
    private bool preserveBackgroundSave;
    private Word.Application oWord;
    string closedFilename = string.Empty;

    /// <summary>
    /// CONSTRUCTOR  takes the Word application object to link to.
    /// </summary>
    /// <param name="oApp"></param>
    public WordSaveHandler(Word.Application oApp)
    {
        oWord = oApp;
        // hook to before save
        oWord.DocumentBeforeSave += oWord_DocumentBeforeSave;
        oWord.WindowDeactivate += oWord_WindowDeactivate;
    }
    
    /// <summary>
    /// Public property to get the name of the file
    /// that was closed and saved
    /// </summary>
    public string ClosedFilename
    {
        get
        {
            return closedFilename;
        }
    }

    /// <summary>
    /// WORD EVENT  fires before a save event.
    /// </summary>
    /// <param name="Doc"></param>
    /// <param name="SaveAsUI"></param>
    /// <param name="Cancel"></param>
    void oWord_DocumentBeforeSave(Word.Document Doc, ref bool SaveAsUI, ref bool Cancel)
    {
        // This could mean one of four things:
        // 1) we have the user clicking the save button
        // 2) Another add-in or process firing a resular Document.Save()
        // 3) A Save As from the user so the dialog came up
        // 4) Or an Auto-Save event
        // so, we will start off by first:
        // 1) Grabbing the current background save flag. We want to force
        //    the save into the background so that Word will behave
        //    asyncronously. Typically, this feature is on by default,
        //    but we do not want to make any assumptions or this code
        //    will fail.
        // 2) Next, we fire off a thread that will keep checking the
        //    BackgroundSaveStatus of Word. And when that flag is OFF
        //    no know we are AFTER the save event
        preserveBackgroundSave = oWord.Options.BackgroundSave;
        oWord.Options.BackgroundSave = true;
        // kick off a thread and pass in the document object
        bool UiSave = SaveAsUI; // have to do this because the bool from Word
        // is passed to us as ByRef
        new Thread(() =>
        {
            Handle_WaitForAfterSave(Doc, UiSave);
        }).Start();
    }

    /// <summary>
    /// This method is the thread call that waits for the same to compelte.
    /// The way we detect the After Save event is to essentially enter into
    /// a loop where we keep checking the background save status. If the
    /// status changes we know the save is compelte and we finish up by
    /// determineing which type of save it was:
    /// 1) UI
    /// 2) Regular
    /// 3) AutoSave
    /// </summary>
    /// <param name="Doc"></param>
    /// <param name="UiSave"></param>
    private void Handle_WaitForAfterSave(Word.Document Doc, bool UiSave)
    {
        try
        {
            // we have a UI save, so we need to get stuck
            // here until the user gets rid of the SaveAs dialog
            if (UiSave)
            {
                while (isBusy())
                    Thread.Sleep(1);
            }

            // check to see if still saving in the background
            // we will hang here until this changes.
            while (oWord.BackgroundSavingStatus > 0)
                Thread.Sleep(1);
        }
        catch (ThreadAbortException)
        {
            // we will get a thread abort exception when Word
            // is in the process of closing, so we will
            // check to see if we were in a UI situation
            // or not
            if (UiSave)
            {
                AfterUiSaveEvent(null, true);
            }
            else
            {
                AfterSaveEvent(null, true);
            }
        }
        catch
        {
            oWord.Options.BackgroundSave = preserveBackgroundSave;
            return; // swallow the exception
        }

        try
        {
            // if it is a UI save, the Save As dialog was shown
            // so we fire the after ui save event
            if (UiSave)
            {
                // we need to check to see if the document is
                // saved, because of the user clicked cancel
                // we do not want to fire this event
                try
                {
                    if (Doc.Saved == true)
                    {
                        AfterUiSaveEvent(Doc, false);
                    }
                }
                catch
                {
                    // DOC is null or invalid. This occurs because the doc
                    // was closed. So we return doc closed and null as the
                    // document
                    AfterUiSaveEvent(null, true);
                }
            }
            else
            {
                // if the document is still dirty
                // then we know an AutoSave happened
                try
                {
                    if (Doc.Saved == false)
                        AfterAutoSaveEvent(Doc, false); // fire autosave event
                    else
                        AfterSaveEvent(Doc, false); // fire regular save event
                }
                catch
                {
                    // DOC is closed
                    AfterSaveEvent(null, true);
                }
            }
        }
        catch { }
        finally
        {
            // reset and exit thread
            oWord.Options.BackgroundSave = preserveBackgroundSave;
        }
    }

    /// <summary>
    /// WORD EVENT – Window Deactivate
    /// Fires just before we close the document and it
    /// is the last moment to get the filename
    /// </summary>
    /// <param name="Doc"></param>
    /// <param name="Wn"></param>
    void oWord_WindowDeactivate(Word.Document Doc, Word.Window Wn)
    {
        closedFilename = Doc.FullName;
    }

    /// <summary>
    /// Determines if Word is busy  essentially that the File Save
    /// dialog is currently open
    /// </summary>
    /// <param name="oApp"></param>
    /// <returns></returns>
    private bool isBusy()
    {
        try
        {
            // if we try to access the application property while
            // Word has a dialog open, we will fail
            object o = oWord.ActiveDocument.Application;
            return false; // not busy
        }
        catch
        {
            // so, Word is busy and we return true
            return true;
        }
    }
}

 

And here is how you set it up and attach to it’s events:

public partial class ThisAddIn
{
    WordSaveHandler wsh = null;
    private void ThisAddIn_Startup(object sender,
                                    System.EventArgs e)
    {
        // attach the save handler
        wsh = new WordSaveHandler(Application);
        wsh.AfterAutoSaveEvent += new WordSaveHandler.AfterSaveDelegate(wsh_AfterAutoSaveEvent);
        wsh.AfterSaveEvent += new WordSaveHandler.AfterSaveDelegate(wsh_AfterSaveEvent);
        wsh.AfterUiSaveEvent += new WordSaveHandler.AfterSaveDelegate(wsh_AfterUiSaveEvent);
    }

    void wsh_AfterUiSaveEvent(Word.Document doc, bool isClosed)
    {
        if (!isClosed)
            MessageBox.Show("After SaveAs Event");
        else
            MessageBox.Show("After Close and SaveAs Event. The filname was: " + wsh.ClosedFilename);
    }

    void wsh_AfterSaveEvent(Word.Document doc, bool isClosed)
    {
        if (!isClosed)
            MessageBox.Show("After Save Event");
        else
            MessageBox.Show("After Close and Save Event. The filname was: " + wsh.ClosedFilename);
    }

    void wsh_AfterAutoSaveEvent(Word.Document doc, bool isClosed)
    {
        MessageBox.Show("After AutoSave Event");
    }

How to determine if an Excel Workbook is Embedded… and more…

When working with a Visual Studio Tools for Office (VSTO) project, you may want to have your add-in behave differently when the Active Workbook is inside an embedding. More specifically, the user has inserted an Excel sheet into a Word Document, for example, and has just double-clicked on it and you find that you need to detect this scenario. The trick is to know when you are in an embedded scenario and as it turns out, this is not easy to do. If you are an OLE guru, you might check the Container property on the Workbook Object:

However, this property always throws an Exception for Office Document types. It specifically calls out Internet Explorer, for a reason. This property does not work with OLE Embeddings in other Office documents. You can check the path length and as I have seen in many solutions hardcoding for “Workbook in…” but this fails for multi-language solutions.

What I found you have to do is access a series of OLE properties through C# to get the information you need. And as an added bonus, not only can you determine if Excel is embedded, but you can also get the Name of the class object it is embedded inside of and the Moniker / Filename of the file it is embedded in. I created the following Extension methods for Excel:

/// <summary>
/// EXTENSION METHOD CLASS FOR EXCEL
/// </summary>
public static class ExcelExtensionMethods
{
    [DllImport("ole32.dll")]
    static extern int CreateBindCtx(uint reserved, out IBindCtx ppbc);

    /// <summary>
    /// WORKBOOK EXTENSION METHOD
    /// Checks to see if the Workbook is embeeded inside of 
    /// another ActiveX Document type, sy=uch as Word or Excel.
    /// </summary>
    /// <param name="PobjWb"></param>
    /// <returns></returns>
    public static bool IsEmbedded(this Excel.Workbook PobjWb)
    {
        if (PobjWb.Path == null || PobjWb.Path.Length == 0)
        {
            try
            {
                // requires using Microsoft.VisualStudio.OLE.Interop;
                // and you have to manually add this to reference from here:
                // C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\Microsoft.VisualStudio.OLE.Interop.dll
                IOleObject LobjOleObject = ((object)PobjWb) as IOleObject;
                IOleClientSite LobjPpClientSite;
                // get the client site
                LobjOleObject.GetClientSite(out LobjPpClientSite);
                // if there is one - we are embedded
                if (LobjPpClientSite != null)
                {
                    return true;
                }
                else
                {
                    // not embedded
                    return false;
                }
            }
            catch (Exception ex)
            {
                // exception
                Debug.Print(ex.ToString());
                return false;
            }
            finally { }
        }
        else
        {
            // not embedded
            return false;
        }
    }

    /// <summary>
    /// WORKBOOK EXTENSION METHOD
    /// This method return the name of the class that we
    /// are embedded inside of.
    /// If we are not embedded it return null.
    /// If there is any exception it return null.
    /// If the container cannot be accessed it returns UNKNOWN.
    /// </summary>
    /// <param name="PobjWb"></param>
    /// <returns></returns>
    public static string EmbedClassName(this Excel.Workbook PobjWb)
    {
        try
        {
            IOleObject LobjOleObject = ((object)PobjWb) as IOleObject;
            IOleClientSite LobjPpClientSite;
            // get the client site
            LobjOleObject.GetClientSite(out LobjPpClientSite);
            if (LobjPpClientSite != null)
            {
                IOleContainer LobjPpContainer;
                LobjPpClientSite.GetContainer(out LobjPpContainer);
                if (LobjPpContainer != null)
                {
                    return LobjPpContainer.GetType().Name;
                }
                else
                {
                    // something wrong - container is not valid
                    return "UNKNOWN";
                }
            }
            else
            {
                // not embedded
                return null;
            }
        }
        catch (Exception ex)
        {
            Debug.Print(ex.ToString());
            return null; // failed
        }
    }

    /// <summary>
    /// WORKBOOK EXTENSION METHOD
    /// Get the full path to the file that the workbook is embedded 
    /// inside of. 
    /// If we are not embeeded then this will return null.
    /// If we are embedded but there are issues with the container
    /// or an exception occurs, it will return null.
    /// Otherwise we get the full path and filename.
    /// </summary>
    /// <param name="PobjWb"></param>
    /// <returns></returns>
    public static string EmbedMoniker(this Excel.Workbook PobjWb)
    {
        try
        {
            IOleObject LobjOleObject = ((object)PobjWb) as IOleObject;
            IOleClientSite LobjPpClientSite;
            // get the client site
            LobjOleObject.GetClientSite(out LobjPpClientSite);
            if (LobjPpClientSite != null)
            {
                IOleContainer LobjPpContainer;
                LobjPpClientSite.GetContainer(out LobjPpContainer);
                if (LobjPpContainer != null)
                {
                    // get the moniker
                    IMoniker LobjMoniker;
                    LobjPpClientSite.GetMoniker((uint)OLEGETMONIKER.OLEGETMONIKER_FORCEASSIGN,
                                                (uint)OLEWHICHMK.OLEWHICHMK_OBJFULL,
                                                out LobjMoniker);
                    if (LobjMoniker != null)
                    {
                        // now pull the moniker display name
                        // this will be in the form of PATH!Context
                        string LstrDisplayName;
                        IBindCtx LobjCtx = null;
                        CreateBindCtx(0, out LobjCtx); // required (imported function)
                        LobjMoniker.GetDisplayName(LobjCtx, null, out LstrDisplayName);
                        // remove context is exists
                        if (LstrDisplayName.Contains("!"))
                        {
                            string[] LobjMonikerArray = LstrDisplayName.Split('!');
                            // return the first part - which should be the path
                            return LobjMonikerArray[0];
                        }
                        else
                        {
                            // return full display name
                            return LstrDisplayName;
                        }
                    }
                    else
                    {
                        // no moniker value
                        return null;
                    }
                }
                else
                {
                    // something wrong - container is not valid
                    return null;
                }
            }
            else
            {
                // not embedded
                return null;
            }
        }
        catch (Exception ex)
        {
            Debug.Print(ex.ToString());
            return null; // failed
        }
    }
}

Pay close attention to the comments, because you will not find the OLE reference you need in the References window. You will have to browse to the path given and manually select it.

Now, to use it, I hook to the WorkbookActivate event, like this:

/// <summary>
/// STARTUP
/// </summary>
/// <param name="PobjSender"></param>
/// <param name="pObjEventArgs"></param>
private void ThisAddIn_Startup(object PobjSender, System.EventArgs pObjEventArgs)
{
    Application.WorkbookActivate += new Excel.AppEvents_WorkbookActivateEventHandler(Application_WorkbookActivate);
}

/// <summary>
/// Workbook Activate event fires when any workbook is activated.
/// </summary>
/// <param name="PobjWb"></param>
void Application_WorkbookActivate(Excel.Workbook PobjWb)
{
    // call the extension method created below
    if (PobjWb.IsEmbedded())
    {
        string LstrClass = PobjWb.EmbedClassName();
        string LstrPath = PobjWb.EmbedMoniker();
        MessageBox.Show("This workbook is embedded in a [" + LstrClass + "] " +
                        "with a path of: \n\n\t" + LstrPath);
    }
}

What you can see from the code above is that on WorkbookActivate, I check to see if the workbook is embedded and if it is, then I get the Class Name by calling my other Extension Method – EmbedClassName() – and then I get the path to the file it is embedded inside of (for example, the Word document path that contains the embedded Excel sheet) by calling yet another Extension Method – EmbedMoniker().

Creating a Loader Add-in (Master Add-in)

I have now helped about half a dozen customers over the last 3 years to perform this very same task. In each case the scenario is the same:

  • They have documents that are very specific to their system
  • They want to create an add-in to assist with the management of these documents.
  • They only want the add-in to load when one of their document is opened.
  • BONUS: They also want to remove other add-ins
  • BONUS: They would like a pristine instance of Word or Excel.

There are several methods to accomplish each of these tasks. But the one I find is easiest to maintain is what I call the “Master Add-in” approach.

In the “Master Add-in” you essentially attach one event: DocumentOpen or WorkbookOpen. You place code in the event to detect whether it is a document you care about. This can be done in several ways:

  • You can look at the path where the file came from.
  • You can look at the name of the file, if there is a specific naming convention you follow.
  • Or, you can tag he document with Document Properties or Custom Xml Parts.

Once you have identified that it is a document you care about you follow this basic process:

  • Get the path to the file being open and store it.
  • Close the workbook or document being passed into the Open event.
  • Create a new Instance of Word or Excel.
  • Iterate through the COM Add-ins collection and the Add-ins collection and disable everything you do not want running. This includes disabling the Master Add-in as you do not want it running for the next parts.
  • Locate your COM Add-in in the collection and set Connect = true.
  • Open the document or workbook from the application object.

You will now have two instances of Excel or Word open. One that the user was originally working with which has the Master Add-in loaded and then the new one that is customized with your specific add-in and only your add-in.

This is useful for when you have multiple versions of your system and you can update the Master to recognize which version to launch.

Getting this to work just right is sometimes a challenge and you have to be careful not to disable all the add-ins for all instances. I have a few customer examples that I have built and will work on cleaning them up. I will combine the best parts and will write a new entry in the future which will walk you through creating one.