Setting custom data in Clipboard in a .NET Excel addin

Question

I have a Excel addin written in C# that has some Copy/Paste processing code. When the user copies, I want to put my own TableData class object on the clipboard and retrieve it back when the user paste the data on the worksheet. Sounds real simple and I found a dozen articles online but for some reason it is not working in my code.

Here is my TableData class:

[Serializable]
[Guid("794E06D9-7CE4-40dc-8187-BDC105A0F866")]
public class TableData
{
    public string Name;

    public TableData()
    { }
}

I even gave it a GUID to make sure its loading the exact same class even if it is loading from some other assembly (if addin object loaded twice for some reason).

This is what I am doing to set data while copying:

TableData data = new TableData();
data.Name = "test";
Clipboard.SetData("MyTable", data);

And on Paste:

TableData data = Clipboard.GetData("MyTable") as TableData;

HERE >> data is null but if I print the GetData("MyTable") in immediate window, it shows a memory stream which I was unable to convert to TableData even by using the BinaryFormatter::DeSerialize() method

I might be doing some silly mistake here but I am unable to figure it out. Can somebody point out the problem here?

UPDATED

It is a COM addin and NOT a VSTO addin. I have set the macros in OnKey method so they get called when the user presses CTRL+C and CTRL+V in Excel worksheet. There is a separate VBA module that handles these requests and routes them to the .NET addin.

Application.OnKey("^c", "OnCopy");

So the call goes to VBA and then the VBA calls back into my .NET addin. Not sure if there is some other process involved in between the calls.

Works fine if I put and retrieve byte[] from the clipboard but not for my custom class

Answer 1

I created a simple VSTO addin with a Copy and a Paste button with the exact code above. The Table object is correctly available in the Paste hander.

1. How is Paste initiated in your addin? From an Excel event, or from user action in the addin?
2. Is this a VSTO addin that uses the task pane? Details, please.
3. Is the paste occurring in a different process instance of Excel?

Answer 2

This article lists some steps that may help. These include registering the clipboard format, and explicitly creating a DataObject and setting its data to your TableData, and then setting the clipboard data to the DataObject. It's worth a shot since it is only a few lines of code.

Answer 3

Gotcha!!

The problem was that the code was unable to find the same type during Paste. This is because that the addin was at location different from the host application i.e. Excel. I tried by serializing the class and then putting it on the clipboard. Then De-Serializing it back to the original type but it failed during De-Serialize and a "TargetInvocationException* was thrown saying "Unable to find assembly [addin assembly full name]". Clipboard::SetData(...) was not throwing any exception, it was just silently handling it and returning NULL.

The solution is to handle the AppDomain.CurrentDomain.AssemblyResolve event and return the current executing assembly when a query for addin's assembly comes.