none
2D-Array to CustomXMLPart and back RRS feed

  • Question

  • Hi,

    at first a little background informations:

    I've created a class module in VBA to manage the "cascading combo box problem". The primary application for this is Excel.

    Now someone asks me how to get that to work in Word. As Word can have a table (which is similar to a sheet) I've added some code to load/save the data from a Word table.

    But as this table is always visible, I have been asked to use a CustomXMLPart instead.
    I made
    a few attempts, but I could not find a solution that meets my requirements.

    Here is a sample file:
    https://dl.dropboxusercontent.com/u/35239054/Cascade%20ComboBox.docm

    The basic of the SaveToTable, ReadFromTable routines is a 2D array.

    Now my questions:

    a) How can I save/load a 2D array into/from a CustomXMLPart?

    b) How can I 100% sure to pick the right CustomXMLPart when I open the file the next time?

    Andreas.

    Saturday, November 21, 2015 9:04 AM

Answers

  • Please feel free to let me know if you still have Word developing issues.


    Hi Fei,

    I got it done, see code below or downlaod this file:
    https://dl.dropboxusercontent.com/u/35239054/CustomXMLParts.docm


    And furthermore I can store a one or two dimensional array with data of any kind in Excel, Word or Powerpoint!

    The biggest problem was to understand that values have to be stored in attributes and not as nodenames or nodevalues!

    The result XML-file looks like this sample:

    <Array>
      <Boundary UBound="3" LBound="1">1</Boundary>
      <Boundary UBound="1" LBound="0">2</Boundary>
      <Item Value="Screws <= 5"" Type="8" Column="0" Row="1">1</Item>
      <Item Value="04.12.2015 11:49:49" Type="7" Column="1" Row="1">2</Item>
      <Item Value="Screws > 5"" Type="8" Column="0" Row="2">3</Item>
      <Item Value="123,456" Type="5" Column="1" Row="2">4</Item>
      <Item Value="Nuts & Bolts" Type="8" Column="0" Row="3">5</Item>
      <Item Value="Line&0x0D;&0x0A;break&0x09;tabbed" Type="8" Column="1" Row="3">6</Item>
    </Array>



    At the end of the code below you'll see Sub Example_SelectNode.
    Related to that code I've have some additional questions:

    a) How can I select all rows > 1 and < 3?
    b) How can I select only item 5?
    c) How can I select all items > 5?

    Andreas.

    'Version 1.1
    'Andreas Killer
    '04.12.15
    'Attribute VB_Name = "modCustomXMLPart"
    
    Option Explicit
    Option Compare Binary
    
    Sub Example_ArrayToCustomXMLPart()
      'Sample to show how to store/load data into/from a CustomXMLPart in Excel, Word or PowerPoint
      Dim Data, Data2
      Dim ID As String
      Dim i As Long, j As Long
    
      'Create a 2D array (boundarys as you like)
      ReDim Data(1 To 3, 0 To 1)
      'Write some "XML critical" values into the array
      Data(1, 0) = "Screws <= 5"""
      Data(2, 0) = "Screws > 5"""
      Data(3, 0) = "Nuts & Bolts"
      Data(1, 1) = Now
      Data(2, 1) = 123.456
      Data(3, 1) = "Line" & vbCrLf & "break" & vbTab & "tabbed"
    
      'Store the array as CustomXMLPart into this file
      ID = ArrayToCustomXMLPart(Data)
      'Get the array back into a 2nd variable
      Data2 = CustomXMLPartToArray(ID)
    
      'Only for this example: Validate the contents
      Debug.Print ID
      For i = LBound(Data) To UBound(Data)
        For j = LBound(Data, 2) To UBound(Data, 2)
          If Data(i, j) <> Data2(i, j) Or VarType(Data(i, j)) <> VarType(Data2(i, j)) Then
            'Uups. :-)
            Stop
          End If
        Next
      Next
      Debug.Print "100% match!"
      
      'Works also with 1D arrays
      ReDim Data(1 To 5)
      For i = LBound(Data) To UBound(Data)
        Data(i) = i
      Next
      ID = ArrayToCustomXMLPart(Data)
      Data2 = CustomXMLPartToArray(ID)
      
      'Only for this example: Validate the contents
      Debug.Print ID
      For i = LBound(Data) To UBound(Data)
        If Data(i) <> Data2(i) Or VarType(Data(i)) <> VarType(Data2(i)) Then
          'Uups. :-)
          Stop
        End If
      Next
      Debug.Print "100% match!"
      
      'Or empty arrays :-)
      Data = Array()
      ID = ArrayToCustomXMLPart(Data)
      Data2 = CustomXMLPartToArray(ID)
      
      Debug.Print ID
      If LBound(Data) <> LBound(Data2) Or UBound(Data) <> UBound(Data2) Then
        'Uups. :-)
        Stop
      End If
      Debug.Print "100% match!"
    End Sub
    
    Function ArrayToCustomXMLPart(ByVal Data, Optional ByVal ID As String, Optional ByVal File As Object) As String
      'Add an array to the Office file as custom XML part, returns the ID
      'If an ID is given, an existing custom XML part is used
      'Data can be a one or two dimensional array
      Dim CXP As CustomXMLPart
      Dim CXN As CustomXMLNode
      Dim i As Long, j As Long, k As Long, Size As Long, Typ As VbVarType
      Dim Value
    
      Size = Dimension(Data)
      Select Case Size
        Case Is < 0
          Err.Raise 5, "ArrayToCustomXMLPart", "Data is not an array"
        Case Is > 2
          Err.Raise 5, "ArrayToCustomXMLPart", "To many dimensions"
      End Select
    
      'Get the Active... if necessary
      If File Is Nothing Then Set File = ActiveFile
    
      On Error Resume Next
      Set CXP = File.CustomXMLParts.SelectByID(ID)
      On Error GoTo 0
      If CXP Is Nothing Then
        'Create a new one
        Set CXP = File.CustomXMLParts.Add("<Array/>")
      Else
        'Delete all existing elements
        For Each CXN In CXP.DocumentElement.ChildNodes
          CXN.Delete
        Next
        For Each CXN In CXP.DocumentElement.Attributes
          CXN.Delete
        Next
      End If
      'Return the GUID
      ArrayToCustomXMLPart = CXP.ID
    
      With CXP.DocumentElement
        'Store the properties of the array
        For i = 1 To Size
          .AppendChildNode "Boundary", NodeType:=msoCustomXMLNodeElement, NodeValue:=i
          With .LastChild
            .AppendChildNode "LBound", NodeType:=msoCustomXMLNodeAttribute, NodeValue:=LBound(Data, i)
            .AppendChildNode "UBound", NodeType:=msoCustomXMLNodeAttribute, NodeValue:=UBound(Data, i)
          End With
        Next
        'Search for values
        Select Case Size
          Case 0
            'Empty array
          Case 1
            For i = LBound(Data) To UBound(Data)
              If Not IsEmpty(Data(i)) Then
                Value = Data(i)
                GoSub StoreItem
              End If
            Next
          Case 2
            For i = LBound(Data) To UBound(Data)
              For j = LBound(Data, 2) To UBound(Data, 2)
                If Not IsEmpty(Data(i, j)) Then
                  Value = Data(i, j)
                  GoSub StoreItem
                End If
              Next
            Next
        End Select
    
        Exit Function
    StoreItem:
        'Check the data type
        Typ = VarType(Value)
        Select Case Typ
          Case VbVarType.vbBoolean:   'Okay
          Case VbVarType.vbByte:      'Okay
          Case VbVarType.vbCurrency:  'Okay
          Case VbVarType.vbDate:      'Okay
          Case VbVarType.vbDecimal:   'Okay
          Case VbVarType.vbDouble:    'Okay
          Case VbVarType.vbInteger:   'Okay
          Case VbVarType.vbLong:      'Okay
          Case VbVarType.vbSingle:    'Okay
          Case VbVarType.vbString:    'Okay
          Case VbVarType.vbVariant:   'Okay
          Case Else
            Err.Raise 458, "ArrayToCustomXMLPart", "Invalid data type"
        End Select
        'Add an item
        k = k + 1
        .AppendChildNode "Item", NodeType:=msoCustomXMLNodeElement, NodeValue:=k
        With .LastChild
          'Store the properties of the value
          .AppendChildNode "Row", NodeType:=msoCustomXMLNodeAttribute, NodeValue:=i
          If Size > 1 Then .AppendChildNode "Column", NodeType:=msoCustomXMLNodeAttribute, NodeValue:=j
          .AppendChildNode "Type", NodeType:=msoCustomXMLNodeAttribute, NodeValue:=Typ
          .AppendChildNode "Value", NodeType:=msoCustomXMLNodeAttribute, NodeValue:=StringToXML(Value)
        End With
        Return
      End With
    End Function
    
    Function CustomXMLPartToArray(ByVal ID As String, Optional ByVal File As Object) As Variant
      'Get the custom XML part from the Office file, converts and returns an array
      Dim CXP As CustomXMLPart
      Dim CXNs As CustomXMLNodes
      Dim CXN As CustomXMLNode, CAN As CustomXMLNode
      Dim Data, Value
      Dim Lb1 As Long, Ub1 As Long, Lb2 As Long, Ub2 As Long
      Dim Boundary() As Long
      Dim i As Long, j As Long, Size As Long, Typ As VbVarType
    
      'Get the Active... if necessary
      If File Is Nothing Then Set File = ActiveFile
    
      On Error Resume Next
      Set CXP = File.CustomXMLParts.SelectByID(ID)
      On Error GoTo 0
      If CXP Is Nothing Then Err.Raise 744, "CustomXMLPartToArray", "CustomXMLPart not found"
    
      'Get the properties of the array
      Set CXNs = CXP.SelectNodes("//Boundary")
      Size = CXNs.Count
      If Size = 0 Then
        'Empty array
        CustomXMLPartToArray = Array()
        Exit Function
      End If
      ReDim Boundary(1 To CXNs.Count, 1 To 2)
      For Each CXN In CXNs
        i = i + 1
        Boundary(i, 1) = CXN.SelectSingleNode("./@LBound").Text
        Boundary(i, 2) = CXN.SelectSingleNode("./@UBound").Text
      Next
      'Create it
      Select Case Size
        Case 1
          ReDim Data(Boundary(1, 1) To Boundary(1, 2))
        Case 2
          ReDim Data(Boundary(1, 1) To Boundary(1, 2), Boundary(2, 1) To Boundary(2, 2))
        Case Else
          Err.Raise 5, "CustomXMLPartToArray", "To many dimensions"
      End Select
    
      'Visit all items
      For Each CXN In CXP.SelectNodes("//Item")
        'Get the properties of the item
        'ToDo: Is SelectSingleNode faster as this FOR EACH?
        For Each CAN In CXN.Attributes
          With CAN
            Select Case .BaseName
              Case "Row": i = .NodeValue
              Case "Column": j = .NodeValue
              Case "Type": Typ = .NodeValue
              Case "Value": Value = XMLToString(.NodeValue)
            End Select
          End With
        Next
        'Convert to a VB data type
        Select Case Typ
          Case VbVarType.vbBoolean:   Value = CBool(Value)
          Case VbVarType.vbByte:      Value = CByte(Value)
          Case VbVarType.vbCurrency:  Value = CCur(Value)
          Case VbVarType.vbDate:      Value = CDate(Value)
          Case VbVarType.vbDecimal:   Value = CDec(Value)
          Case VbVarType.vbDouble:    Value = CDbl(Value)
          Case VbVarType.vbInteger:   Value = CInt(Value)
          Case VbVarType.vbLong:      Value = CLng(Value)
          Case VbVarType.vbSingle:    Value = CSng(Value)
          Case VbVarType.vbString:    'Okay
          Case VbVarType.vbVariant:   Value = CVar(Value)
          Case Else
            Err.Raise 458, "CustomXMLPartToArray", "Invalid data type"
        End Select
        Select Case Size
          Case 1
            Data(i) = Value
          Case 2
            Data(i, j) = Value
        End Select
      Next
      CustomXMLPartToArray = Data
    End Function
    
    Private Function ActiveFile(Optional ByVal App As Object) As Object
      'Get the active file of the application
      If App Is Nothing Then Set App = Application
      Select Case App.Name
        Case "Microsoft Excel"
          Set ActiveFile = CallByName(App, "ActiveWorkbook", VbGet)
        Case "Microsoft Word"
          Set ActiveFile = CallByName(App, "ActiveDocument", VbGet)
        Case "Microsoft PowerPoint"
          Set ActiveFile = CallByName(App, "ActivePresentation", VbGet)
        Case "Microsoft Publisher"
          Set ActiveFile = CallByName(App, "ActiveDocument", VbGet)
        Case Else
          Err.Raise 5, "CustomXMLPartToArray", "Unknown application"
      End Select
    End Function
    
    Private Function Dimension(Arr) As Long
      'Returns the number of dimensions of an array or 0 for an undimensioned array or -1 if no array at all.
      If IsArray(Arr) Then
        On Error GoTo Done
        Do
          Dimension = Dimension + 1
        Loop While IsNumeric(UBound(Arr, Dimension))
    Done:
        'Test for Array()
        If Dimension = 2 Then If UBound(Arr) < LBound(Arr) Then Dimension = 1
      End If
      Dimension = Dimension - 1
    End Function
    
    Private Function StringToXML(ByVal S As String) As String
      Dim Digit As String, i As Integer
      For i = 0 To 31
        If InStr(S, ChrW(i)) > 0 Then
          Digit = "&0x" & Right$("0" & Hex(i), 2) & ";"
          S = Replace(S, ChrW(i), Digit)
        End If
      Next
      StringToXML = S
    End Function
    
    Private Function XMLToString(ByVal S As String) As String
      Dim Digit As String, i As Integer
      If InStr(S, "&0x") > 0 Then
        For i = 0 To 31
          Digit = "&0x" & Right$("0" & Hex(i), 2) & ";"
          If InStr(S, Digit) > 0 Then
            S = Replace(S, Digit, ChrW(i))
            If InStr(S, "&0x") = 0 Then Exit For
          End If
        Next
      End If
      XMLToString = S
    End Function
    
    Sub DeleteAllCustomXMLParts() '(Optional ByVal File As Object)
      Dim File As Object
      'Delete all custom XML parts
      Dim CXP As CustomXMLPart
      'Get the Active... if necessary
      If File Is Nothing Then Set File = ActiveFile
      'Delete all non-built in
      For Each CXP In File.CustomXMLParts
        If Not CXP.BuiltIn Then CXP.Delete
      Next
    End Sub
    
    Private Sub Example_SelectNode()
      Dim CXP As CustomXMLPart
      Dim CXNs As CustomXMLNodes
      Dim CXN As CustomXMLNode
      Dim CAN As CustomXMLNode
      
      For Each CXP In ActiveFile.CustomXMLParts
        If Not CXP.BuiltIn Then
          If CXP.DocumentElement.BaseName = "Array" Then
            Debug.Print CXP.ID
            Exit For
          End If
        End If
      Next
      'Set CXP = ActiveDocument.CustomXMLParts.SelectByID("{F028FC16-9186-4D1C-BF95-E4E958275A51}")
      If CXP Is Nothing Then
        Debug.Print "CustomXMLPart not found"
        Exit Sub
      End If
      
      'Get all items
      Set CXNs = CXP.SelectNodes("//Item")
      'Get all rows > 1
      Set CXNs = CXP.SelectNodes("//*[@Row > 1]")
      'Get the left column   Note: Input was 'Array(1 to 3, 0 to 1)'
      Set CXNs = CXP.SelectNodes("//*[@Column = 0]")
    
      For Each CXN In CXNs
        Set CAN = CXN.SelectSingleNode("./@Value[1]")
        If CAN Is Nothing Then
          Debug.Print "Uups. Node has no 'Value' attribut?"
          Exit Sub
        Else
          Debug.Print CAN.NodeValue
        End If
      Next
    End Sub
    
    


    Friday, December 4, 2015 11:15 AM

