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

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

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

    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!

  • #2
    It may be tough to help without seeing the script. Could you share it, or is it considered proprietary?
    PC-DMIS 2016.0 SP8

    Jeff

    Comment


    • edrake
      edrake commented
      Editing a comment
      Pretty sure I'm safe to share it, will post below.

  • #3
    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.

    Comment


    • edrake
      edrake commented
      Editing a comment
      Thanks, that is what I was doing, but this script outputs every dimension with a 'non-none' output, whether or not it was executed. The reporting has always been fine, until we started trying to use this CSV script.

    • LostL
      LostL commented
      Editing a comment
      I was not talking about the reporting but the output of the script. My script reads the variables in the program and filters which dimensions to output in various forms based on that.

  • #4
    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 ?

    Comment


    • edrake
      edrake commented
      Editing a comment
      Which version are you referring to? I'm currently developing everything in 2011 MR1, as it our lowest common denominator version-wise. We are considering updating the SMA on the two CMM's that are limited to 2011 MR1, which would put them all at 2013 MR1, or better.

    • mb0258
      mb0258 commented
      Editing a comment
      version is 2016 sp10

  • #5
    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.

    Comment


    • #6
      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.
      AndersI
      SW support - Hexagon Metrology Nordic AB

      Comment


      • edrake
        edrake commented
        Editing a comment
        Originally posted by AndersI View Post
        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
        This is encouraging! I'm still getting my feet wet with scripting and all, but I'm now looking into the PCDMIS BASIC Help file and reading on '.ExecutedCommands' and see if I can make sense of how to apply it to this script. I will post the script code below if you'd like to see if you can parse his coding (script was written by a guy who is not familiar with PC-DMIS, and likes it that way...)

    • #7
      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.

      Comment


      • #8
        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)

        Comment


        • AndersI
          AndersI commented
          Editing a comment
          Did you try .ExecutedCommands instead of .Commands on line 10?

        • edrake
          edrake commented
          Editing a comment
          Yes, and it worked perfectly!

          My apologies, I was away since Friday, and just saw your reply this morning. I would have replied then but I ran into another minor quirk, where wrong tolerances were being used, but that was all me (Tolerance assigned depending on an OP# Variable). Got that bit fixed, and it seems to exactly what we needed! This is also encouraging me to delve deeper into the scripting/VB side of things...

          My many thanks to you, AndersI!

      Related Topics

      Collapse

      Working...
      X