Office wide After Save As Event (and tangent on extension methods and lambdas in Office code)

First off, I am way behind on my blogging. I actually owe a few blog entries to some folks that I will be getting around to. Life and work has been busy, complicated, not quite as balanced as I would like. Disappointed smile But this one issue has recently come up and is directly customer focused, therefore it gets the priority.

I was recently asked how to handle an After Save As scenario exactly the same in each application – as closely as possible. And only the Save As scenario. This is the scenario in which the user s saving a document for the first time or the user is choosing to same the same file with a different name and/or location.

So, wild tangent time… Hot smile  If you have been following my blogs for a while you will find out two things I like to do, call them programming style:

  1. Use extension methods
  2. Use inline Lambda expressions

This example today is no different. However, I recently got into a philosophical discussion on why I take these two approaches and WHY I think why you should too.

<rant>
Extension methods allow you to encapsulate a lot of code, allow for multiple re-use in other projects and once tested and vetted, keep the root entry points of your code (usually event methods or ribbon button clicks) cleaner. They are not any harder to debug, but do allow a debugger to potentially step over a large operation with an F10. I am very much about clean and neat code.

Lambda expressions are – lets admit it – cool. Smile But they serve a purpose, especially in threads to make “flow” more obvious. This is sort of the opposite end of the extension method argument in that sometimes putting a smaller operational block into the same method from which it will only ever be derived/called, just makes sense. It keeps it all in one place and easier to follow/debug.

Anyway, these are my opinions and different developers have their own styles. I invariably lose the conversation each time with my customers because by the time I get to their code: “it is just not the way we do things here.” Oh well. Punch
</rant>

With that said, lets get down to business. I have created a class called OfficeExtentions that works best in a separate Windows DLL project that you then reference in your core VSTO project. I demonstrate how to call it later. But here is the class:

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading;
using PowerPoint = Microsoft.Office.Interop.PowerPoint;
using Excel = Microsoft.Office.Interop.Excel;
using Word = Microsoft.Office.Interop.Word;

namespace OfficeExtensions
{
public static class OfficeExtensions
{
public delegate void AfterPowerPointSaveHandler(PowerPoint.Presentation PobjPres);
public delegate void AfterWordSaveHandler(Word.Document PobjDoc);
public delegate void AfterExcelSaveHandler(Excel.Workbook PobjWb);
private const int MCintDELAY = 1000;

/// <summary>
/// POWERPOINT EXTENSION METHOD - AFTER SAVE
/// This function allows you to pass in a function that you want to
/// have called after PowerPoint has completed a SaveAs. If the user
/// only performs a save, your method will not be called. This only
/// gets called when the Save As Dialog is used.
/// </summary>
/// <param name="PobjApp"></param>
/// <param name="PobjFunc"></param>
public static void AttachToPowerPointAfterSaveAsEvent(this PowerPoint.Application PobjApp, AfterPowerPointSaveHandler PobjFunc)
{
// FIRST - we attach to the BeforeSave event
PobjApp.PresentationBeforeSave += (PowerPoint.Presentation PobjPres, ref bool PbolCancel) =>
{
// start a new thread using LAMBDA
new Thread(() =>
{
Thread.Sleep(MCintDELAY); // need this delay
if (hasSaveAsDialogOpen())
{
while (hasSaveAsDialogOpen())
{
Thread.Sleep(1);
System.Windows.Forms.Application.DoEvents();
}
// look for the saveAs dialog and as long as it
// is open we will wait right here
PobjFunc.Invoke(PobjPres);
}
}
).Start();
};
}

/// <summary>
/// WORD EXTENSION METHOD - AFTER SAVE
/// This function allows you to pass in a function that you want to
/// have called after Word has completed a SaveAs. If the user
/// only performs a save, your method will not be called. This only
/// gets called when the Save As Dialog is used.
/// </summary>
/// <param name="PobjApp"></param>
/// <param name="PobjFunc"></param>
public static void AttachToWordAfterSaveAsEvent(this Word.Application PobjApp, AfterWordSaveHandler PobjFunc)
{
// FIRST - we attach to the BeforeSave event
PobjApp.DocumentBeforeSave += (Word.Document PobjDoc, ref bool PbolSaveAsUi, ref bool PbolCancel) =>
{
// start a new thread using LAMBDA
new Thread(() =>
{
Thread.Sleep(MCintDELAY); // need this delay
if (hasSaveAsDialogOpen())
{
while (hasSaveAsDialogOpen())
{
Thread.Sleep(1);
System.Windows.Forms.Application.DoEvents();
}
// look for the saveAs dialog and as long as it
// is open we will wait right here
PobjFunc.Invoke(PobjDoc);
}
}
).Start();
};
}

/// <summary>
/// EXCEL EXTENSION METHOD - AFTER SAVE
/// This function allows you to pass in a function that you want to
/// have called after Excel has completed a SaveAs. If the user
/// only performs a save, your method will not be called. This only
/// gets called when the Save As Dialog is used.
/// </summary>
/// <param name="PobjApp"></param>
/// <param name="PobjFunc"></param>
public static void AttachToExcelAfterSaveAsEvent(this Excel.Application PobjApp, AfterExcelSaveHandler PobjFunc)
{
// FIRST - we attach to the BeforeSave event
PobjApp.WorkbookBeforeSave += (Excel.Workbook PobjWb, bool PbolSaveAsUi, ref bool PbolCancel) =>
{
// start a new thread using LAMBDA
new Thread(() =>
{
Thread.Sleep(MCintDELAY); // need this delay
if (hasSaveAsDialogOpen())
{
while (hasSaveAsDialogOpen())
{
Thread.Sleep(1);
System.Windows.Forms.Application.DoEvents();
}
// look for the saveAs dialog and as long as it
// is open we will wait right here
PobjFunc.Invoke(PobjWb);
}
}
).Start();
};
}

#region API CODE
[DllImport("user32.dll", SetLastError = true, CharSet = CharSet.Auto)]
private static extern IntPtr FindWindowEx(IntPtr parentHandle, IntPtr childAfter, string className, string windowTitle);

[DllImport("user32.dll", SetLastError = true)]
private static extern uint GetWindowThreadProcessId(IntPtr hWnd, out uint lpdwProcessId);

/// <summary>
/// Helper function to see if Word has any dialogs open
/// </summary>
/// <returns></returns>
private static bool hasSaveAsDialogOpen()
{
const string LCstrWIN_CLASS = "#32770";
const string LCstrWIN_CAPTION = "Save As";
IntPtr LintHWin = IntPtr.Zero;
LintHWin = FindWindowEx(IntPtr.Zero, LintHWin, LCstrWIN_CLASS, LCstrWIN_CAPTION);
uint PID = 0;
while (LintHWin != IntPtr.Zero)
{
// Make sure that the window handle that we got is for the current running
// Office Application process. We do this by checking if the PID for this window
// our Office application are the same.
GetWindowThreadProcessId(LintHWin, out PID);
if (PID == Process.GetCurrentProcess().Id)
break; // found it and it belongs to our app
// get next window
LintHWin = FindWindowEx(IntPtr.Zero, LintHWin, LCstrWIN_CLASS, LCstrWIN_CAPTION);
}
return LintHWin != IntPtr.Zero;
}
#endregion
}
}

What this is doing is giving an extension method off the root of Application that allows you to attach to the event. When the event occurs it calls your function parameter.

I have built three extensions methods:

  • AttachToExcelAfterSaveEvent
  • AttachToWordAfterSaveEvent
  • AttachToPowerPointAfterSaveEvent

NOTE: I tried to get creative and simply create one overloaded function called “AttachToAfterSaveAsEvent” but this failed to compile in the Excel and PowerPoint VSTO add-ins because they required the Word.Application to be defined. Fair enough – if I added Word.Application references to my Excel and PowerPoint VSTO project all was copasetic – but WHY. Seems there is some strangeness going on in the Interops when using overloaded functions. I did not have time to investigate this further, so rather than require you to reference all the apps in each of your separate application specific projects, I opted for different names. If you feel so inclined to get it to work… please let me know if you do and you figured it out.

Each of these methods works the same. They attach to the BeforeSave event in each application, and kick off a thread. The thread is used so we will know we are OUTSIDE of the event handler when they are executed. In the thread we issue a small delay to allow the dialog to appear, and then look for a Save As dialog. If we detect one we go into a loop looking for that Save As dialog to disappear. Once it does – we call the function you define/passed as a parameter.

Here are the different ways you can call it:

  • As a LAMBA expression:
/// <summary>
/// STARTUP
/// </summary>
/// <param name="PobjSender"></param>
/// <param name="PobjEventArgs"></param>
private void ThisAddIn_Startup(object PobjSender, System.EventArgs PobjEventArgs)
{
// Attach a method to the Extension methods After Before Save As event
// in this case we are doing LAMBDA expression
Application.AttachToPowerPointAfterSaveAsEvent((PowerPoint.Presentation PobjPres) =>
{
MessageBox.Show("The filename is: " + PobjPres.FullName);
});
}

  • Or traditional means:
