Getting Started with OfficeJS

If you are just getting started with OfficeJS, then this post is for you. I will assume you have some understanding of what Office Web Add-ins are. However, there is one very basic point I will demonstrate in this tutorial:

  • An Office Web Add-In is simply a web site and an XML “description” file. I detailed this in this blog post. And we are going to build on that.

So, you are brand new to OfficeJS, and want to get started. The first thing you need to do is get your development environment up and running. There is a script for that:

Microsoft Office Development Environment Script

NOTE: For more information on this script, please read my blog post.

Next, you will need to pull down the Web Add-in Side Loader tool. Keep this ZIP on your desktop. This will be used later in the steps below to make it really easy for you to install the Add-in to Excel. However, if you prefer to do the steps manually, see the link below. For more information on this tool, please see my blog post and/or the GitHub repository.

Now that you have the development environment setup, lets build your very first, super basic Excel add-in:

  • Create a folder on your desktop called “MyFirstAddIn”
  • Open Notepad and copy/paste the code from HERE, then click File, Save, name it “manifest.xml”, change the type to “All Files *.*”, browse to folder on your desktop, and click Save.

NOTE: In this step you created the manifest file. This file will be used to “install” the add-in into Excel. Essentially, it will tell Excel where the web page is for the task pane.

  • Close Notepad, then open it again. Copy paste this code HERE, then click File, Save, name it “home.html”, change the type to “All Files *.*”, browse to the folder on your desktop, and click Save.

NOTE: In this step you created the primary file for website. This one file contains both the HTML and JavaScript needed to make the taskpane load in Excel.

  • Now you need to side load the manifest and you do that by first opening the ZIP file on your desktop: Set-WebAddin (v1.0.0.0).zip and extracting the Set-WebAddin.exe to the “MyFirstAddIn” folder.
  • Press Window+R to open the run dialog, and type CMD and press enter.

NOTE: This will open the Windows Command Prompt which you will use to sideload the add-in and run it.

NOTE: The word “sideload” is a fancy way to say install it for only your instance of Excel. It is also known as Developer Sideloading.

  • Change the directory to the folder on your desktop with this command:
cd "%userprofile%\desktop\MyFirstAddIn"
  • Next, type this command:
Set-WebAddin -test -manifestPath "%userprofile%\desktop\myfirstaddin\manifest.xml"

NOTE: In this step you have sideloaded the add-in to Office. This essentially tells Office you have an added an Excel add-in via the information in the manifest file which, when loaded tells Excel everything it needs to know about it, including where to find it. If you prefer to do the sideloading steps manually, you can follow them here.

  • Now you are almost ready. Type this last command (that is a dot/period at the end, very important):
http-server .

NOTE: The above steps start a local the http-server on your computer so that it will serve the webpage in the browser from the folder (.) you are current inside. You can now test this by clicking this link.

  • You are now ready to go. Simply open Excel and a new Blank Workbook. Then on the Insert tab, click the down arrow next to My add-ins and click BasicAddin. It should look like this:
The BasicAddIn in the Developer Add-ins menu
  • Your add-in Task Pane should load.
  • Click in cell A1, type “Hello World!”, and then in your Task Pane, click the “Click here” button and you should see “Hello World!” in the bottom of the page.
  • Once you are done, you can press CTRL+C in the command prompt window to shutdown the server and then to uninstall the add-in you can use this command:
Set-WebAddin -cleanup -manifestPath "%userprofile%\desktop\myfirstaddin\manifest.xml"

And that is it. There are three things to note here:

  • You created your first web add-in.
  • You did not use VSCode or any Integrated Development Environment. You did this only with Notepad.
  • But as an added bonus – you have also installed VSCode and the tools you need to start doing more complex things with Office Web Add-ins.

JavaScript, HTML, Web, CSS, front-end, back-end, web server, ports, local hosts… some, if not all of these are new terms if you are just getting into this new world of the Web and Web add-ins. But it does not have to be that complicated. As I referred to earlier, the following posts should be your next two stops on your journey:

