Save as XML with VBA?

Question

Is there any way to save an Excel table as XML? I have the XML Schema file... and some data in a table... and I have in Excel the Save as XML file option but can I save a file as XML from VBA? I want to automate a process and I didn;t find nothing on this option. Thanks!

Something like this? stackoverflow.com/questions/2766760/… — Siddharth Rout
– Siddharth Rout, Commented Mar 4, 2012 at 19:23
That piece of code converts the data from a table... it's ok and will be the ultimate solution but I have the XML schema file ..and I want to do everything with the options Excel already has ... I'll try to record a macro to see if there is something useful. — Andrei Ion
– Andrei Ion, Commented Mar 4, 2012 at 19:27
so... what I want is a VBA code that will load the xml scema... will do what it does when I drag/drop it over the table... than use the Save as and save it as XML — Andrei Ion
– Andrei Ion, Commented Mar 4, 2012 at 19:28

Andrei Ion · Accepted Answer · 2012-03-04 19:40:50Z

7

the good ol' macro recorder saved me this time :)) (Why didn't I used it before I posted here?) So... To load an xml schema you have:

ActiveWorkbook.XmlMaps.Add("Book2.xml", "raport").Name _
        = "raport_Map"

And to save it as xml:

ActiveWorkbook.SaveAsXMLData Filename:="Book3.xml", _
        Map:=ActiveWorkbook.XmlMaps("raport_Map")

Who would have thought that it's that easy?

answered Mar 4, 2012 at 19:40

Andrei Ion

1,83717 gold badges36 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Siddharth Rout Over a year ago

+1) Gr8 Job! It's really fun when you discover something on your own. Isn't it ;)

DrHaze Over a year ago

I wasn't aware of xml mapping before reading your answer and I want to add an useful piece of info for those in the same case as me: Creating an XML Mapping Schema in Excel 2010

Solata · Accepted Answer · 2014-03-13 10:52:18Z

0

This link helped me the most -> http://curiousmind.jlion.com/exceltotextfile

Script on link:

Sub MakeXML(iCaptionRow As Integer, iDataStartRow As Integer, sOutputFileName As String)
    Dim Q As String
    Q = Chr$(34)

    Dim sXML As String

    sXML = "<?xml version=" & Q & "1.0" & Q & " encoding=" & Q & "UTF-8" & Q & "?>"
    sXML = sXML & "<rows>"


    ''--determine count of columns
    Dim iColCount As Integer
    iColCount = 1
    While Trim$(Cells(iCaptionRow, iColCount)) > ""
        iColCount = iColCount + 1
    Wend

    Dim iRow As Integer
    iRow = iDataStartRow

    While Cells(iRow, 1) > ""
        sXML = sXML & "<row id=" & Q & iRow & Q & ">"

        For icol = 1 To iColCount - 1
           sXML = sXML & "<" & Trim$(Cells(iCaptionRow, icol)) & ">"
           sXML = sXML & Trim$(Cells(iRow, icol))
           sXML = sXML & "</" & Trim$(Cells(iCaptionRow, icol)) & ">"
        Next

        sXML = sXML & "</row>"
        iRow = iRow + 1
    Wend
    sXML = sXML & "</rows>"

    Dim nDestFile As Integer, sText As String

    ''Close any open text files
    Close

    ''Get the number of the next free text file
    nDestFile = FreeFile

    ''Write the entire file to sText
    Open sOutputFileName For Output As #nDestFile
    Print #nDestFile, sXML
    Close
End Sub

Sub test()
    MakeXML 1, 2, "C:\Users\jlynds\output2.xml"
End Sub

edited Mar 13, 2014 at 10:52

answered Mar 13, 2014 at 10:31

Solata

1,5024 gold badges29 silver badges43 bronze badges

1 Comment

Marcus Mangelsdorf Over a year ago

Unfortunately, the original link is down. But you can access a capture from 2013 here: web.archive.org/web/20130502005831/http://curiousmind.jlion.com/…

Collectives™ on Stack Overflow

Save as XML with VBA?

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related