/// <summary>
/// STARTUP
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
// Attach a method to the Extension methods After Before Save As event
Application.AttachToExcelAfterSaveAsEvent(HandleAfterBeforeSaveAs);
}

/// <summary>
/// Handles the after SaveAs dialog
/// </summary>
/// <param name="PobjPres"></param>
private void HandleAfterBeforeSaveAs(Excel.Workbook PobjWb)
{
MessageBox.Show("The filename is: " + PobjWb.FullName);
}

There it is.

Also, I recently added RATINGS to my posts. Please rate this post if you so feel inspired to. I would like to know it is being read and that you found it useful. Open-mouthed smile

Counting down…

With one month and one week to go, it is time to start moving off Windows XP and Office 2003. Here is another great article from Microsoft about how/why:

Support for Windows XP and Office 2003 ends April 8, 2014 — what’s next?
http://blogs.technet.com/b/firehose/archive/2014/02/26/support-for-windows-xp-and-office-2003-ends-april-8-2014-what-s-next.aspx

A few interesting highlights from the article:

  • Windows XP and Office 2003, however, have been supported for more than a decade, or since “Baywatch” went off the air.
  • Computers currently running Windows XP and Office 2003 won’t stop working on April 9, but over time security and performance will be affected: Many newer apps won’t run on Windows XP; new hardware may not support Windows XP; and without critical security updates, PCs may become vulnerable to harmful viruses, spyware and other malicious software that can steal or damage personal information and business data.
  • Office 365 — the next generation of familiar Office productivity applications in the cloud. The subscription-based service offers familiar Office tools and maintains file integrity and design when documents are edited by multiple people, and it provides enterprise-class security and privacy.

If you are considering the move and have questions about your Microsoft Office Integrated Line of Business Applications, there are many ways Microsoft and Microsoft partners can assist you in assessing and remediating these solutions.

You can learn more about Office 365 for your business here: http://blogs.office.com/office365forbusiness/

CodePlex: Loading an Excel UDF from VSTO

When I look at my stats on my blog, one post gets dozens of hits a day:

[UPDATE] Creating Excel UDF’s in C#
http://davecra.com/2013/06/08/update-creating-excel-udfs-in-c/

This post is very popular and looking on the web, I see a lot of people chattering about it and a lot of questions. So, I have been asked repeatedly if I can post a project that demonstrates this, rather than just supply the code. I finally got around to creating a project on CodePlex and posted the source there. Here is a link to the shared project:

Loading an Excel UDF from VSTO

Getting Appointments on a specific date from Outlook

I was working on proof of concept Outlook add-in for a customer when I ran into  series of distressing exceptions trying to access specific recurring appointments from the calendar. There seems to be a great many ways to get a list of appointments for a specific date, but you may find the .Start date of these vary wildly. I went down a path using GetRecurrencePattern().GetOccurrence(DateTime.Now), and I got this exception a LOT:

“You changed one of the recurrences of this item, and this instance no longer exists. Close any open items and try again.”

After doing some more searching, I found that I was going about it all wrong. Now, I will be the first to say that I am not the strongest in the Outlook Object Model. Excel, Word and especially PowerPoint are by bread and butter. But sometimes, Outlook can be just downright confusing. Disappointed smile

In the end, this is what I came up with to get a list of all the appointments on a given date in a specific users calendar that you select from the Address list:

Outlook.Recipient LobjRecipient = null;
// crete a select names dialog
Outlook.SelectNamesDialog LobjSnd = MobjOutlook.Session.GetSelectNamesDialog();
// limit to the TO box
LobjSnd.NumberOfRecipientSelectors = Outlook.OlRecipientSelectors.olShowTo;
LobjSnd.AllowMultipleSelection = false; // there can be only one
LobjSnd.Display(); // display it
// do we have resolved names
if (!LobjSnd.Recipients.ResolveAll())
{
LobjRecipient = null; // NO
return; // exit out
}
else
{
LobjRecipient = LobjSnd.Recipients[1]; // yes
}
LobjSnd = null;
// open the shares Calendar folder
Outlook.MAPIFolder LobjFolder = MobjOutlook.ActiveExplorer().Session.GetSharedDefaultFolder(
LobjRecipient, Outlook.OlDefaultFolders.olFolderCalendar)
as Outlook.MAPIFolder;
// get all the items
Outlook.Items LobjItems = LobjFolder.Items;
LobjItems.Sort("[Start]"); // sort the items by start date
LobjItems.IncludeRecurrences = true; // be sure to include recurrences
string LstrDay = DateTime.Now.ToShortDateString(); // today
// set the find string to today 0:00 to 23:59:59
string LstrFind = "[Start] <= \"" + LstrDay + " 11:59 PM\"" +
" AND [End] > \"" + LstrDay + " 12:00 AM\"";
// find the first appointment for the day
Outlook.AppointmentItem LobjAppt = LobjItems.Find(LstrFind);
while (LobjAppt != null)
{
// ...do your thing here...

// get the next item
LobjAppt = LobjItems.FindNext();
}