Getting started as an OfficeJS Developer

Start Developing in OfficeJS Today with ScriptLab

If you have any questions, please reach out to me.

An Asynchronous MessageBox

Recently, I had a project I was working on where we needed to be able to notify the user of a possible problem during a long running process. The results would not be ruined, or wrong, just likely incomplete. In this particular case we were collecting several pieces of information and sometimes one of those pieces were not available (say, for example the server was down, the file was missing, or it took too long). We wanted to let the user know the process had encountered an issue, but not stop it. Yet we wanted to also give them the option to retry it. So we presented an Abort, Retry, Cancel dialog like this one:

messagebox

The problem is that with a traditional MessageBox the entire process would be frozen. If this happens immediately after the user heads off to lunch, when they get back there would be no report, no data and most of the process would not have been run yet, for example.

What I needed was for the process to continue, but to allow the user to be able to take action before the process completed on anything that was found. For example, they see this error above and think: “oh crap, I forgot to copy over the report file.” So they put the correct file in place and hit “Retry.”

Therefore, I created the AsyncMessageBox class:


using System;
using System.Runtime.InteropServices;
using System.Threading;
using System.Windows.Forms;
namespace NonModalMessageBox
{
/// <summary>
/// Provides an asyncronous MessageBox. You can use this static class to
/// present a message to the usser while your code can continue to run.
/// You can attach to the AsynMessagebox.MessageboxClosed event to get the
/// result from the dialog once the user does close it.
/// </summary>
public static class AsyncMessageBox
{
private static readonly IntPtr HWND_TOPMOST = new IntPtr(1);
private static readonly uint SWP_NOMOVE = 0x0002;
private static readonly uint SWP_NOSIZE = 0x0001;
private static readonly uint SWP_SHOWWINDOW = 0x0040;
[DllImport("user32")]
private static extern IntPtr FindWindow(string PstrClassName, string PstrCaption);
[DllImport("user32")]
private static extern void SetWindowPos(IntPtr PintWnd, IntPtr PintWndInsertAfter, int PintX, int PintY, int PintCx, int PintCy, uint uFlags);
public delegate void MessageBoxClosedHandler(object PobjSender, MessageBoxClosedEventArgs PobjEventArgs);
public static event MessageBoxClosedHandler MessageBoxClosed;
private static bool MbolAlreadyShowing = false;
/// <summary>
/// Shows an asyncronous dialog
/// Fires the MessageBoxClosed event when it is closed.
/// This is a static messagebox, so only one can be displayed at a time
/// Once called, the event handler is detached
/// </summary>
/// <param name="PstrText">The message in the message box</param>
/// <param name="PstrCaption">The cpation on the message box</param>
/// <param name="PobjButtons">The buttons for the message box</param>
/// <param name="PobjIcon">The icon for the messafe box</param>
/// <param name="PobjDefault">The default button selected in the messagebox</param>
/// <returns></returns>
public static bool Show(string PstrText, string PstrCaption = "", MessageBoxButtons PobjButtons = MessageBoxButtons.OK, MessageBoxIcon PobjIcon = MessageBoxIcon.None, MessageBoxDefaultButton PobjDefault = MessageBoxDefaultButton.Button1)
{
try
{
if (MbolAlreadyShowing) return false; // failed – already displayed
DialogResult LobjResult = DialogResult.None;
// start a thread to show the dialog
new Thread(() => {
MbolAlreadyShowing = true;
LobjResult = MessageBox.Show(PstrText, PstrCaption, PobjButtons, PobjIcon, PobjDefault);
MbolAlreadyShowing = false;
}).Start();
// start a separate thread to wait for the result from above
new Thread(() => {
// now make it topmost
IntPtr LintHwnd = FindWindow("#32770", PstrCaption);
SetWindowPos(LintHwnd, HWND_TOPMOST, 0, 0, 0, 0, SWP_NOSIZE | SWP_NOMOVE | SWP_SHOWWINDOW);
// Stay here until we get a result
while (LobjResult == DialogResult.None)
{
Thread.Sleep(10);
Application.DoEvents();
}
// create a hidden form so we can invoke back to the UI thread
// otherwise anyone attached to the event handler will get an
// exception if they try anything with the UI
using (Form LobjForm = new Form { ShowInTaskbar = false, Opacity = 0 })
{
LobjForm.Show(); // show hidden form – ui thread
// fire the event
LobjForm.Invoke(new Action(() => {
MessageBoxClosed?.Invoke(new object(), new MessageBoxClosedEventArgs(LobjResult));
MessageBoxClosed = null; // important to release this
}));
LobjForm.Close(); // close hidden form
}
}).Start();
return true; // created
}
catch
{
return false; // failed
}
}
}
/// <summary>
/// Event arguments for an AsyncMessageBox
/// </summary>
public class MessageBoxClosedEventArgs
{
public DialogResult Result { get; private set; }
public MessageBoxClosedEventArgs(DialogResult PobjResult)
{
Result = PobjResult;
}
}
}

