How to store information in a Excel WoorkBook

Question

I'm programing an Excel Addin with VS2008 and VSTO(C#) over Office 2007. This addin needs to store information embeded or inside the Wookbook to recover some status when the user opens again the Wookbook with this kind of information. Before save the Wookbook, the Addin serialize in XML all the information, and after open a WoorkBook the Addin try to deserialize this information if it found it.

I tried to use the Office.DocumentProperties but each string element is truncated (max.256 chars)

At events *Excel.AppEvents_WorkbookOpenEventHandler* and *Excel.AppEvents_WorkbookBeforeCloseEventHandler* :

Office.DocumentProperties properties = (Office.DocumentProperties)this.Application.ActiveWorkbook.CustomDocumentProperties;
properties.Add(Constants.ADDIN_PERSISTENCE, false, Office.MsoDocProperties.msoPropertyTypeString, serialize(configurationInstance), null);

At event *AppEvents_WorkbookOpenEventHandler*:

Office.DocumentProperties properties = (Office.DocumentProperties)Application.ActiveWorkbook.CustomDocumentProperties;
Configuration configurationInstance = deserialize((string)properties[Constants.ADDIN_PERSISTENCE]);

But this doesn't work because the serialization returns a string longer than 256 chars.

In other Addin, over Excel 2003, I store the information in the first cell on a "veryhidden" sheet with a strange name. I don't know if there are better solutions.

Any suggestion?

Answer 1

OK, I found "the good method". Office.CustomXMLParts is a collection to store XML data.

You can find information directly at Msdn[1]

MSDN example:

private void AddCustomXmlPartToWorkbook(Excel.Workbook workbook){
string xmlString =
    "<?xml version=\"1.0\" encoding=\"utf-8\" ?>" +
    "<employees xmlns=\"http://schemas.microsoft.com/vsto/samples\"&gt;" +
        "<employee>" +
            "<name>Karina Leal</name>" +
            "<hireDate>1999-04-01</hireDate>" +
            "<title>Manager</title>" +
        "</employee>" +
    "</employees>";
Office.CustomXMLPart employeeXMLPart = workbook.CustomXMLParts.Add(xmlString, missing);
}

Answer 2

nice method. thanks.