CODEWITHSUNDEEP
c#excelexcel-dna
James
James

Reputation: 93

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

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

Upvotes: 4

Views: 1507

Answers (2)

James
James

Reputation: 93

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

Upvotes: 4

Charles Williams
Charles Williams

Reputation: 23505

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

Upvotes: 4

Related Questions