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 = _
            ActiveWorkbook.XmlMaps(mapname).ExportXml(xml)
          If res = xlXmlExportSuccess Then
              XMLwithNS = Replace(xml, _
                            basicroot, fullroot, 1, 1)
          Else
              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
              ts.Close
              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.

      Advertisements

      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 »
      1234567
      891011121314
      15161718192021
      22232425262728

      %d bloggers like this: