XML Report output

Post Reply
User avatar
CrashN8
Site Admin
Site Admin
Posts: 371
Joined: Thu Feb 09, 2017 4:07 pm

XML Report output

Post by CrashN8 » Fri Mar 22, 2019 12:42 pm

Using Excel it is possible to convert report data saved in Excel format into XML format with user specified schema / layout for XML Elements, Attributes, or so-called <tags>

You must have Microsoft Excel installed, saving report data into Excel format using DDE is recommended although I suspect if you're clever you could use text file or default Excel output as Data Source. In this example I will output (by DDE) the Serial Number, Date, Time, and Actual Report results to an Excel sheet. I suggest creating CMM-Manager program and outputting report data with DDE using overwrite option. Once you run the program once and have a spreadsheet with measured data then we can prepare the spreadsheet for XML conversion task. This is done by using XML Source button in the Excel Developer ribbon tab. Details for each step are shown below...
  1. Export measured results, serial number, time, and date to Excel document. In this example I am only writing to fields shown in yellow.

    XML_DDE_out.jpg

  2. Now we need to import an XML Schema (map of sorts) into Excel. This Schema defines the default layout of desired XML output. Create this Schema file in Notepad and save with .XML extension, then load into Excel spreadsheet by clicking the [XML Maps...] button in the XML Source panel as shown below. I found detailed instructions for XML Schema mapping in Excel here - https://spreadsheeto.com/xml/

    Example XML Schema contents:

    Code: Select all

    <?xml version="1.0" encoding="UTF-8"?>
    <InspectionData>
      
    <equipment name="Nikon VMA">
        <location>Nikon Booth at Automate</location>
    </equipment>
    
    <part name="TestPart001">
        <partNumber>TP001</partNumber>
        <serial>1234-5678</serial>
        <date>
          <year>2019</year>
          <month>1</month>
          <day>31</day>
        </date>
    
        <time>
          <hour>18</hour>
          <minute>30</minute>
          <second>21</second>
        </time>
        <passfail>Fail</passfail>
    
      <feature name="circle 1-1">
        <control name="Diameter" MetricName="Test Diameter">
        <units>millimeters</units>
        <nominal>9.001</nominal>
        <measured>8.876</measured>
        <upperTolerance>2</upperTolerance>
        <lowerTolerance>-1</lowerTolerance>
        <deviation>-0.125</deviation>
        <Test>Pass</Test>
        </control>
      </feature>
    
      <feature name="circle 2-1">
        <control name="Diameter" MetricName="Test Diameter">
        <units>millimeters</units>
        <nominal>10.001</nominal>
        <measured>9.876</measured>
        <upperTolerance>2</upperTolerance>
        <lowerTolerance>-1</lowerTolerance>
        <deviation>-0.125</deviation>
        <Test>Pass</Test>
        </control>
      </feature>
    
    </part>
    
    </InspectionData>
  3. Now we associate each Map item with a column or cell in spreadsheet. Note items only output to XML once will be mapped to a single cell while items like Feature Naming, measured data, etc. that are repeated will be mapped to a vertical column.

    XML_Mapping.jpg

  4. Test the Schema Mapping by clicking the Export button in Developer tab>XML>Export from Excel. If the Schema map is correct then Excel will prompt user for manual File SaveAs for new XML file. Open the file and confirm contents.

  5. Finally add a step at end of CMM-Manager program that asks Excel to Save a copy of XLS file in XML format based on XML Schema file. This is a short VB Script that can be re-used on any CMM-Manager program where XML output is required. Note the unique file path for XLS, output XML, and Schema XML files.

    Code: Select all

    set OExcel=CreateObject("Excel.Application")
    OExcel.visible = False
    OExcel.Workbooks.Open("C:\Users\Public\Excel 2 XML\Squeak XML Test_2.xlsx")
    set wb = OExcel.Workbooks.Open("C:\Users\Public\Excel 2 XML\Squeak XML Test_2.xlsx")
    set objMapToExport = wb.XmlMaps("InspectionData_Map")
    wb.SaveAsXMLData "C:\Users\Public\Excel 2 XML\converted_XML_from_CMMM.xml", objMapToExport 
    OExcel.Quit
    


See attached example CMM-Manager Program and all associated Excel, XML, etc. files.
You do not have the required permissions to view the files attached to this post.

User avatar
CrashN8
Site Admin
Site Admin
Posts: 371
Joined: Thu Feb 09, 2017 4:07 pm

Re: XML Report output

Post by CrashN8 » Mon Mar 25, 2019 3:08 pm

It was brought to my attention that example uses file paths that are relative to my Desktop... You can easily resolve this by replacing path with something more universal such as C:\users\Public... I will update example files as time allows.

Edit - Files updated - just copy contents of ZIP file to C:\Users\Public\Excel 2 XML and run the CMM-Manager program.

Post Reply