All replies

  • Hi Andreas

    a) A custom xml part consists of well-formed XML. You need to learn how to correctly structure XML - which is not a topic for the Word forum. You need to ask in an XML forum, but do some basic research, first, since this is very ground-level.

    Once you've settled on the basic XML structure you can save that to a file you load or you can hard-code it in your VBA. To create the Custom XML Part you need Document.CustomXMLParts.Add and you can include the XML as a string or load it from the file later using CustomXMLPart.Load.

    VBA contains the basic tools for manipulating XML DOM in the Custom XML Part: AddNode, SelectNodes, SelectSingleNode. These all work the same as the commands in the MSXML Parser (which is actually used in the background).

    b) After you figure out the basics of XML, research the term "Namespace". Declare your own namespace in your XML then you can always retrieve the Custom XML Part based on the namespace. The VBA method for this (once you get that far) is: Document.CustomXMLParts.SelectByNamespace


    Cindy Meister, Office Developer/Word MVP, <a href="http://blogs.msmvps.com/wordmeister"> my blog</a>

    Monday, November 23, 2015 4:58 PM
    Moderator
  • You need to ask in an XML forum, but do some basic research, first, since this is very ground-level.

    Cindy,

    CustomXMLPart is after all a part of Word, right?

    When I ask about CustomXMLPart in an XML forum the answer is:
    "This question is related to Word, ask in a Word forum."

    Do you know the XML Tools AddIn for Excel 2003? I've updated that code to Excel 2010 in 2011!
    https://dl.dropboxusercontent.com/u/35239054/XmlTools.zip
    But is that helpful for my task? No!

    I've done already some basic research.
    I've read the help, but the samples in the help are sh..., none of them work.
    I've tried to find more information about CustomXMLPart using Google, no luck, no working examples.

    If my question is so simple, are you not able to give me a simple and working example?

    Andreas.

    Monday, November 23, 2015 7:38 PM
  • Rephrase your question for an XML forum. Ask about how to store a 2-dimensional array in XML and don't mention Word. It doesn't matter whether this is Word or some other kind of program, as far as the XML goes.

    If you want an example of one possible way to do it, then:

    write the xml identifier here, I'm not taking the time to look it up
    <array>
      <item>
        <mem1>value</mem1>
        <mem2>value</mem2>
      </item>
      <item>
        <mem1>value</mem1>
        <mem2>value</mem2>
      </item
      <item>
        <mem1>value</mem1>
        <mem2>value</mem2>
      </item
    </array>


    Cindy Meister, Office Developer/Word MVP, <a href="http://blogs.msmvps.com/wordmeister"> my blog</a>

    Monday, November 23, 2015 7:55 PM
    Moderator
  • If you want an example of one possible way to do it, then:

    I've seen many sides that shows XML samples like that, but nowhere how to create them with VBA in Word!

    How to do that in Word with VBA with a CustomXMLPart?
    And how to get that back from a CustomXMLPart with VBA into a 2D-Array?

    And you are wrong, the methods in VBA in Word doesn't store the items in that way.

    I already tried and the XML result is this:

    <root>America<row1col2><row1col3><row1col4><row2col1>Canada<row2col3><row2col4><row3col1><row3col2>Quebec<row3col4><row4col1><row4col2><row4col3>Montreal<row5col1><row5col2><row5col3>Quebec City<row6col1><row6col2><row6col3>Laval<row7col1><row7col2>Yukon<row7col4><row8col1><row8col2><row8col3>Carmacs<row9col1><row9col2><row9col3>Dawson<row10col1>USA<row10col3><row10col4><row11col1><row11col2>Oregon<row11col4><row12col1><row12col2><row12col3>Portland<row13col1><row13col2><row13col3>Salem<row14col1><row14col2><row14col3>Eugene<row15col1><row15col2>Utah<row15col4><row16col1><row16col2><row16col3>Fairfield<row17col1><row17col2><row17col3>Europe<row18col2><row18col3><row18col4><row19col1>Germany<row19col3><row19col4><row20col1><row20col2>Niedersachsen<row20col4><row21col1><row21col2><row21col3>Hannover<row22col1><row22col2><row22col3>Oldenburg<row23col1><row23col2>Bayern<row23col4><row24col1><row24col2><row24col3>München<row25col1><row25col2><row25col3>Augsburg<row26col1><row26col2><row26col3>Nürnberg</row26col3></row26col2></row26col1></row25col3></row25col2></row25col1></row24col3></row24col2></row24col1></row23col4></row23col2></row23col1></row22col3></row22col2></row22col1></row21col3></row21col2></row21col1></row20col4></row20col2></row20col1></row19col4></row19col3></row19col1></row18col4></row18col3></row18col2></row17col3></row17col2></row17col1></row16col3></row16col2></row16col1></row15col4></row15col2></row15col1></row14col3></row14col2></row14col1></row13col3></row13col2></row13col1></row12col3></row12col2></row12col1></row11col4></row11col2></row11col1></row10col4></row10col3></row10col1></row9col3></row9col2></row9col1></row8col3></row8col2></row8col1></row7col4></row7col2></row7col1></row6col3></row6col2></row6col1></row5col3></row5col2></row5col1></row4col3></row4col2></row4col1></row3col4></row3col2></row3col1></row2col4></row2col3></row2col1></row1col4></row1col3></row1col2></root>

    Here is my test file:
    https://dl.dropboxusercontent.com/u/35239054/Samples/2e4e513e-259b-4cac-9108-b661d1446aa0.docm

    And when I try to read it back and create a 2D-array, I failed, because the items are not returned as they are stored!

    Or I do something fundamentally wrong or I hit a BUG or whatever, I have no idea.

    So what I ask for is a simple sample how to store a 2D-array into a CustomXML with VBA and read it back later, that's all.

    Can you please give me a sample? Or is it really so difficult?

    Andreas.

    Tuesday, November 24, 2015 9:07 AM
  • Hi Andreas

    When you ask something like this, you need to supply what you already have - in this case the XML structure you want to have. Without that starting point there's nothing we can do to help you. The XML should be formatted so that it's readable, similar to what I did. That kind of formatting also reveals structural mistakes more readily than when things are in a single line.

    FWIW the XML you show us is not well-formed - the structure is incorrect. Before you have at least enough basics to create the XML structure correctly it makes no sense to discuss code. These forums are for showing you how to solve your own problems, not writing the code for you. People who help out here get paid for that...

    I will not download and open documents from the forums, the security risk is too great.

    If you don't already have one, get an XML editor tool. That will help you with these kinds of basics. For example:
      https://xmlnotepad.codeplex.com/
      https://www.microsoft.com/en-us/download/details.aspx?id=7973


    Cindy Meister, Office Developer/Word MVP, <a href="http://blogs.msmvps.com/wordmeister"> my blog</a>

    Tuesday, November 24, 2015 4:14 PM
    Moderator
  • You need to supply what you already have - in this case the XML structure you want to have.

    I will not download and open documents from the forums, the security risk is too great.

    It doesn't matter for me which structure the XML has or not has, I don't care!

    All I want is to store the data of a two dimensional array into a CustomXMLPart and read it back later.

    You don't want to download documents, okay, I have this Word table:
    EDIT: I don't know why, but the forum doesn't shows the table correctly. It seems only that some lines are missing. You can copy the table from here and paste into Word.

    America

     

     

     

     

    Canada

     

     

     

     

    Quebec

     

     

     

     

    Montreal

     

     

     

    Quebec City

     

     

     

    Laval

     

     

    Yukon

     

     

     

     

    Carmacs

     

     

     

    Dawson

     

    USA

     

     

     

     

    Oregon

     

     

     

     

    Portland

     

     

     

    Salem

     

     

     

    Eugene

     

     

    Utah

     

     

     

     

    Fairfield

     

     

     

    Vineyard

    Europe

     

     

     

     

    Germany

     

     

     

     

    Niedersachsen

     

     

     

     

    Hannover

     

     

     

    Oldenburg

     

     

    Bayern

     

     

     

     

    München

     

     

     

    Augsburg

     

     

     

    Nürnberg

    I have this code to read it into a 2D-array:

    Function TableToArray(ByVal T As Word.Table) As Variant
      Dim Row As Long, Col As Long
      Dim Data, Value
      On Error Resume Next
      With T
        ReDim Data(1 To .Rows.Count, 1 To .Columns.Count)
        For Row = 1 To UBound(Data)
          For Col = 1 To UBound(Data, 2)
            Value = TrimWhite(T.Cell(Row, Col).Range.Text)
            If Value = "" Then Value = Empty
            Data(Row, Col) = Value
          Next
        Next
      End With
      TableToArray = Data
    End Function
    
    Private Function TrimWhite(ByVal S As String) As String
      'Return a string with white space removed
      Dim i As Long
      For i = 1 To Len(S)
        If Asc(Mid$(S, i, 1)) < 32 Then Mid$(S, i, 1) = vbNullChar
      Next
      TrimWhite = Replace$(S, vbNullChar, "")
    End Function

    That's it, anything that follows is what I've tried:

    Sub ArrayToCustomXMLPart() Dim Data Data = TableToArray(ActiveDocument.Tables(1)) Dim Row As Long, Col As Long Dim CXP As CustomXMLPart Dim CXN As CustomXMLNode Set CXP = ActiveDocument.CustomXMLParts.Add("<root>data</root>") Debug.Print CXP.ID Set CXN = CXP.SelectSingleNode("/root") For Row = 1 To UBound(Data) For Col = 1 To UBound(Data, 2) If Not IsEmpty(Data(Row, Col)) Then CXN.Text = Data(Row, Col) Else CXN.AppendChildNode "row" & Row & "col" & Col Set CXN = CXN.LastChild End If Next Next End Sub Sub CustomXMLPartToArray() Dim CXP As CustomXMLPart Dim CXN As CustomXMLNode
    Dim Data Set CXP = ActiveDocument.CustomXMLParts.SelectByID("{84F3F7DC-A7D8-46A8-BF31-023199505C46}") End Sub

    Andreas.


    Tuesday, November 24, 2015 4:56 PM
  • <<It doesn't matter for me which structure the XML has or not has, I don't care! >>

    You have to care because what goes into a Custom XML Part must be well-formed XML. And the code to write and read it has to work with that XML. You have to understand what you're doing, otherwise you can't write the code.

    If you're not able to make the effort, turn your array into a delimited text string and write it to a DocumentVariable object (that's text stored in the document, but not visible to the user). You can retrieve the string from the DocumentVariable and turn it back into an array. The VBA commands, in case you're not already familiar with them, are Join and Split.


    Cindy Meister, Office Developer/Word MVP, <a href="http://blogs.msmvps.com/wordmeister"> my blog</a>

    Tuesday, November 24, 2015 5:12 PM
    Moderator
  • You have to care because what goes into a Custom XML Part must be well-formed XML. And the code to write and read it has to work with that XML. You have to understand what you're doing, otherwise you can't write the code.

    Yes, exactly! But I don't what is the best XML structure for this case and how to use the CustomXMLPart to create that structure.

    That's why I ask for help. So can you help me?

    BTW, you can choose any XML structure you like. A DocumentVariable is not an option.

    Andreas.

    Wednesday, November 25, 2015 8:09 AM
  • Hi Andres,

    Have you fixed this issue now? I wrote a sample to demonstrate that this procedure:

    Option Explicit
    
    Sub DeleteAllCustomXMLParts()
      Dim CXP As CustomXMLPart
      For Each CXP In ActiveDocument.CustomXMLParts
        If Not CXP.BuiltIn Then CXP.Delete
      Next
    End Sub
    
    Function TableToArray(ByVal T As Word.Table) As Variant
      Dim Row As Long, Col As Long
      Dim data, Value
      On Error Resume Next
      With T
        ReDim data(1 To .Rows.Count, 1 To .Columns.Count)
        For Row = 1 To UBound(data)
          For Col = 1 To UBound(data, 2)
            Value = TrimWhite(T.Cell(Row, Col).Range.Text)
            If Value = "" Then Value = Empty
            data(Row, Col) = Value
          Next
        Next
      End With
      TableToArray = data
    End Function
    
    Sub ArrayToCustomXMLPart()
      Dim data
      data = TableToArray(ActiveDocument.Tables(1))
      
      Dim Row As Long, Col As Long
      Dim CXP As CustomXMLPart
      Dim CXN As CustomXMLNode
      
      DeleteAllCustomXMLParts
      
      Set CXP = ActiveDocument.CustomXMLParts.Add("<root></root>")
      Debug.Print CXP.ID
      Set CXN = CXP.SelectSingleNode("/root")
      
      Dim lastPosition(2) As Variant
      Dim columnsCount As Integer
      columnsCount = 4
    
      
      CXN.AppendChildNode data(1, 1)
        Set CXN = CXP.SelectSingleNode("//" & data(1, 1))
      For Row = 2 To UBound(data)
        For Col = 1 To UBound(data, 2)
          If Not IsEmpty(data(Row, Col)) Then
           
            If Col > lastPosition(1) Then
                  
                    CXN.AppendChildNode data(Row, Col)
                    Set CXN = CXP.SelectSingleNode("//" & data(Row, Col))
            ElseIf Col = lastPosition(1) Then
                 Set CXN = CXN.ParentNode
                 CXN.AppendChildNode data(Row, Col)
               Set CXN = CXP.SelectSingleNode("//" & data(Row, Col))
            Else
                Set CXN = FindParentCXN(CXN, Col, data)
               CXN.AppendChildNode data(Row, Col)
                   Set CXN = CXP.SelectSingleNode("//" & data(Row, Col))
            End If
            
            lastPosition(0) = Row
            lastPosition(1) = Col
         
          End If
        Next
      Next
    End Sub
    
    Sub CustomXMLPartToArray()
      Dim CXP As CustomXMLPart
      Dim CXN As CustomXMLNode
      'Set CXP = ActiveDocument.CustomXMLParts.SelectByID("{84F3F7DC-A7D8-46A8-BF31-023199505C46}")
      
     
      For Each CXP In ActiveDocument.CustomXMLParts
        If Not CXP.BuiltIn Then Exit For
      Next
      
      Set CXN = CXP.DocumentElement
      ShowChildNodes CXN, 0
    End Sub
    
    Sub ShowChildNodes(ByVal PXN As CustomXMLNode, ByVal Level As Integer)
      Dim CXN As CustomXMLNode
      For Each CXN In PXN.ChildNodes
        If CXN.HasChildNodes Then
          ShowChildNodes CXN, Level + 1
        Else
          Debug.Print String(Level, "-") & CXN.NodeValue
        End If
      Next
    End Sub
    
    Private Function TrimWhite(ByVal S As String) As String
      'Return a string with white space removed
      Dim i As Long
      For i = 1 To Len(S)
        If Asc(Mid$(S, i, 1)) < 32 Then Mid$(S, i, 1) = vbNullChar
      Next
      TrimWhite = Replace$(S, vbNullChar, "")
    End Function
    
    Function FindParentCXN(currentNode As CustomXMLNode, ByVal targetColumn, data As Variant)
    
    While Not FindColumn(currentNode.BaseName, data) < targetColumn
    Set currentNode = currentNode.ParentNode
    Wend
    Set FindParentCXN = currentNode
    End Function
    
    Function FindColumn(findValue As String, data As Variant)
    Dim Row As Long, Col As Long
       For Row = 1 To UBound(data)
          For Col = 1 To UBound(data, 2)
           
            If data(Row, Col) = findValue Then
                FindColumn = Col
            End If
          Next
        Next
    End Function

    Note, since the empty string in XML node is invalid character in Word, to run this code sample successfully, please change the "Quebec City" to "QuebecCity".

    In addition, if you have any problems about XML, you can try to get more effective support from XML forum as Cindy suggested.

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.



    Tuesday, December 1, 2015 8:38 AM
    Moderator
  • Have you fixed this issue now? I wrote a sample to demonstrate that this procedure:

    Node, since the empty string in XML node is invalid character in Word, to run this code sample successfully, please change the "Quebec City" to "Quebec City".

    Hi Fei,

    thank you very much for that sample code. I've updated the sample file with your code, but I get a RTE 0xE0041806 also on a different places. Here's the file:
    https://dl.dropboxusercontent.com/u/35239054/Samples/2e4e513e-259b-4cac-9108-b661d1446aa0.docm

    What I don't understand is that:

    Word saves a Word-table as XML internal, Excel saves any sheet as XML internal.
    And you can call it as you like, but the data in a table/sheet is nothing else as a 2D-array.

    So why do I have to change anything?
    When Word can save the table as is as XML, why can't I do that?
    Is there no
    general XML structure to store data from a 2D-array?

    I can not believe that I am the first who asks. :-)

    Andreas.


    Tuesday, December 1, 2015 11:23 AM
  • Download the Open XML SDK productivity Tool and inspect the actual XML Word and Excel are using to store tables. you'll see it's NOT a simple 2-D array. There's a lot of structural and formatting information stored as well. And Word and Excel store the information differently.

    And, yes, you are the first person I've ever seen ask that. Probably because you can't be bothered to research and learn about the technologies.


    Cindy Meister, Office Developer/Word MVP, <a href="http://blogs.msmvps.com/wordmeister"> my blog</a>

    Tuesday, December 1, 2015 2:38 PM
    Moderator
  • Hi Andreas,

    The "Quebec City" contain a blank space, we need to remove it before we add a custom node in Word with this string as the name.

    In the sample document, I just change the "Quebec City" to "QuebecCity" in the document, you can try to format the data in the code.

    Here is the document for your reference:
    http://1drv.ms/1XFZmCH

    Regards & Fei

     


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, December 2, 2015 6:45 AM
    Moderator
  • The "Quebec City" contain a blank space, we need to remove it before we add a custom node in Word with this string as the name.

    Fei,

    A node in a CustomXMLPart in Word can not store "Quebec City"? That can not be right...

    In the interim I've got help from Gregory K. Maxey:
    http://gregmaxey.mvps.org/

    I still have to take a few hurdles, but I think I'm close to a very simple solution to store any kind of data into XML, without any knowledge of XML structures and all that sh....

    Andreas.

    Wednesday, December 2, 2015 4:41 PM
  • Hi Andres,

    >>A node in a CustomXMLPart in Word can not store "Quebec City"? That can not be right...<<

    Sorry for the confusion. The Word can contain the "Quebec City" in the content, however it can't be store it as an custom element node's name.

    This is not the limitation of Word but XML. Here are some rules for the name of XML:

      • Element names are case-sensitive
      • Element names must start with a letter or underscore
      • Element names cannot start with the letters xml (or XML, or Xml, etc)
      • Element names can contain letters, digits, hyphens, underscores, and periods
      • Element names cannot contain spaces

      Any name can be used, no words are reserved (except xml).

    You can research the rules for the XML name. Please feel free to let me know if you still have Word developing issues.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, December 3, 2015 8:54 AM
    Moderator
  • This is not the limitation of Word but XML. Here are some rules for the name of XML:
      • Element names are case-sensitive
      • Element names must start with a letter or underscore
      • Element names cannot start with the letters xml (or XML, or Xml, etc)
      • Element names can contain letters, digits, hyphens, underscores, and periods
      • Element names cannot contain spaces

      Any name can be used, no words are reserved (except xml).

    Hi Fei,

    thank you for that information, indeed very helpful.

    And in a sample from Gregory K. Maxey I've seen how to add attributes to a node, now it's only a small step... I'll be back tomorrow.

    Andreas.

    Thursday, December 3, 2015 11:27 AM
  • Please feel free to let me know if you still have Word developing issues.


    Hi Fei,

    I got it done, see code below or downlaod this file:
    https://dl.dropboxusercontent.com/u/35239054/CustomXMLParts.docm


    And furthermore I can store a one or two dimensional array with data of any kind in Excel, Word or Powerpoint!

    The biggest problem was to understand that values have to be stored in attributes and not as nodenames or nodevalues!

    The result XML-file looks like this sample:

    <Array>
      <Boundary UBound="3" LBound="1">1</Boundary>
      <Boundary UBound="1" LBound="0">2</Boundary>
      <Item Value="Screws <= 5"" Type="8" Column="0" Row="1">1</Item>
      <Item Value="04.12.2015 11:49:49" Type="7" Column="1" Row="1">2</Item>
      <Item Value="Screws > 5"" Type="8" Column="0" Row="2">3</Item>
      <Item Value="123,456" Type="5" Column="1" Row="2">4</Item>
      <Item Value="Nuts & Bolts" Type="8" Column="0" Row="3">5</Item>
      <Item Value="Line&0x0D;&0x0A;break&0x09;tabbed" Type="8" Column="1" Row="3">6</Item>
    </Array>



    At the end of the code below you'll see Sub Example_SelectNode.
    Related to that code I've have some additional questions:

    a) How can I select all rows > 1 and < 3?
    b) How can I select only item 5?
    c) How can I select all items > 5?

    Andreas.

    'Version 1.1
    'Andreas Killer
    '04.12.15
    'Attribute VB_Name = "modCustomXMLPart"
    
    Option Explicit
    Option Compare Binary
    
    Sub Example_ArrayToCustomXMLPart()
      'Sample to show how to store/load data into/from a CustomXMLPart in Excel, Word or PowerPoint
      Dim Data, Data2
      Dim ID As String
      Dim i As Long, j As Long
    
      'Create a 2D array (boundarys as you like)
      ReDim Data(1 To 3, 0 To 1)
      'Write some "XML critical" values into the array
      Data(1, 0) = "Screws <= 5"""
      Data(2, 0) = "Screws > 5"""
      Data(3, 0) = "Nuts & Bolts"
      Data(1, 1) = Now
      Data(2, 1) = 123.456
      Data(3, 1) = "Line" & vbCrLf & "break" & vbTab & "tabbed"
    
      'Store the array as CustomXMLPart into this file
      ID = ArrayToCustomXMLPart(Data)
      'Get the array back into a 2nd variable
      Data2 = CustomXMLPartToArray(ID)
    
      'Only for this example: Validate the contents
      Debug.Print ID
      For i = LBound(Data) To UBound(Data)
        For j = LBound(Data, 2) To UBound(Data, 2)
          If Data(i, j) <> Data2(i, j) Or VarType(Data(i, j)) <> VarType(Data2(i, j)) Then
            'Uups. :-)
            Stop
          End If
        Next
      Next
      Debug.Print "100% match!"
      
      'Works also with 1D arrays
      ReDim Data(1 To 5)
      For i = LBound(Data) To UBound(Data)
        Data(i) = i
      Next
      ID = ArrayToCustomXMLPart(Data)
      Data2 = CustomXMLPartToArray(ID)
      
      'Only for this example: Validate the contents
      Debug.Print ID
      For i = LBound(Data) To UBound(Data)
        If Data(i) <> Data2(i) Or VarType(Data(i)) <> VarType(Data2(i)) Then
          'Uups. :-)
          Stop
        End If
      Next
      Debug.Print "100% match!"
      
      'Or empty arrays :-)
      Data = Array()
      ID = ArrayToCustomXMLPart(Data)
      Data2 = CustomXMLPartToArray(ID)
      
      Debug.Print ID
      If LBound(Data) <> LBound(Data2) Or UBound(Data) <> UBound(Data2) Then
        'Uups. :-)
        Stop
      End If
      Debug.Print "100% match!"
    End Sub
    
    Function ArrayToCustomXMLPart(ByVal Data, Optional ByVal ID As String, Optional ByVal File As Object) As String
      'Add an array to the Office file as custom XML part, returns the ID
      'If an ID is given, an existing custom XML part is used
      'Data can be a one or two dimensional array
      Dim CXP As CustomXMLPart
      Dim CXN As CustomXMLNode
      Dim i As Long, j As Long, k As Long, Size As Long, Typ As VbVarType
      Dim Value
    
      Size = Dimension(Data)
      Select Case Size
        Case Is < 0
          Err.Raise 5, "ArrayToCustomXMLPart", "Data is not an array"
        Case Is > 2
          Err.Raise 5, "ArrayToCustomXMLPart", "To many dimensions"
      End Select
    
      'Get the Active... if necessary
      If File Is Nothing Then Set File = ActiveFile
    
      On Error Resume Next
      Set CXP = File.CustomXMLParts.SelectByID(ID)
      On Error GoTo 0
      If CXP Is Nothing Then
        'Create a new one
        Set CXP = File.CustomXMLParts.Add("<Array/>")
      Else
        'Delete all existing elements
        For Each CXN In CXP.DocumentElement.ChildNodes
          CXN.Delete
        Next
        For Each CXN In CXP.DocumentElement.Attributes
          CXN.Delete
        Next
      End If
      'Return the GUID
      ArrayToCustomXMLPart = CXP.ID
    
      With CXP.DocumentElement
        'Store the properties of the array
        For i = 1 To Size
          .AppendChildNode "Boundary", NodeType:=msoCustomXMLNodeElement, NodeValue:=i
          With .LastChild
            .AppendChildNode "LBound", NodeType:=msoCustomXMLNodeAttribute, NodeValue:=LBound(Data, i)
            .AppendChildNode "UBound", NodeType:=msoCustomXMLNodeAttribute, NodeValue:=UBound(Data, i)
          End With
        Next
        'Search for values
        Select Case Size
          Case 0
            'Empty array
          Case 1
            For i = LBound(Data) To UBound(Data)
              If Not IsEmpty(Data(i)) Then
                Value = Data(i)
                GoSub StoreItem
              End If
            Next
          Case 2
            For i = LBound(Data) To UBound(Data)
              For j = LBound(Data, 2) To UBound(Data, 2)
                If Not IsEmpty(Data(i, j)) Then
                  Value = Data(i, j)
                  GoSub StoreItem
                End If
              Next
            Next
        End Select
    
        Exit Function
    StoreItem:
        'Check the data type
        Typ = VarType(Value)
        Select Case Typ
          Case VbVarType.vbBoolean:   'Okay
          Case VbVarType.vbByte:      'Okay
          Case VbVarType.vbCurrency:  'Okay
          Case VbVarType.vbDate:      'Okay
          Case VbVarType.vbDecimal:   'Okay
          Case VbVarType.vbDouble:    'Okay
          Case VbVarType.vbInteger:   'Okay
          Case VbVarType.vbLong:      'Okay
          Case VbVarType.vbSingle:    'Okay
          Case VbVarType.vbString:    'Okay
          Case VbVarType.vbVariant:   'Okay
          Case Else
            Err.Raise 458, "ArrayToCustomXMLPart", "Invalid data type"
        End Select
        'Add an item
        k = k + 1
        .AppendChildNode "Item", NodeType:=msoCustomXMLNodeElement, NodeValue:=k
        With .LastChild
          'Store the properties of the value
          .AppendChildNode "Row", NodeType:=msoCustomXMLNodeAttribute, NodeValue:=i
          If Size > 1 Then .AppendChildNode "Column", NodeType:=msoCustomXMLNodeAttribute, NodeValue:=j
          .AppendChildNode "Type", NodeType:=msoCustomXMLNodeAttribute, NodeValue:=Typ
          .AppendChildNode "Value", NodeType:=msoCustomXMLNodeAttribute, NodeValue:=StringToXML(Value)
        End With
        Return
      End With
    End Function
    
    Function CustomXMLPartToArray(ByVal ID As String, Optional ByVal File As Object) As Variant
      'Get the custom XML part from the Office file, converts and returns an array
      Dim CXP As CustomXMLPart
      Dim CXNs As CustomXMLNodes
      Dim CXN As CustomXMLNode, CAN As CustomXMLNode
      Dim Data, Value
      Dim Lb1 As Long, Ub1 As Long, Lb2 As Long, Ub2 As Long
      Dim Boundary() As Long
      Dim i As Long, j As Long, Size As Long, Typ As VbVarType
    
      'Get the Active... if necessary
      If File Is Nothing Then Set File = ActiveFile
    
      On Error Resume Next
      Set CXP = File.CustomXMLParts.SelectByID(ID)
      On Error GoTo 0
      If CXP Is Nothing Then Err.Raise 744, "CustomXMLPartToArray", "CustomXMLPart not found"
    
      'Get the properties of the array
      Set CXNs = CXP.SelectNodes("//Boundary")
      Size = CXNs.Count
      If Size = 0 Then
        'Empty array
        CustomXMLPartToArray = Array()
        Exit Function
      End If
      ReDim Boundary(1 To CXNs.Count, 1 To 2)
      For Each CXN In CXNs
        i = i + 1
        Boundary(i, 1) = CXN.SelectSingleNode("./@LBound").Text
        Boundary(i, 2) = CXN.SelectSingleNode("./@UBound").Text
      Next
      'Create it
      Select Case Size
        Case 1
          ReDim Data(Boundary(1, 1) To Boundary(1, 2))
        Case 2
          ReDim Data(Boundary(1, 1) To Boundary(1, 2), Boundary(2, 1) To Boundary(2, 2))
        Case Else
          Err.Raise 5, "CustomXMLPartToArray", "To many dimensions"
      End Select
    
      'Visit all items
      For Each CXN In CXP.SelectNodes("//Item")
        'Get the properties of the item
        'ToDo: Is SelectSingleNode faster as this FOR EACH?
        For Each CAN In CXN.Attributes
          With CAN
            Select Case .BaseName
              Case "Row": i = .NodeValue
              Case "Column": j = .NodeValue
              Case "Type": Typ = .NodeValue
              Case "Value": Value = XMLToString(.NodeValue)
            End Select
          End With
        Next
        'Convert to a VB data type
        Select Case Typ
          Case VbVarType.vbBoolean:   Value = CBool(Value)
          Case VbVarType.vbByte:      Value = CByte(Value)
          Case VbVarType.vbCurrency:  Value = CCur(Value)
          Case VbVarType.vbDate:      Value = CDate(Value)
          Case VbVarType.vbDecimal:   Value = CDec(Value)
          Case VbVarType.vbDouble:    Value = CDbl(Value)
          Case VbVarType.vbInteger:   Value = CInt(Value)
          Case VbVarType.vbLong:      Value = CLng(Value)
          Case VbVarType.vbSingle:    Value = CSng(Value)
          Case VbVarType.vbString:    'Okay
          Case VbVarType.vbVariant:   Value = CVar(Value)
          Case Else
            Err.Raise 458, "CustomXMLPartToArray", "Invalid data type"
        End Select
        Select Case Size
          Case 1
            Data(i) = Value
          Case 2
            Data(i, j) = Value
        End Select
      Next
      CustomXMLPartToArray = Data
    End Function
    
    Private Function ActiveFile(Optional ByVal App As Object) As Object
      'Get the active file of the application
      If App Is Nothing Then Set App = Application
      Select Case App.Name
        Case "Microsoft Excel"
          Set ActiveFile = CallByName(App, "ActiveWorkbook", VbGet)
        Case "Microsoft Word"
          Set ActiveFile = CallByName(App, "ActiveDocument", VbGet)
        Case "Microsoft PowerPoint"
          Set ActiveFile = CallByName(App, "ActivePresentation", VbGet)
        Case "Microsoft Publisher"
          Set ActiveFile = CallByName(App, "ActiveDocument", VbGet)
        Case Else
          Err.Raise 5, "CustomXMLPartToArray", "Unknown application"
      End Select
    End Function
    
    Private Function Dimension(Arr) As Long
      'Returns the number of dimensions of an array or 0 for an undimensioned array or -1 if no array at all.
      If IsArray(Arr) Then
        On Error GoTo Done
        Do
          Dimension = Dimension + 1
        Loop While IsNumeric(UBound(Arr, Dimension))
    Done:
        'Test for Array()
        If Dimension = 2 Then If UBound(Arr) < LBound(Arr) Then Dimension = 1
      End If
      Dimension = Dimension - 1
    End Function
    
    Private Function StringToXML(ByVal S As String) As String
      Dim Digit As String, i As Integer
      For i = 0 To 31
        If InStr(S, ChrW(i)) > 0 Then
          Digit = "&0x" & Right$("0" & Hex(i), 2) & ";"
          S = Replace(S, ChrW(i), Digit)
        End If
      Next
      StringToXML = S
    End Function
    
    Private Function XMLToString(ByVal S As String) As String
      Dim Digit As String, i As Integer
      If InStr(S, "&0x") > 0 Then
        For i = 0 To 31
          Digit = "&0x" & Right$("0" & Hex(i), 2) & ";"
          If InStr(S, Digit) > 0 Then
            S = Replace(S, Digit, ChrW(i))
            If InStr(S, "&0x") = 0 Then Exit For
          End If
        Next
      End If
      XMLToString = S
    End Function
    
    Sub DeleteAllCustomXMLParts() '(Optional ByVal File As Object)
      Dim File As Object
      'Delete all custom XML parts
      Dim CXP As CustomXMLPart
      'Get the Active... if necessary
      If File Is Nothing Then Set File = ActiveFile
      'Delete all non-built in
      For Each CXP In File.CustomXMLParts
        If Not CXP.BuiltIn Then CXP.Delete
      Next
    End Sub
    
    Private Sub Example_SelectNode()
      Dim CXP As CustomXMLPart
      Dim CXNs As CustomXMLNodes
      Dim CXN As CustomXMLNode
      Dim CAN As CustomXMLNode
      
      For Each CXP In ActiveFile.CustomXMLParts
        If Not CXP.BuiltIn Then
          If CXP.DocumentElement.BaseName = "Array" Then
            Debug.Print CXP.ID
            Exit For
          End If
        End If
      Next
      'Set CXP = ActiveDocument.CustomXMLParts.SelectByID("{F028FC16-9186-4D1C-BF95-E4E958275A51}")
      If CXP Is Nothing Then
        Debug.Print "CustomXMLPart not found"
        Exit Sub
      End If
      
      'Get all items
      Set CXNs = CXP.SelectNodes("//Item")
      'Get all rows > 1
      Set CXNs = CXP.SelectNodes("//*[@Row > 1]")
      'Get the left column   Note: Input was 'Array(1 to 3, 0 to 1)'
      Set CXNs = CXP.SelectNodes("//*[@Column = 0]")
    
      For Each CXN In CXNs
        Set CAN = CXN.SelectSingleNode("./@Value[1]")
        If CAN Is Nothing Then
          Debug.Print "Uups. Node has no 'Value' attribut?"
          Exit Sub
        Else
          Debug.Print CAN.NodeValue
        End If
      Next
    End Sub
    
    


    Friday, December 4, 2015 11:15 AM
  • a) How can I select all rows > 1 and < 3?

    b) How can I select only item 5?
    c) How can I select all items > 5?

    I got the answer from Gregory K. Maxey http://gregmaxey.mvps.org/:

      Set CXNs = CXP.SelectNodes("//*[@Row > 1 and @Row < 3]")  or since we only expect Row attributes in “Item” nodes I believe CXP.SelectNodes("//Item[@Row > 1 and @Row < 3]") would work equally well. 

      Set CXNs = CXP.SelectNodes("//Item[position()=5]")

      Set CXNs = CXP.SelectNodes("//Item[position()>5]")

    Thank you for your attention.

    Andreas.

    Sunday, December 6, 2015 6:18 AM
  • Hi Andreas,

    Late to the party here, but I would create a string of each of the items in the array with a separator (such as |) and save the string to a Document Variable.  Then to re-create the array, get the value of the Document Variable and use the Split command.


    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org

    Sunday, December 6, 2015 9:23 AM
  • I would create a string of each of the items in the array with a separator (such as |) and save the string to a Document Variable.

    Doug,

    That was my first thought too, but if you check my first post, I have a two dimensional array and most of the fields in the array are empty, a string with separators becomes complicated. Especially when the array gets large.

    Greg Maxey asks why not to use a CustomXMLPart, which has many benefits. Furthermore CustomXMLParts exists in Word, Excel and Powerpoint, so IMHO it's no question, that's the way to go.

    Theoretical it is possible to have a feature like an "invisble Excel sheet" working in the background in a CustomXMLPart.

    But I never thought that CustomXMLPart is so poorly documented. It was really a hard way to get the code above to work. (And it is still under development.)And without the help of Greg I'd never make it. The comments in his files was more helpful as the whole documentation from Microsoft.

    Anyway, thank you for your idea and that you have been dealing with my problem.

    Andreas.

    Sunday, December 6, 2015 6:02 PM