Under the covers this uses a traditional MessageBox, but on background thread. You need to attach to the MessageBoxClosed event before you make the AsyncMessageBox.Show() call if you want to get the result. Here is how you use this method:


AsyncMessageBox.MessageBoxClosed += (o, e) =>
{
// handle the user response here
if(e.Result == DialogResult.Retry)
{
// retry code here
}
};
AsyncMessageBox.Show("There was an issue accessing 'quaterly report.xlsx'.",
"Quarterly Report Generator",
MessageBoxButtons.AbortRetryIgnore,
MessageBoxIcon.Exclamation);

Anyway, thought this might be useful, so I have shared it. wlEmoticon-hotsmile.png

Office Scripts: The future is here

Microsoft has been working hard to make Office available on every platform. One of the biggest changes has been the ability to run Office the a browser or Office online. However, one drawback has been that automation in Office online has been limited to Web Add-ins which are aimed solidly at professional developers. However, Visual Basic for Applications (VBA) macros are only supported in Windows and Mac clients. They are not supported on the web platform, the iOS or Android platforms. And, well, they still are not.  wlEmoticon-disappointedsmile.png  However, something new has arrived which will allow you to create “macros” anywhere, on any platform (eventually).

Microsoft recently announced Office Scripts. It is an end-user approachable, web and collaborative supported scripting language. It is still in preview and only supported in Excel online, for now.

First, to access this you must have your administrator enable it in the Office 365 Portal:

turn_on_scripts

For citizen programmers this line from the above page says it all:

Scripts allow you to record and replay your Excel actions on different workbooks and worksheets. If you find yourself doing the same things over and over again, an Office Script can help you by reducing your whole workflow to a single button press.

So, I enabled it in my test tenant and created the quintessential “Hello Wold” Action Recording. When your administrator enables this on your tenant, you will get this new Automation tab:

AutomationTab

From there, I clicked on Record Actions, typed “Hello World!” in cell A1, and then stopped the macro. It then asked me to name it and give a description (optional), and then Save. From there it opened the Code Editor and here is what I see:


async function main(context: Excel.RequestContext) {
// Set range A1 on selectedSheet
let workbook = context.workbook;
let worksheets = workbook.worksheets;
let selectedSheet = worksheets.getActiveWorksheet();
selectedSheet.getRange("A1").values = [
["Hello World"]
];
}

view raw

HelloWorld.js

hosted with ❤ by GitHub

Once you open a new workbook, you can then click on the Code Editor button and you will see all your recorded scripts:

recordings

The future is here. Check it out now. You can learn more about Office Scripts from here: https://docs.microsoft.com/en-us/office/dev/scripts/tutorials/excel-tutorial.

 

What is Office 365, really?

In my day-to-day dealings with people and organizations, there is a common confusion on the term “Office 365.” And that confusion is fed by a few common myths that I have heard repeated by some very smart people in those organizations. So, first, let me dismiss the myths…

  • The first one is that Office 365 is online, in the browser applications only… NO! Office 365 is NOT just Excel Online, Word Online, PowerPoint Online, Outlook Online, and OneNote Online.
  • And the second myth is that the desktop applications are getting deprecated… NO! Microsoft is NOT doing away with the installed desktop applications on Windows and Mac in favor of web based versions.

So let’s get to the question:

If Microsoft is not replacing Office with web versions, then what the heck is all this Office 365 and cloud stuff then?

Primarily, Office 365 is the name of the subscription. You no longer buy the Office applications on disks and install them, then buy the next version, etc. With the subscription you always get the newest version (and much more). As you will read from the link above, there are lots of FAQ’s and other information that explain how this works.

So Excel Online, ET AL. is PART of the Office 365 subscription based platform. Office 365 still includes the traditional Microsoft Office desktop applications on the PC/Windows or the Mac. And they are not going anywhere. When you hear that your organization is moving to Office 365 it does not mean you will ONLY be accessing Office from a web browser.

Office 365 is really Office anywhere, on any device. For example, Office 365 also includes applications on iPhone, iPad, and Android. And it is not just the traditional applications. There are incredible tools such as Forms, Sway, Teams, and for developers the Graph API. It also shifts workloads like Exchange Server for mail and SharePoint server for document management from local IT servers to servers run by Microsoft in the cloud.

So when you hear Office 365, DO NOT THINK GOOGLE DOCS. wlEmoticon-smile.png The online versions of Office are only there to provide an additional avenues to access Office from anywhere, on any device. Per the Office 365 website:

Works across multiple devices

Get the fully installed Office apps on multiple PCs, Macs, tablets, and mobile devices (including Windows, iOS, and Android).

They are not as fully featured and as such you really need to consider when you might use them over using the traditional Microsoft Office desktop applications. Per this site (my emphasis added):

Office for the web (formerly Office Web Apps) opens Word, Excel, OneNote, and PowerPoint documents in your web browser. Office for the web makes it easier to work and share Office files from anywhere with an internet connection, from almost any device. Microsoft Office 365 customers with Word, Excel, OneNote, or PowerPoint can view, create, and edit files on the go. …

The following tables compare Office for the web feature capabilities to feature-rich Microsoft Office desktop apps.

So, if you like your Excel just the way it is, installed on your scientific workstation with a bazillion gigabytes of RAM, you can still run it there if you move to Office 365. What you get with the traditional Office applications is more frequent, seamless and automatic updates from the web. Per the Office 365 website:

Monthly Updates

Get the latest features and capabilities with fully installed and always up-to-date versions of Outlook, Word, Excel, PowerPoint for Windows or Mac, OneNote (features vary), Teams, and Access and Publisher (PC only).

Hopefully, this has helped clear up and dispel the myths. And when you go back to the smart person that has told you otherwise, and continues to insist and tell you otherwise, please send them here. wlEmoticon-hotsmile.png

EXCEL: Auto-Run Macros Disabled without Warning

This entry applies if your security settings in Excel are set to “Disable all macros except digitally signed macros” like this:

disabledmacros

In this scenario, when you open a workbook that is digitally signed, you will get a yellow bar across the top of the screen, that says: “SECURITY WARNING Macros have been disabled”, like this:

macrowarning

But if the workbook is NOT signed and you attempt to open the workbook with macros, you will not see anything, no warning at all. The workbook will appear to open, but all macros will be disabled. If you try to run a macro, you will get the following warning:

macrowarning2

However, the insidious thing is your AutoRun macros will also not execute and you will get no warning. If your code performs some type of data refresh of the spreadsheet, you might not realize this did not happen and what you will be left with is stale data. For many financial institutions this can be a scary prospect.

To workaround this problem, I have created an XLAM that can warn your users that they opened an untrusted workbook. You can access the source and the add-in both on GitHub: VBAProjectNotSignedWarning-Addin

What this add-in does is look for XLSM file to be opened, it then checks to see if the VBAProjectSigned property is false. If the workbook code is not signed, macros are assumed to be in the file, and the user will get the following warning:

certwarningaddin

The SOURCE is provided in the depot because for it to run properly in your environment, you might want to open the XLSM sign the VBA Project with your corporate code certificate, secure the project with a password, and then Save A Copy as an XLAM (Excel Add-in). Once created, you can then place the add-in in the XLSTART folder in Program Files for each user in your organization. Then when they try to open an unsigned macro workbook, they will get a handy warning message.

The code for this is fairly simple. I will not go into each module, just the main function that performs the check of each Workbook that is opened via the Application.Workbook_Open event:


Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)
If Right(UCase(Wb.Name), 4) = "XLSM" Then
If Wb.VBASigned = False Then
MsgBox "The workbook '" & Wb.Name & "' is not signed. " & _
"If there are auto-run macros in the workbook, " & _
"they will not be run.", vbExclamation, "Signed Workbook Check"
End If
End If
End Sub

view raw

WorkbookOpen.vb

hosted with ❤ by GitHub

Using Azure Functions in Excel

I have been delving more and more into Azure recently. In looking for a way to build an Excel User Defined Function (UDF) that did not require Visual Basic for Applications (VBA) or similar, I looked at what I could do with:

And to my surprise it was actually very easy to set this up. Here are the steps:

  1. Log into https://portal.azure.com
  2. Click “Create a Resource” in the upper left
  3. Type “Functions App” then “Create”
  4. Then follow the directions to create your own Functions App called “ExcelFunctions,” for example.
  5. You will want to create an HTTP Trigger function and I called mine “AddNumbers”
  6. You will be asked where you want to edit the function and I chose online in the browser, which is what makes this the coolest thing ever.
  7. Here is what my function looks like:


#r "Newtonsoft.Json"
using System.Net;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Primitives;
using Newtonsoft.Json;
public static async Task<IActionResult> Run(HttpRequest req, ILogger log)
{
log.LogInformation("C# HTTP trigger function processed a request.");
string v1 = req.Query["value1"];
string v2 = req.Query["value2"];
if(v1!=null && v2!=null)
{
int result = int.Parse(v1) + int.Parse(v2);
return (ActionResult)new OkObjectResult($"{result}");
}
else
{
return new BadRequestObjectResult("Invalid input");
}
}

Once you have created your function, you can also test it right there in Azure. On the Right is a tab called “Test.” You can select it, and then add parameters like this:

azurefunctiontest

You can click “Save and Run” at the top of the page to run the function and verify the result: 42. Now, to get this to work in Excel, you need the URL. To do this click the “</> Get Function URL” button:

geturl

This will give you value like this:

https://xxx.azurewebsites.net/api/AddNumbers?code=123456…==

Now the fun part, getting this to work in Excel.

  1. Open Excel and create a new Workbook
  2. In  A1 type 100, in B1 type -58
  3. Select C1 and add the following function:

=WEBSERVICE("https://xxx.azurewebsites.net/api/AddNumbers?code=123456...==&value1=" & A1 & "&value2=" & B1)

Once you press enter and Excel updates you should see the value of 42 appear.

This is a very simple example of how you can implement an Azure Function in Excel as a User Defined Function. Imagine all the possibilities where you can collect data, analyze results, use Cognitive Services and vast stores of data from a Data Lake and much, much more. The possibilities are endless.

How to Configure VSCode for Office Development

I thought I had written this some time ago, but I guess I did not. So here goes…

