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!

[UPDATE] Detect Auto-Save Multiple Versions

Giving credit where it is due is important and I thought the code Michael Zlatkovsky provided me after reading my previous post was important enough to add to my blog.

In my previous post, I pointed to a new property in Word 2013 that you can use to access whether a file is in the Auto Save or a regular save event call. You can technically write your code for Office 2010 in VS2010 and VSTO4/.NET4 and still access this property fairly easily, see the code below with Michaels comments.

Additionally, I added the Office 2007/2010 code in there as well, so it is a complete solution for Office 2007 / 2010 and 2013. Thanks Michael!

int version = int.Parse(Application.Version
        .Substring(0, Application.Version.IndexOf(".")));
if (version >= 15)
{
    // By using “dynamic”, you can get access to 
    // the IsInAutosave API even if you’re compiling 
    // against the Office 2010 PIAs. But then when 
    // the published add-in is run under Office 2013,
    // the IsInAutosave behavior is able to execute 
    // properly.
    dynamic dynamicDoc = Doc;
    if (dynamicDoc.IsInAutosave)
    {
        MessageBox.Show("Is Office 2013 autosave");
    }
    else
    {
        MessageBox.Show("If Office 2013 Normal save");
    }
}
else
{
    // This is the Office 2007 / Office 2010 logic
    object oBasic = Application.WordBasic;
    // this is where we invoke the object and
    // get the property. But we get an "object" 
    // back so be careful casting it.
    object fIsAutoSave =
         oBasic.GetType().InvokeMember(
             "IsAutosaveEvent",
             BindingFlags.GetProperty,
             null, oBasic, null);

    if (int.Parse(fIsAutoSave.ToString()) == 1)
    {
        MessageBox.Show("Is Office 2007/2010 AutoSave");
    }
    else
    {
        MessageBox.Show("Is Office 2007/2010 Normal save");
    }
}