none
Create XML file export from Excel RRS feed

  • Question

  • HI

    I have the code below that I am rrying to export to xml as part of program I'm developing- but the exported XML file keeps being changed- any idea how to prevent this?

    e.g code starting off- and apparently must be exactly this!

    <?xml version="1.0" encoding="UTF-8"?>
    <Document xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

    but what I get is

    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <ns1:Document xmlns:ns1="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

    so iit adds the standalone = Yes bit and puts ns1: on every line!

    Other that find replace any ideas?

    Thanks

    David

    Wednesday, March 2, 2016 6:33 PM

Answers

  • Hi, David_1234

    According to your description, as far as I know that You export XML data by exporting the contents of mapped cells on the worksheet. When you export data, Excel applies the following rules to determine what data to save and how to save it:

    1. Empty items are not created when blank cells exist for an optional element, but empty items are created when blank cells exist for a required element.

    2. Unicode Transformation Format-8 (UTF-8) encoding is used to write the data.

    3. All namespaces are defined in the Root XML element.

    4. Excel overwrites existing namespace prefixes. The default namespace is assigned a prefix of ns0. Successive namespaces are designated ns1, ns2 to ns<count> where <count> is the number of namespaces written to the XML file.

    5. Comment nodes are not preserved.

    This workaround is that you could export a table in Excel into an XML format. You have to do is select the table range, then run the ExportRangeToXML procedure. You'll be prompted for a filename and location to save the XML file. You can customise the XML Element names used in the XML structure for the table and rows. The table headers are used for Element names in each row of data.

    Sub ExportRangeToXML()
     
       Dim strXML As String
       Dim varTable As Variant
       Dim intRow As Integer
       Dim intCol As Integer
       Dim intFileNum As Integer
       Dim strFilePath As String
       Dim strRowElementName As String
       Dim strTableElementName As String
       Dim varColumnHeaders As Variant
     
        'Set custom names
        strTableElementName = "Table"
        strRowElementName = "Row"
     
        'Set file path
        strFilePath = Application.GetSaveAsFilename(, "(*.xml),*.xml", , "Save As...")
        If strFilePath = vbNullString Then Exit Sub
     
        'Get table data
        varTable = Selection.Value
        varColumnHeaders = Selection.Rows(1).Value
     
        'Build xml
        strXML = "<?xml version=""1.0"" encoding=""utf-8""?>"
        strXML = strXML & "<" & strTableElementName & ">"
        For intRow = 2 To UBound(varTable, 1)
            strXML = strXML & "<" & strRowElementName & ">"
            For intCol = 1 To UBound(varTable, 2)
                strXML = strXML & "<" & varColumnHeaders(1, intCol) & ">" & _
                    varTable(intRow, intCol) & "</" & varColumnHeaders(1, intCol) & ">"
            Next
            strXML = strXML & "</" & strRowElementName & ">"
        Next
        strXML = strXML & "</" & strTableElementName & ">"
     
        'Get next file number
        intFileNum = FreeFile
     
        'Open the file, write output, then close file
        Open strFilePath For Output As #intFileNum
        Print #intFileNum, strXML
        Close #intFileNum
     
    End Sub

    In addition if you have any feedbacks for Excel, please feel free to submit them to User Voice:

    https://excel.uservoice.com/

    Thanks for your understanding.

    • Marked as answer by David_JunFeng Sunday, March 13, 2016 1:13 PM
    Friday, March 4, 2016 1:30 AM