NOTE: First, I want to state that if you are creating applications for the full Office clients, it might be best to continue using Visual Studio 2017 as you are able to debug directly in these clients with much more ease than you can from VSCode.

Some might find it hard to believe, but I have been geeking out hard and using VSCode to develop OfficeJS (Office 365 Web Add-ins). I find it a very useful, light client, where I am able to focus on just the basics. When combined with tools like Node Package Manager (NPM), browser sync, Yoeman and Git, you have a surprisingly robust development environment what makes doing things delightfully easy. wlEmoticon-hotsmile.png But it was not always this way, I had a learning curve and it took me a while to get it all setup and running correctly. I am not saying it was hard, just… different. So I channeled my inner geek and much to his delight, it turned out for the better.

So as I stated the challenge was getting it all setup. This is going to be very different, especially if you are coming from the Visual Studio 2017 world (much more so, from the VBA world). So, here are the step by step instructions I use for getting my VSCode development environment all configured for Office Development:

  1. Download and install VSCode: https://code.visualstudio.com/ .
  2. Download and install Node to get Node Package Manager (NPM): https://nodejs.org/en/download/. On this page you will want to download the Windows/MSI/64-bit version.
  3. Download and install Git: https://git-scm.com/download. On this page you will want to make sure you select to install the latest which is from the link in the upper right of the page.
  4. Now we have the basic packages we need in order to begin development. What we need to do now is install the Yeoman generator. To do this:
    • Open VSCode
    • Press CTRL+`. This will open the console window. Alternatively you can go to the View menu and click Integrated Terminal
    • Switch to the Terminal in the Console and then type the following command: npm install global yo
  5. Now you are ready to start generating OfficeJS add-ins from VSCode, and these steps will walk you through the first one:
    • Type: yo office sample-1
    • Once you do this the scaffold generator will kick in and ask you a few questions. Answer them as I have, in bold, below:? Would you like to create a new subfolder for your project? No
      ? Which Office client application would you like to support? Excel
      ? Would you like to create a new add-in? Yes, I need to create a new web app and manifest file for my add-in.
      ? Would you like to use TypeScript? No
      ? Choose a framework: Jquery
      ? Would you like to open it now while we finish creating your project? Yes
    • Once complete you will not have a solution folder called sample1 under your user profile c:\users\<yourname>\sample-1, but the project will not be open in VSCode, yet…
    • To open your project on the File menu, click Open Folder and then browse to and open the sample1 folder, then press Select Folder. NOTE: If you are coming from Visual Studio where the tree of solution files appears on the right side of the screen, the “Explorer” in VSCode appears on the left and it is actually a listing of ALL files and ALL folders in the solution directory.
    • You will see the code files for your project on the left hand side in the Explorer. The key files of importance to you, getting started will be:
      • sample-1-manifest.xml – this is your manifest for publishing your add-in.
      • index.html – this is your primary page or “task pane” for your add-in.
    • In this example I will not have you edit any of the files, but the basics are completely provided to build a solution. At this point we will check this into Git. Press CTRL+SHIFT+G. This will open the Source Control repository page. Click the icon to the right of the words Source Control, to Initialize Repository. This will open the folder to your solution, simply click Initialize Repository. You can now work with Git to manage your project. I will not go into more detail, but if you are interested, please watch this video: https://git-scm.com/video/what-is-git
    • Finally, we are ready to debug. But first, if you are on Windows 10, and you use Edge or Internet Explorer as your default browser, I would strongly suggest using the Google Chrome browser because the debug tools are so much better. However, there is one change you need to make:
      • Open the bsconfig.json file in the Explorer
      • At the top of the configuration you need to place this line: “browser”: “chrome”, so that the file looks like this:
        {
            "browser": "chrome",
            "ui": {...
    • To debug, press CTRL+` to open or return to the Integrated Terminal. Type: npm start. This will open your project in Chrome. You might get a warning that the site is not trusted, click Advanced and select to trust anyway / continue.
    • Open another tab and browse to office365.com. Once there, log into your account. On the Office menu in the upper left, click Excel. On the Insert menu, click Office Add-ins. In the upper right of the dialog, click Upload my add-in. Click Browse and then select the file: c:\users\<yourname>\sample-1\sample-1-manifest.xml. Your add-in will now load on the Home tab, switch there, and press Show Taskpane.

