XML Export from Excel – part 2

In Part 1, I wanted to export the data in a simple workbook as XML, so I can pick it up with XML query and documentation tools (of which more anon).

The code needs to:

    1. construct the output file name from the BU name and period (e.g. Report_Applications_4Q2009.xml)
    2. open a Scripting.TextStream on this (new) file
    3. get the complete XML string from the bureport map
    4. substitute the basic <bureport> element with the full one
    5. write the modified XML string to the TextStream.

      Items 3 and 4 are done by a simple function:

      Function XMLwithNS() As String
          Dim xml As String
          Dim res As XlXmlExportResult
          res = _
          If res = xlXmlExportSuccess Then
              XMLwithNS = Replace(xml, _
                            basicroot, fullroot, 1, 1)
              XMLwithNS = ""
          End If
      End Function

      “NS” in the function name stands for “namespace” – that is, the reference to the XML schema, discussed in Part 1. basicroot and fullroot are the before and after versions of the root element.

      Notice that XMLMap.ExportXML returns a success/failure status; the (ByRef) variable xml gets the actual XML string. The export method will fail if the mapped data is invalid with respect to the map (i.e. schema); we return an empty string in this case.

      Here’s the main routine:

      Sub ExportXMLtoFile()
          Dim bu As String, period As String
          Dim xmlfilename As String
          Dim xml As String
          bu = Range("BU").Value
          period = Range("Period").Value
          xmlfilename = basename + "_" + bu + "_" + _
                                period + ".xml"
          Dim fso As New Scripting.FileSystemObject
          Dim ts As Scripting.TextStream
          ChDir ActiveWorkbook.Path
          MsgBox "Current directory is " & CurDir
          xml = XMLwithNS
          If Len(xml) > 0 Then 'okay
              Set ts = fso.CreateTextFile(xmlfilename)
              ts.Write XMLwithNS
              MsgBox "Exported XML to file " & xmlfilename
          Else 'XML did not validate
              MsgBox "XMl does not validate; export aborted"
          End If
      End Sub

      As mentioned before, we’d probably want to run this as a batch operation from a ‘master’ workbook, which iterated over the data workbooks (maybe looking at File.DateLastModified).

      So, having now got umpteen XML data files, what can we do with them? I’ll discuss this in Part 3.


      3 Responses to “XML Export from Excel – part 2”

      1. 1 Christal December 30, 2013 at 12:00 am

        I believe everything posted made a lot of sense.
        However, what about this? what if you were to write a
        killer headline? I am not saying your content isn’t good, but what
        if you added a title that grabbed people’s attention?

        I mean XML Export from Excel – part 2 | Roy MacLean’s VBA Blog is a little vanilla.

        You could glance at Yahoo’s front page and note how they
        create article titles to grab people to open the links.

        You might try adding a video or a picture or two to grab readers interested about everything’ve written.
        Just my opinion, it would bring your posts a little bit
        more interesting.

      2. 2 alexandru lupei August 28, 2014 at 12:39 pm

        Hi Roy,
        Thank you for the example. It is pretty straightforward. 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?

      1. 1 XML Export from Excel – part 3 « Roy MacLean's VBA Blog Trackback on February 4, 2010 at 2:39 pm

      Leave a Reply

      Fill in your details below or click an icon to log in:

      WordPress.com Logo

      You are commenting using your WordPress.com account. Log Out / Change )

      Twitter picture

      You are commenting using your Twitter account. Log Out / Change )

      Facebook photo

      You are commenting using your Facebook account. Log Out / Change )

      Google+ photo

      You are commenting using your Google+ account. Log Out / Change )

      Connecting to %s

      February 2010
      M T W T F S S
      « Jan   Mar »

      %d bloggers like this: