How to unpack a VBA collection object passed to a C# VSTO Addin. RRS feed

  • Question

  • My apologies for the somewhat redundant post from last April, but I thought re-stating the issue might garner more of a response.

    I am trying to build a VSTO Excel Addin in C# that will accept a collection as a passed parameter. The collection is of an unspecified number of items, and actually has another collection as one of the properties, also of an undetermined number. I seem to be able to pass the collection object without an issue, but I cannot figure out how to “unpack” the collection on the Addin side.

    I would like to determine if the collection is enumerable and, if it is, iterate over or through it and extract the property values for each item in the collection and place them in a yet-to-be-constructed C# class on the Addin side.

    The problem seems to be that the Addin reads the incoming collection object as a system object, and that’s where the trouble starts. It doesn’t interpret the collection object as a collection at all.

    Using the Microsoft.VisualBasic reference I can get the “TypeName” of the object, which is “Collection”, but I can’t seem to get at the enumerability, the enumerator itself or the actual collection object property values.

    Any assistance will be greatly appreciated. This riddle is the key to the entire project.

    If you need the VBA code I’ll provide it, but it simply passes a collection built from a class and some strings.

    Here’s my code outline, which is obviously just the basic framework at this point:

    using System;
    using System.Diagnostics;
    using System.IO;
    using System.Net;
    using System.Net.Http;
    using System.Net.Http.Headers;
    using System.Collections;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Data;
    using System.Reflection;
    using System.Runtime.InteropServices;
    using Excel = Microsoft.Office.Interop.Excel;
    using System.Windows.Forms;
    using Silanis.ESL.SDK;
    using Silanis.ESL.SDK.Builder;
    namespace eSignLiveAddin
        [InterfaceType(ComInterfaceType.InterfaceIsIDispatch)] //added by mp on 4-13 based on research example.
        public interface IAddInUtilities
            int ESignLiveAPI_Call(object myObj, string docName, string apiURL, string userAPIKey);
        public class AddInUtilities : IAddInUtilities
            public int ESignLiveAPI_Call(object myObj, string docName, string apiURL, string userAPIKey) 
               //this is where i want to determine how many items are in the collection, and then be able to iterate over those items.
    String objType = Microsoft.VisualBasic.Information.TypeName(myObj); //this will show me that the "name" of the object type is a collection.
    //???How do I get at the numerator of myObj, property values, etc.?
                //int enumItem = myObj.GetEnumerator(); //this does NOT work.
                //This reflection code statement works fine on a non-collection object, but not a collection.
                //object EmailAddress = myObj.GetType().InvokeMember("EmailAddress", System.Reflection.BindingFlags.GetProperty, null, myObj, null);
                //string strEmailAddress = System.Convert.ToString(EmailAddress);
                return 223; //just making sure the add-in returns a value to my VBA app, and it does.

    • Moved by CoolDadTx Tuesday, February 5, 2019 2:52 PM Office related
    Tuesday, February 5, 2019 12:39 PM