XP/2003 Deadline Looms

If you are still on Windows XP and Office 2003, if you have not already started your migration, you should start ASAP. The end of support for BOTH is this April. Here is a great link that explains all the reasons you should start today: http://www.microsoft.com/en-us/windows/enterprise/endofsupport.aspx.

I have heard from a number of folks that are “stuck” in XP/2003 land. Namely, because they have a large number of Office based solutions, Excel VBA add-ins, XLL’s, UDF’s, macros and Access 2003 databases that must be migrated and no idea how to begin to remediate them. There is help out there and a number of partners and even service offerings from Microsoft that can help you.

If you have a solution in Office 2003 that you need help to remediate, please contact me. Send me a private tweet or send me an InMail on LinkedIn.

PInvoke Object Extender

If you have done work in Office (especially Excel) and performed operations that look across all the cells in the UsedRange, you might note – if you do a performance analysis of your code – that you will see up to 100ms per Object Model touch, especially for properties and methods from the Cell object. The issue here are all the checks and balances in the Primary Interops as well as the weight of .NET through COM. To avoid it you can Private Invoke (PInvoke) the Excel application directly and .NET does two things for you:

  1. It keeps the function table for the private interface cached so it does not always have to keep going and getting it for you.
  2. It allows you to interface with the EXE directly, bypassing several layers.

The end result, is usually something like a 100ms to 3ms performance improvement.

To help with PInvoke operations, I developed the following class that EXTENDS the OBJECT class type:

/// <summary>
/// This class extends object with a set of methods to help
/// PInvoke into COM objects much faster.
/// </summary>
public static class ObjectExtender
{
public static object InvokeMethod(this object PobjItem, string PstrMethod, object[] PobjMethodParams)
{
try
{
return PobjItem.GetType().InvokeMember(PstrMethod, BindingFlags.InvokeMethod, null, PobjItem, PobjMethodParams, null);
}
catch
{
return null;
}

}

public static object InvokeMethod(this object PobjItem, string PstrMethod)
{
return InvokeMethod(PobjItem, PstrMethod, null);
}

public static object GetProperty(this object PobjItem, string PstrProperty, object[] PobjParams)
{
try
{
return PobjItem.GetType().InvokeMember(PstrProperty, BindingFlags.GetProperty, null, PobjItem, PobjParams, null);
}
catch
{
return null;
}
}

public static object GetProperty(this object PobjItem, string PstrProperty)
{
return GetProperty(PobjItem, PstrProperty, null);
}

public static void SetProperty(this object PobjItem, string PstrProperty, object PobjValue)
{
try
{
object[] LobjVal = { PobjValue };
PobjItem.GetType().InvokeMember(PstrProperty, BindingFlags.SetProperty, null, PobjItem, LobjVal, null);
}
catch
{
return;
}
}

To use this code, you would do something like this:

LobjCell.InvokeMethod("Calculate");
object LobjVal = LobjCell.GetProperty("Validation");
object LobjFormula = LobjVal.GetProperty("Formula1");

VSTO Roadmap

I get asked the question a lot: Is VSTO going away?

With all the buzz and excitement around the new Apps for Office programming model, some Office Developers are feeling like VSTO is being abandoned. I have explained many times that it is NOT. VSTO is here to stay and your investment in solutions are safe.

And now, Michael Zlatkovsky (PM-VSTO), has confirmed – officially – the roadmap for VBA, VSTO and the new Apps for Office programming models. Specifically, let me call out an important quote:

At the same time, however, it’s important to note that all three technologies will be supported in the foreseeable future. This means that if you have an existing VBA or VSTO project, and if you’re satisfied with the capabilities, tooling, and marketability of your existing solution, you can be confident that your investments are safe.

So, keep on coding!