How can I persist data for an Excel Ribbon Addin in Excel

Question

I have created an Excel ribbon addin which needs to persist user selections between Excel sessions. Using custom XML Parts seems to be the best option for this. However I can't get this to work without getting COMExceptions.

The MSDN docs are just not very useful (http://msdn.microsoft.com/en-us/library/bb608612.aspx). Can someone give me an example of making this work in an Excel Ribbon addin?

Answer 1

There are three different methods I know of:

Custom XML parts For an application-level add in, this is my preferred method of storing any application data that needs to be persisted in a saved xls file without ever being visible to the user.

http://msdn.microsoft.com/en-us/library/bb608612.aspx

Cached Data Islands This only works for document-level add ins. You will get exceptions if you try to use it in an application-level add in.

http://blogs.msdn.com/b/eric_carter/archive/2004/04/23/119294.aspx

Hidden worksheet Using VSTO, you can create invisible worksheets that cannot be seen by users. This works well, but leads to a lot of awkward coding to convert your data to fit in an excel sheet.

Update (2014): So in the end the usage of Custom XML parts turned out to be a performance issue so my application had to be changed back to use hidden worksheets. Apparently, once the XML reaches a certain size, Excel becomes very sluggish. My add-in had Custom Parts reach thousands of nodes, and the larger the XML grew, the slower everything in Excel became. For example, simply clicking on any cell would incur a very noticeable delay.

Answer 2

If you want to store any kind of metadata from an application level add-in with a specific document, you can serialise data to some kind of string (base64, xml etc.), and save it in a "very hidden" sheet. A worksheet with visibility set to "very hidden" will only be accessable via the programming API, so even if the user uncovers hidden sheets they will still not be able to access it, or indeed even know that it is there.

        // create sheet for this save
        workbook.Sheets.Add();
        newSettingsWorksheet = workbook.ActiveSheet;
        newSettingsWorksheet.Name = hiddenSheetName;
        newSettingsWorksheet.Visible = Excel.XlSheetVisibility.xlSheetVeryHidden;

One important thing to note, if you are storing a string longer than 32767 characters (the max number of characters that fit in a cell) then you will have to cut it into chunks and spread it across several cells.

Regarding the COM exceptions you are experiencing, you should be aware that Excel can throw a COM exception for ANY request that touches a COM object (e.g. a worksheet, a cell, or anything belonging to Excel) at ANY time if it is busy with another request (e.g. user is typing, it is recalculating formulae). Exceptions you can expect are:

HRESULT: 0x800AC472 (ignore)

HRESULT: 0x8000101A (retry later)

I guess the Excel app developers do this so an add-in can't make Excel itself look bad / unresponsive.

Answer 3

You should use the registry to store bits of information such as user preferences and history that need to persist after the application is shut down or that needs to be shared between multiple instances.

The user's hive (HKEY_CURRENT_USER) will never have permission problems. Just refer to the .NET Registry Class: http://msdn.microsoft.com/en-us/library/microsoft.win32.registry.aspx

Answer 4

Consider using custom properties. Each Excel sheet maintains, behind the scene, lists of properties that are easily used by the programmer. For instance, I've used custom properties to "remember" what items in ribbon drop down lists were chosen for a particular sheet; when the sheet changes, pull up the custom property for that sheet to find out what drop down items were picked when it was last active.

Custom properties persist with each sheet and the document.

using System;
using Microsoft.Office.Interop.Excel;

public partial class CustPropExample
{
  /// <summary>
  /// delete and then store the custom property by passed key and value
  /// </summary>
  bool bExcelCustProp_Replace(Worksheet wkSheet,
    string custPropKey,
    string custPropVal)
  {
    if (!ExcelCustProp_DeleteByKey(wkSheet, custPropKey))
      return (false);

    if (!ExcelCustProp_Add(wkSheet, custPropKey, custPropVal))
      return (false);

    return (true);
  }

  /// <summary>
  /// return the custom property value of passed key
  /// </summary>
  string ExcelCustProp_Get(Worksheet wkSheet,
    string key)
  {
    try
    {
      for (int i = 1; i <= wkSheet.CustomProperties.Count; i++) // NOTE: 1-based !!!!!!!!
      {
        if (wkSheet.CustomProperties.get_Item(i).Name == key)
          return (wkSheet.CustomProperties.get_Item(i).Value);
      }
    }
    catch (Exception ex)
    {
      ShowErrorMsg("Error with getting cust prop; key [" + key + "], exc: " + ex.Message, false);
    }

    return (string.Empty);
  }

  /// <summary>
  /// add cust prop
  /// </summary>
  bool ExcelCustProp_Add(Worksheet wkSheet,
    string key,
    string custPropVal)
  {
    try
    {
      wkSheet.CustomProperties.Add(key, custPropVal);
    }
    catch (Exception ex)
    {
      return(ShowErrorMsg("Error in adding cust prop: " + ex.Message, false));
    }
    return (true);
  }

  /// <summary>
  /// if passed key exists, delete it
  /// </summary>
  bool ExcelCustProp_DeleteByKey(Worksheet wkSheet,
    string key)
  {
    try
    {
      for (int i = 1; i <= wkSheet.CustomProperties.Count; i++) // NOTE: 1-based !!!!!!!!
      {
        if (wkSheet.CustomProperties.Item[i].Name == key)
        {
          wkSheet.CustomProperties.Item[i].Delete();
          break;
        }
      }
    }
    catch (Exception ex)
    {
      return(ShowErrorMsg("Error deleting cust prop (key='" + key + "') - " + ex.Message, false));
    }

    return (true);
  }

  /// <summary>
  /// stub for error handling
  /// </summary>
  bool ShowErrorMsg(string msg,
    bool retval)
  {
    System.Windows.Forms.MessageBox.Show(msg);
    return (retval);
  }

}