Excel/CSV Output Using Only Dimensions That Are Actually Executed (and Set to Output)

**Schrocknroll** · 03-30-2019, 08:34 AM

It may be tough to help without seeing the script. Could you share it, or is it considered proprietary?

**LostL** · 03-31-2019, 05:58 PM

I personally handle that by altering the output based on a variable that controls which sections of the program that gets executed. But that depends on the design of the programs of course.

**mb0258** · 04-01-2019, 10:07 AM

I report with exce csv everytime, output depends on operation to measure selected, but you need to put dimension tolerancing together with measuring routine blocks.

if op10 go blockA (measure A, evaluate A),
if op20 go blockB (measure B, evaluate B), etc

another thing, in the excel output there is a switch for output options (both, report, stats, none, output all regardless of these.), isnt it ?

**Ego Murphy** · 04-01-2019, 01:35 PM

Originally posted by edrake View Post

Our last Quality Manager whipped up a handy little script that outputs a CSV file of the measurement results for a part. Unfortunately, the programs he developed it around were fairly simple, and we now want to use this output on all parts.

He was able to build it so that it would ignore any dimension commands that had an output of 'NONE' (which PCD2EXCEL apparently does not?). My problem arises with other more complex programs, that measure/don't measure various features based on which Operation is being inspected/in-process vs final dimensions/optional Operator input.

For instance, for one part (Airfoil) there is a Dovetail/General Features inspection for Op 45 (Dovetail only), Op 65 (additional Root features), and Op 105 (Final Inspection including both). The way our script currently runs, whichever Operation is run, the PDF report has all of the proper dimensions for that Op, but the CSV output has them all in tandem, and the dimensions for the OPs that didn't actually run will often be out of tolerance (especially scanned features).

The main thing they are trying to use this for is to compile a report of part serial #'s that are in tolerance, and ready to ship to customer/outside services (but we are developing it into other uses). Having these unmeasured dimensions included is obviously putting a kink in efforts to use the CSV files to look for blueprint parts.

Is there another way to do this? I could swear CSV output was standard when I first used PC-DMIS about 20 years ago (took a break for about 13 years), why would they get rid of it? We are currently running 2011 MR1 (X4), 2013 MR1 (X3), and 2017 R2 (X1). Essentially, I just want to put out the same report that I'm printing to PDF in CSV/Excel format.

Any ideas? Thanks in advance!

FYI this kinda belongs in the Code Samples sub-forum but I ain't no mod so let's do this.

The single driving reason behind scripting CSV output is to Format the CMM's output data so that it is extremely useful for SPC, charting, graphing, tracking, automated reporting, etc., etc. as opposed to PC-DMIS's standard PDF reports that are only useful for the rare humans who can read CMM reports.

There are two aspects to this formatting, and the first one is native to CSV/Excel: you get numerical text data in an electronic file that nearly anything can read.
The second one is the customization: rows, columns, headers, names, units, date-time stamps, you can arrange the data anyway you like.
For example, Minitab SPC software wants one part to be only three rows: headers, nominal, actuals. No problem for a CSV script.

If the first is more important than the second, PC-DMIS's Excel reporting function (under the File Printing in newer versions, not that ancient clunky PCD2EXCEL wizard) works quite well. You can even toggle it to write straight CSV instead of XLSX file extension, and just like the PDF Report if something didn't get executed it's not in the report.

It does lack the precise formatting that a CSV script has. If your current CSV script arranges the data just right for some other system to ingest, then you have to keep it and modify as needed - or even replace it with a completely different scripting approach in order to meet the need of "don't report skipped stuff" - which really is a topic for the Code Sample section.

Keep in mind this also: if precise formatting is required, it may be a better use of time to just use the PC-DMIS Excel printing and then write an Excel macro in a master spreadsheet to collect, collate, and auto-format the raw data from the printed Excel files. This would make it easy to have the bad data thrown out, IF Operation=65 then skip Dimension 42B etc.

**AndersI** · 04-02-2019, 10:54 AM

In theory you would only replace a line (in the script) looking more or less like

Code:

Set DmisCommands = DmisPart.Commands

with

Code:

Set DmisCommands = DmisPart.ExecutedCommands

but at least some PC-DMIS versions seems to have trouble with .ExecutedCommands.

Without .ExecutedCommands your script will *definitely* have problems as soon as your program uses LOOP, SUBROUTINE, IF, etc. - anything repeating or conditioinally executing parts of the program is not reflected in .Commands, only in .ExecutedCommands.

As an alternative you can try adding a test for DmisCommand.Skipped at the same place where you test DmisCommand.OutputMode (NONE, STAT, PRINT, BOTH) to decide if it should be printed or not. I have not tested this approach (at least I don't remember testing it), I've only seen that the .Skipped field exists… It would still be problematic with LOOP and other repeating constructs.

**edrake** · 04-05-2019, 06:51 PM

Originally posted by Ego Murphy View Post

FYI this kinda belongs in the Code Samples sub-forum but I ain't no mod so let's do this.

Thanks, I considered that, but am also interested in non-script, PC-DMIS native options (like PCD2EXCEL.exe, or native print commands, depending on version), and not sure if the issue is with the script, or the program, or the PC-DMIS version (we have at least three flavors running among 8 CMM's)...

But anyway, the formatting is not so important, as long as it is consistent. We are developing various applications that would utilize the CSV files, so as long as it came out the same way every time, and only supplied the data for things that were executed, it would be swell.

**edrake** · 04-05-2019, 07:26 PM

Here's the script code, as currently running.

Code:

Sub Main()

   Dim App As Object
   Set App = CreateObject("PCDLRN.Application")

   Dim Part As Object
   Set Part = App.ActivePartProgram

   Dim Cmds As Object
   Set Cmds = Part.Commands

   Dim Cmd As Object
   Dim DCmd As Object
   Dim DcmdID As Object
   Dim TField As Object
   Dim fs As Object
   Dim Out_path As Object
   Dim DimID As String
   Dim ReportDim As String
   Dim CheckDim As String
   Dim DimCallout As String
   Dim DimTXT As String

   Dim FileNumb As Integer

   Set Out_path = Part.GetVariableValue ("OUT_PATH")
   DimTXT = Out_path.StringValue & "_DIMS.CSV"
   FileNumb = FreeFile
   Open DimTXT For Output As #FileNumb

   For Each Cmd In Cmds
      ' Check command Type
      If Cmd.Type <> 1299 Then
         If Cmd.IsTraceField Then
            Set TField = Cmd.TraceFieldCommand
            Write #FileNumb, TField.Name, TField.Value
         End If
      End If
   Next Cmd

   Write #FileNumb, "ID", "Nominal", "Plus Tol", "Minus Tol", "Actual", "Out-of-Tol"

   For Each Cmd In Cmds
      If Cmd.Type <> 1299 Then
         If Cmd.IsDimension Then
            If Cmd.Type = DIMENSION_START_LOCATION Or Cmd.Type = DIMENSION_TRUE_START_POSITION Then
               Set DcmdID = Cmd.DimensionCommand
               DimID = DcmdID.ID
               ReportDim = Cmd.GetText(OUTPUT_TYPE, 0)
            End If

            If Cmd.Type <> DIMENSION_START_LOCATION And Cmd.Type <> DIMENSION_END_LOCATION And Cmd.Type <> DIMENSION_TRUE_START_POSITION And Cmd.Type <> DIMENSION_TRUE_END_POSITION Then
               Set DCmd = Cmd.DimensionCommand
               CheckDim = Cmd.GetText(OUTPUT_TYPE, 0)

               If CheckDim <> "" Then
                  ReportDim = CheckDim
               End If

               If ReportDim = "BOTH" Or ReportDim = "REPORT" Then
                  If DCmd.ID = "" Then
                     If DCmd.AxisLetter <> "TP" Then
                        Print #FileNumb, Chr(34), DimID & "." & DCmd.AxisLetter, Chr(34), ",", Round(DCmd.Nominal, 4), ",", DCmd.Plus, ",", DCmd.Minus, ",", Round(DCmd.Measured, 4), ",", Round(DCmd.OutTol, 4)
                     Else
                        Print #FileNumb, Chr(34), DimID & "." & DCmd.AxisLetter, Chr(34), ",", Round(DCmd.Nominal, 4), ",", DCmd.Plus, ",", DCmd.Minus, ",", Round(DCmd.Deviation, 4), ",", Round(DCmd.OutTol, 4)
                     End If
                  Else
                     If DCmd.AxisLetter <> "TP" Then
                        Print #FileNumb, Chr(34), DCmd.ID, Chr(34), ",", Round(DCmd.Nominal, 4), ",", DCmd.Plus, ",", DCmd.Minus, ",", Round(DCmd.Measured, 4), ",", Round(DCmd.OutTol, 4)
                     Else
                        Print #FileNumb, Chr(34), DCmd.ID, Chr(34), ",", Round(DCmd.Nominal, 4), ",", DCmd.Plus, ",", DCmd.Minus, ",", Round(DCmd.Deviation, 4), ",", Round(DCmd.OutTol, 4)
                     End If
                  End If

                  'Add Min/Max For Profile dimensions
                  If Cmd.Type = 1118 Or Cmd.Type = 1105 Then

                     Print #FileNumb, Chr(34), DCmd.ID & ".Max", Chr$(34), ",", Round(DCmd.Nominal, 4), ",", DCmd.Plus, ",", DCmd.Minus, ",", Round(DCmd.Max, 4), ",", Round(DCmd.OutTol, 4)
                     Print #FileNumb, Chr(34), DCmd.ID & ".Min", Chr$(34), ",", Round(DCmd.Nominal, 4), ",", DCmd.Plus, ",", DCmd.Minus, ",", Round(DCmd.Min, 4), ",", Round(DCmd.OutTol, 4)

                  End If

               End If
            End If
         End If

         'Do GDT
         If Cmd.Type = 184 Then
            ReportDim = Cmd.GetText(OUTPUT_TYPE, 0)
            If ReportDim = "BOTH" Or ReportDim = "REPORT" Then
               DimCallout = Cmd.GetText(LINE1_CALLOUT, 1)
               If DimCallout <> "" Then
                  Print #FileNumb, Chr(34), Cmd.GetText(ID, 0), Chr(34), ",", Round(Cmd.GetText(LINE1_NOMINAL, 1), 4), ",", Cmd.GetText(LINE1_PLUSTOL, 1), ",", Cmd.GetText(LINE1_MINUSTOL, 1), ",", Round(Cmd.GetText(LINE1_DEV, 1), 4), ",", Round(Cmd.GetText(LINE1_OUTTOL, 1), 4)
               End If
               Print #FileNumb, Chr(34), Cmd.GetText(ID, 0), Chr(34), ",", Round(Cmd.GetText(LINE2_NOMINAL, 1), 4), ",", Cmd.GetText(LINE2_PLUSTOL, 1), ",", Cmd.GetText(LINE2_MINUSTOL, 1), ",", Round(Cmd.GetText(LINE2_DEV, 1), 4), ",", Round(Cmd.GetText(LINE2_OUTTOL, 1), 4)

            End If
         End If
      End If

   Next Cmd

   Close #FileNum

End Sub

Function Round(Value, Dec) As Double
   Dim TmpVal As Long
   TmpVal = Value * (10 ^ Dec)

   Round = TmpVal / (10 ^ Dec)

End Function

I have also tried various ways to trick either the script, or PC-DMIS to give the output desired. Using flow control, I was able to get the CSV results how I need them by first running through all of the dimension commands and forcing every dimension output to 'NONE', and every REPT comment to '$$' (skipping over the flow control that only executes if it is the correct OP), and then starting again, following that flow control to force only the executed portion to 'REPT' for comment and 'BOTH' for output for only that correct OP.

This gave me a CSV that only showed the stuff executed in that operation, for each OP. Unfortunately, it also doubled the PDF report (just reports everything twice, even though only executed once with output)

Excel/CSV Output Using Only Dimensions That Are Actually Executed (and Set to Output)

Excel/CSV Output Using Only Dimensions That Are Actually Executed (and Set to Output)

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Related Topics