This looks like a lot of work and I know for most of you that are like me, coming from a Visual Studio and/or VBA background, this is very alien. You might consider this a step backwards or it might seem like it is time to hang up the spurs. wlEmoticon-disappointedsmile.png But give it some time, especially if you are just getting into OfficeJS, it will grow on you. In the meantime, here are some of my other blog entries around this to help you:

Please let me know if you have questions or would like some help with any of this. What I hope to be able to do at some point is to add posts on:

  • Importing a Script Lab project into a Yo Office scaffold
  • A video on the steps outlined in this post. I have done videos for internal training at Microsoft before, but never on my own for my blog. So this will be new territory if I can get around to it.
  • Other samples posts to getting started with various projects.

If you have any ideas of what you would like to see, please let me know.

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:

[code lang=”javascript” collapse=”true” title=”click to expand if the embedding below is not visible.”]
/**
* 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);
}
[/code]


/**
* 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);
}

view raw

setTimeout.js

hosted with ❤ by GitHub

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:

[code lang=”javascript” collapse=”true” title=”click to expand if the embedding below is not visible.”]
// 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
}
};
[/code]


// 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:

[code lang=”javascript” collapse=”true” title=”click to expand if the embedding below is not visible.”]
Wait.Show(null, true, function() {
Alert.Show("The user cancelled.");
});
setTimeout(function(){
Wait.CloseDialog();
Alert.Show("Done!");
}, 15000);
[/code]


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

Automating Excel and Add-ins Don’t load

There is a common problem I see when automating Excel from an external .NET application:

You will launch Excel, open a workbook, perform work, and then try to close Excel and find that it remains in memory. You might even have code in your solution to try to re-use existing instance of Excel and find that it uses one of these “zombie” instances that do not have any of the add-ins loaded… and maybe an add-in your code relies on.

The issue is that Excel is not able to close because  the primary thread of your application is holding on to a COM reference. Even adding Marshal.ComReleaseObject() and GC.Collect() like this probably will not improve the situation either:


Marshal.ReleaseComObject(excelWorkbook);
Marshal.ReleaseComObject(excelApp);
excelApp = null;
excelWorkbook = null;
GC.Collect();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.WaitForFullGCComplete();

view raw

COMRelease.cs

hosted with ❤ by GitHub

From my experience, and from what I have gathered is that until the thread is stopped, the COM reference will be locked. Not sure where the issue lies (EXCEL, COM, or .NET), but I have found a workaround to this. What you have to do is terminate your thread. But because you do not want to terminate your application, you need to create a NEW thread. So the following pattern is what I have found works:


OpenFileDialog openFileDialog1 = new OpenFileDialog();
openFileDialog1.Filter = "Excel Files|*.xls*";
DialogResult result = openFileDialog1.ShowDialog();
if (result == DialogResult.OK)
{
new Thread(() =>
{
Excel.Application excelApp = null;
Excel.Workbook excelWorkbook = null;
this.Invoke(new Action(() =>
{
try
{
excelApp = (Microsoft.Office.Interop.Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
}
catch
{
excelApp = new Excel.Application();
}
string workbookPath = openFileDialog1.FileName;
excelWorkbook = excelApp.Workbooks.Open(workbookPath);
excelApp.Visible = true;
}));
Marshal.ReleaseComObject(excelWorkbook);
Marshal.ReleaseComObject(excelApp);
excelApp = null;
excelWorkbook = null;
openFileDialog1 = null;
GC.Collect();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.WaitForFullGCComplete();
}).Start();
}

 

 

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.