locked
Map XML elements with VBA RRS feed

  • Question

  • Hi,

    Does anyone know any VBA code that can map XML elements to a spreadsheet.
    This is what I have so far

    Sub XML()
    Dim mMap As XmlMap
    Dim i As String
    Dim n As XPath
    Set mMap = ActiveWorkbook.XmlMaps("PartList_Map")
    i = "/PartList/Part/PartID"
    Range("A1").XPath.SetValue mMap, i
    End Sub

    There are other elements within the "part" folder. Is there anyway to map all of the elements in the folder to different cells, without having to give the full name of the element as I have done with "PartID". Could I use "/Partlist/Part/*" or something? Think I may need to use the XMLDOM thing, but I don't know how.

    Thanks for anything you can help with,

    Charlie

    Wednesday, December 14, 2005 1:57 PM

Answers

  • Per our support engineer:

    As our partner want a way to set range’s XPath with some nodes. But it is not supported in Excel 2003. 

    He needs to implement it himself. To do this, using XMLDocument is a simple way. But the data in a schema is complex. It is hard to enum the data in an xml file and map it to the Excel cells.

    To view details of XMLDocument,

    http://msdn.microsoft.com/xml/

     

    The sample code shows how to load the schema using a XML document.

        Dim map1 As XmlMap

        Set map1 = ActiveWorkbook.XmlMaps.Add("C:\Documents and Settings\tqchen\Desktop\1.xml", _

            "Root")

        map1.Name = "Root_Map"

       

        Dim r1 As Range

        Dim xp As XPath

        Set r1 = ActiveSheet.Range("A1")

        Set xp = r1.XPath

        xp.SetValue map1, "/Root/Row/Column1", , True

        Dim o As DOMDocument

        Set o = New DOMDocument

        o.loadXML map1.Schemas.Item(1).XML

     

    You can operate the data in the XML Document, the complexity is based on your xml file.

    I hope it can help you. Best Regards, Tian Qiang

     

    -brenda (ISV Buddy Team)

    Wednesday, December 21, 2005 5:54 PM