Excel-DNA save custom data into worksheet (not cells)

Question

I'm hoping there is a method to save custom data from an Excel-DNA plugin into the workbook..

Something the user would enter into from a custom Excel-DNA form.

I realize this can be saved into cells, but I don't want the user to see or change the data..

Is there a method of attaching maybe an custom XML file to a workbook, or adding a hidden worksheet?

James

score 4 · Accepted Answer · answered May 18 '15 at 08:05

4

You can do this using Workbook.CustomXMLParts: see
Custom XML Parts Overview

answered May 18 '15 at 08:05

Charles Williams

23,121
5
38
38

score 4 · Answer 2 · answered May 19 '15 at 02:45

For those interested, the following worked from an Excel-DNA ribbon button:

Microsoft.Office.Interop.Excel.Application excelApp = (Microsoft.Office.Interop.Excel.Application)ExcelDnaUtil.Application;
Microsoft.Office.Interop.Excel.Workbook workbook = excelApp.ActiveWorkbook;

System.Collections.IEnumerator enumerator = workbook.CustomXMLParts.SelectByNamespace("http://schemas.microsoft.com/vsto/samplestest").GetEnumerator();
enumerator.Reset();


if (!(enumerator.MoveNext())) {
  string xmlString1 = "<?xml version=\"1.0\" encoding=\"utf-8\" ?>" +
    "<employees xmlns=\"http://schemas.microsoft.com/vsto/samplestest\">" +
    "<employee>" +
    "<name>Surender GGG</name>" +
    "<hireDate>1999-04-01</hireDate>" +
    "<title>Manager</title>" +
    "</employee>" +
    "</employees>";



  Office.CustomXMLPart employeeXMLPart = workbook.CustomXMLParts.Add(xmlString1);

} else {

  Office.CustomXMLPart a = (Office.CustomXMLPart)enumerator.Current;

  a.NamespaceManager.AddNamespace("x", "http://schemas.microsoft.com/vsto/samplestest");
           MessageBox.Show(a.SelectSingleNode("/x:employees/x:employee/x:name").Text);
  MessageBox.Show(a.XML);
}

First time adds the xml document, second time retrieves it. You can save the workbook & reopen to confirm it persists..

James

Excel-DNA save custom data into worksheet (not cells)

2 Answers2