Excel - XML mapping.

**Bunta** · 10-10-2013, 03:45 AM

Hi all.

I have a similar issue to a question that can be found in this thread, and it is the closest to answer I've been able to find through using google.

I have the same issue in that I have created my XSD file and pulled it in to Excel. When I manually drag it on to the cell I want it applies the XSD across all columns and rows and I can then export the XML. The problem is a I have no idea how to use this in a macro. I 've looked at the thread above and used the solution and googled ListColumn.XPath.SetValue syntax which the thread opener used as solution but I just can't get it working.

Is someone able to very kindly just show me what the syntax should look like if I'm applying to sheet A Cell B1 and then I can adapt it to use on all sheets in the workbook.

thanks very much.

**Bunta** · 10-10-2013, 04:29 AM

EDIT: I'm slowly getting there. I have corrected the last error, the name was not the same as liseted in the maps view and needed to CAM002_Map. Now when I run it I get The Xpath is not valid because either the Xpath syntax is incorrect or is not supported by Excel. It does turn the table into XML though, but as I need this to be an automated process to output a ot of Excel to XML I can't be cending the code. Can anyone help? It lists this part of the code as being the error in the debug.

column.ListColumns(1).XPath.SetValue map, strXPath

I've created something, placed it below, however It get a Runtime Error '9' Subscript out of range when I run it and it highlights

Set map = ActiveWorkbook.XmlMaps("CAM002Accomm")

as being the error when I debug it.

Sub CreateXMLList()
    Dim map As XmlMap
    Dim strXPath As String
    Dim column As ListObject

    ' Specify the schema map to use.
    Set map = ActiveWorkbook.XmlMaps("CAM002Accomm")
     
    ' Create a new list.
    Set column = ActiveSheet.ListObjects.Add
         
    ' Specify the first element to map.
    strXPath = "/CAM002Accomm/Row/Record_Number"
    ' Map the element.
    column.ListColumns(1).XPath.SetValue map, strXPath

    ' Specify the element to map.
    strXPath = "/CAM002Accomm/Row/LPI"
    column.ListColumns(2).XPath.SetValue map, strXPath
    
        ' Specify the element to map.
    strXPath = "/CAM002Accomm/Row/AccDate"
    column.ListColumns(3).XPath.SetValue map, strXPath
    
        ' Specify the element to map.
    strXPath = "/CAM002Accomm/Row/AccStatus"
    column.ListColumns(4).XPath.SetValue map, strXPath

End Sub

**Bunta** · 10-10-2013, 05:57 AM

In case anyone is interested I have the final solution and I have posted it below along with a screenshot of the XML source view in my spreadsheet so if any one has the same issue they can see where the names came from. The issue was I had the names incorrect in the Xpath. The can found in the XML source picture. Just needs to be updated to the names others are using:

Sub CreateXMLList()
    Dim map As XmlMap
    Dim strXPath As String
    Dim column As ListObject

    ' Specify the schema map to use.
    Set map = ActiveWorkbook.XmlMaps("CAM002Accomm_Map")
     
    ' Create a new list.
    Set column = ActiveSheet.ListObjects.Add
         
    ' Specify the first element to map.
    strXPath = "/CAM002Accomm/Row/Record_Number"
    ' Map the element.
    column.ListColumns(1).XPath.SetValue map, strXPath

    ' Specify the element to map.
    strXPath = "/CAM002Accomm/Row/LPI"
    column.ListColumns(2).XPath.SetValue map, strXPath
    
        ' Specify the element to map.
    strXPath = "/CAM002Accomm/Row/AccDate"
    column.ListColumns(3).XPath.SetValue map, strXPath
    
        ' Specify the element to map.
    strXPath = "/CAM002Accomm/Row/AccStatus"
    column.ListColumns(4).XPath.SetValue map, strXPath

End Sub

XML source.png