ProfessionalExcel.com In the office, Excel is a way of life… not just a tool

24Apr/149

Export Excel table range to XML using VBA

Today I needed to export a table in Excel into an XML format, and here's the code I whipped up to do it.

All 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.

'*****************************************************
' Purpose:   Exports a selected range (table) to an xml
'            format using table headers.
' Notes:     Requires entry of file name. Uses table headers
'            as Element names for each row.
'
' Developer:        Date:       Description:
' Chris Read        24/04/14    Created.
'*****************************************************
Public 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
Comments (9) Trackbacks (0)
  1. Thanks! Very helpful article.

  2. Hello,

    I found this really helpful, nevertheless I need to do slightly different process in my case.

    How should I modify the code to bring one XML file for each row in the table range? separate file for each, only one row of data ini them. I have been playing a bit, but not successful.

    In addion, how should I amend the save code to avoid the user needing to confirm each file save?

    How to define sublevels?

    Thank you in advance for any support, just started in this XML world….

    JL

    • >> How should I modify the code to bring one XML file for each row in the table range?
      You need to move the For loop for each row so that it encloses the code above (move to before setting file name).

      >> In addion, how should I amend the save code to avoid the user needing to confirm each file save?
      Answer: Set the filename in the code (instead of user prompt) using row data (when it’s enclosed within the row For loop).

      >> How to define sublevels?
      Answer: No idea what you mean by sub levels.

      • Thanks,

        Would it be possible to get sample coding?

        About the sublevels, I mean when for example it goes like:

        As said, little newbie….

  3. Hey Read,

    Awesome Coding but I don’t understand one part

    ‘Get next file number
    intFileNum = FreeFile

    What does it mean ???

  4. Plus one dude!

    This was helpful, great post!

  5. Hello,
    Thank you for the example.
    I have a question regarding xml mapping.
    I don’t want to export the xml I create. I just want to map the sheet in excel to an xml automatically.
    Can you please point me to a function which does that?


Leave a comment

No trackbacks yet.