CODEWITHSUNDEEP
vb.netexcelvisual-studio-2012add-in
cnotes
cnotes

Reputation: 247

Retrieve CustomXMLParts in Excel Addin using VB.NET

I'm needing a little help retrieving the values stored in a CustomXMLPart in an Excel Addin using VB.NET. I've searched and haven't found a lot of detail on this. From what I've found, the code that I have should work. At first, I thought my xml part wasn't being added so to persist across sessions, but I displayed the count of the CustomXMLParts collection before (3) and after (4) adding my custom xml part and the count did increase by one. I, also displayed the count upon opening the saved Excel workbook and it was the same number (4) after adding my CustomXMLPart to the collection. Here's the pertinent code below. Any help would be greatly appreciated. Need more information just let me know.

In the Excel addin, I have a popup window where I ask for user input and that's the information that I need to persist. In the code behind, that's where I create the xml and add to the collection.

Code:

    Dim workbook As Excel.Workbook = Globals.ThisAddIn.Application.ActiveWorkbook
    Dim xml As String

    xml = "<?xml version=""1.0"" encoding=""utf-8"" ?>" _
        & "<refreshViewPointData xmlns=""http://refreshviewpointdata.com"">" _
        & "<dataReference>" _
        & "<system>" & cboSystem.Text & "</system>" _
        & "<library>" & cboLibraries.Text & "</library>" _
        & "<view>" & txtObject.Text & "</view>" _
        & "<headers>" & chkInclColumnHdrs.Checked.ToString() & "</headers>" _
        & "<numOfRecords>" & txtRowCount.Text & "</numOfRecords>" _
        & "<reference>" & txtReference.Text & "</reference>" _
        & "</dataReference>" _
        & "</refreshViewPointData>"

    workbook.CustomXMLParts.Add(xml, System.Type.Missing)

In the ThisAddIn_Startup() method of ThisAddIn.vb file is where I attempt to retrieve the CustomXMLPart. I call a RetrieveCustomXMLPart() method from ThisAddIn_Startup().

Code for RetrieveCustomXMLParts():

    Dim parts As Microsoft.Office.Core.CustomXMLParts

    parts = Application.ActiveWorkbook.CustomXMLParts.SelectByNamespace("http://refreshviewpointdata.com")

    If parts.Count > 0 Then
        RefreshData(parts.ToString())
    End If

Code for RefreshData():

    Dim r As New RibbonViewPoint
    Dim viewXMLPart As New XmlDocument

    Dim system, library, sObject, reference As String
    Dim headers As Boolean
    Dim numRecords As Integer

    'Load the xml from the string.
    viewXMLPart.LoadXml(part)

    'Retrieve the values from the xml document.
    system = viewXMLPart.SelectSingleNode("/dataReference/system").Value
    library = viewXMLPart.SelectSingleNode("/dataReference/library").Value
    sObject = viewXMLPart.SelectSingleNode("/dataReference/view").Value
    headers = CType(viewXMLPart.SelectSingleNode("/dataReference/headers").Value, Boolean)
    numRecords = CType(viewXMLPart.SelectSingleNode("/dataReference/numOfRecords").Value, Integer)
    reference = viewXMLPart.SelectSingleNode("/dataReference/reference").Value

    'Call method to run the object to refresh the data.
    r.RunSelectedObject(system, library, sObject, headers, reference, numRecords)

In the RetrieveCustomXMLPart() method, the SelectByNamespace() method is not returning my CustomXMLPart that clearly has the same namespace as what I'm passing in. Anyone know what's wrong?

Also, if anyone has any insight on something else I didn't understand that would be great as well. In the RefreshData() method, I made the viewXMLPart variable as an XMLDocument to load the data and get the values from there. Prior to I had it defined as "Dim viewXMLPart As New Microsoft.Office.Core.CustomXMLPart", which kept giving me a syntax error saying that "'Microsoft.Office.Core.CustomXMLPartClass.Friend Sub New()' is not accessible in this context because it is Friend."

Thanks!!!

Upvotes: 0

Views: 967

Answers (1)

cnotes
cnotes

Reputation: 247

Found a solution. The below is what worked.

    Dim workbook = Application.ActiveWorkbook
    Dim customXMLParts = workbook.CustomXMLParts.SelectByNamespace("urn:viewpoint-refresh")
    Dim customXMLPart = customXMLParts.Cast(Of CustomXMLPart)().FirstOrDefault()

Upvotes: 1

Related Questions