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...
- Export measured results, serial number, time, and date to Excel document. In this example I am only writing to fields shown in yellow.
- 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>
- 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.
- 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.
- 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.