hexagon logo
Register Sign in
  • Replies 16 replies
  • Subscribers 33 subscribers
  • Views 250 views
  • Users 0 members are here
Options
Related

Script for automated csv output

Rookie
Writing a script to export the feature nominals and actuals to a .csv file. The file will be read back in later in the program to populate the nominal and actuals for Generic points to report from. The .csv file will be serialized for each part so that the data will be retrievable for a partial re-run of reworked surfaces. Instead of renspecting the whole part, the csv file will contain all of the previous data for repopulating the generic points.

Having great success up to a point. I have the feature ID's in the csv file but I cannot figure out the right code to write the nominals and actuals into the file. This is a work in progress and some of the lines are commented out. I want to start with nominal X in the B column of the file and end with the actual K vectors in the N column. Just can't figure out the right code to start writing the hit data. Everything else is working great. 

'Declares the File System Object And Instantiates it
Dim  objFSO
Set  objFSO = CreateObject("Scripting.FileSystemObject")

' PC-DMIS APPLICATION DECLARATIONS
Dim  PCDApp As Object
Set PCDApp = CreateObject("PCDLRN.Application")
Dim  PRG As Object
Set  PRG = PCDApp.ActivePartProgram
Dim Cmds As Object
Set Cmds = PRG.Commands
Dim Cmd As Object
Dim DCmd As Object
Dim  CCmd As Object
Dim   DCmdID As Object

'VARIABLES FROM THE PART PROGRAM For FILE SAVE And EXCEL FILE HEADER
Dim VPART As Object 
Set VPART = PRG.GetVariableValue("VPART")

Dim VDESCRIPTION As Object
Set VDESCRIPTION = PRG.GetVariableValue("VDESCRIPTION")

Dim VSERIALNUMBER As Object
Set VSERIALNUMBER = PRG.GetVariableValue("VSERIALNUMBER")

Dim  VSRLFLDRPATH As Object
Set  VSRLFDRPATH = PRG.GetVariableValue("VSRLFLDRPATH")


'EXCEL APPLICATION DECLARATIONS
Dim Xcl As Object
Set Xcl = CreateObject("Excel.Application") 
Dim Xclwrkbks As Object
Dim  Xclwrkbk As Object
Dim Xclsht  As Object
Dim  count As Integer

Set  Xclwrkbks = Xcl.Workbooks
Set  Xclwrkbk = Xcl.Workbooks.Open("D:\CMM_PROGRAMS\PC-DMIS_SUPPORT_FILES\EXCEL_FILES\TEMPLATE.csv")
Set  Xclsht = Xclwrkbk.Worksheets("TEMPLATE")


'EXCEL SHEET HEADER
Xclsht.Range("A2").Value = "Part Number"
Xclsht.Range("A3").Value = "Description"
Xclsht.Range("A4").Value = "Serial #"
Xclsht.Range("B2").Value = VPART.StringValue
Xclsht.Range("B3").Value = VDESCRIPTION.StringValue
Xclsht.Range("B4").Value = VSERIALNUMBER.StringValue
count = 7

Xclsht.Range("A7").Value = "FeatureName"
Xclsht.Range("B7").Value = "Nominal X"
Xclsht.Range("C7").Value = "Nominal Y"
Xclsht.Range("D7").Value = "Nominal Z"
Xclsht.Range("E7").Value = "Nominal I"
Xclsht.Range("F7").Value = "Nominal J"
Xclsht.Range("G7").Value = "Nominal K"

Xclsht.Range("I7").Value = "Measured X"
Xclsht.Range("J7").Value = "Measured Y"
Xclsht.Range("K7").Value = "Measured Z"
Xclsht.Range("L7").Value = "Measured I"
Xclsht.Range("M7").Value = "Measured J"
Xclsht.Range("N7").Value = "Measured K"


Dim FCmd As Object

   For Each Cmd In Cmds

    If Cmd.IsFeature Then

    Dim  Fcntr As Integer
    
    count = count + 1

    Set FCmd = Cmd.FeatureCommand
    FeatureList(Fcntr) = FCmd.ID 
    Fcntr = Fcntr + 1 
Set  PRBHIT = CreateObject("PCDLRN.PointData")

   'Set PRBHIT = FCmd.GetHit (Fcntr, FHITDATA_CENTROID, FDATA_THEO, FDATA_PART, AlignID, PLANE_TOP)
   'Set PRBHIT = FCmd.GetHit (Fcntr, FHITDATA_CENTROID, FDATA_MEAS, FDATA_PART, AlignID, PLANE_TOP)
   'Set PRBHIT = FCmd.GetHit (Fcntr, FHITDATA_VECTOR, FDATA_THEO, FDATA_PART, AlignID, PLANE_TOP)


     Xclsht.Range("A" & count).Value = FCmd.ID

'Script is correct up To this point
     Xclsht.Range("B" & count).Value = FCmd.TX


Xcl.Visible=True

End If

Next
  • Can you clarify - are you wanting to get each touch point from every feature, or the measured location of the centroid of the computed feature (what prints out on the report).
  • I know it isn't as much fun by why not use the PCD2Xcel wizard? That will take care of writing everything out and then you just need to read it back in. If you don't like that output for some reason you can look at the source code for it in the PCDMIS install directory and can borrow the relevant chunks of code for your script or just edit the source code to reformat the output how you want it.
  • Can you clarify - are you wanting to get each touch point from every feature, or the measured location of the centroid of the computed feature (what prints out on the report).

    All of the features are vector points. Just want the Nominal and Actual XYZ,IJK from each point. DaSalo had a good suggestion about looking at the source code. Only catch is that I need this totally automated for operator safety lol.
  • You need to uncomment the GetHit parts or there will be no values to work with.

    Try changing: 
    Xclsht.Range("B" & count).Value = FCmd.TX


    to: 
    Set PRBHIT = FCmd.GetHit (Fcntr, FHITDATA_CENTROID, [B]FDATA_THEO[/B], FDATA_PART, AlignID, PLANE_TOP)
Xclsht.Range("B" & count).Value = Cdbl(PRBHIT.X) ' = THEO_X

Set PRBHIT = FCmd.GetHit (Fcntr, FHITDATA_CENTROID, [B]FDATA_MEAS[/B], FDATA_PART, AlignID, PLANE_TOP)
Xclsht.Range("G" & count).Value = Cdbl(PRBHIT.X) ' = MEAS_X

Set PRBHIT = FCmd.GetHit (Fcntr, [B]FHITDATA_VECTOR[/B], FDATA_THEO, FDATA_PART, AlignID, PLANE_TOP)
Xclsht.Range("N" & count).Value = Cdbl(PRBHIT.X) ' THEO_I


    The bolded parts decide what value you will end up with in the cell.
  • All of the features are vector points. Just want the Nominal and Actual XYZ,IJK from each point. DaSalo had a good suggestion about looking at the source code. Only catch is that I need this totally automated for operator safety lol.

    What do you mean? PCD2XL is totally automated. I use it to output a .csv for every program we run. Completely stable, completely automatic, handles every type of dimension, etc. To make it completely automatic and to have control over the file name and location you must do the following:
    1) Call the PCD2XL executable using the external executable function. This will place a command to call the .exe in the edit window.
    2) Type an "A" after the path to the executable. This switch puts it in auto mode. I believe Anders was responsible for this enhancement.
    3) To control the file name and location you need to write out the .P2X configuration file each time. I do this at the top of every program. Just open a file for writing that has same name as your active program and give it the extension .P2X. Then put a bunch of write line commands to write out each line of the file and on the one for file name just stick in whatever path and name you want for the output file.

    This requires that you dimension all of your points. The path that you are on to just pull the data directly from the features avoids the need to dimension so is definitely more direct for this purpose. Just wanted you to know that there is a very good solution for .csv export already available should you need it in the future.
  • Thanks, guys. I'll give both suggestions a shot.
  • Set  PRBHIT = CreateObject("PCDLRN.PointData")

Xclsht.Range("A" & count).Value = FCmd.ID

   Set PRBHIT = FCmd.GetHit (Fcntr, FHITDATA_CENTROID, FDATA_THEO, FDATA_PART, AlignID, PLANE_TOP)
Xclsht.Range("B" & count).Value = Cdbl(PRBHIT.X) ' = THEO_X
Xclsht.Range("C" & count).Value = Cdbl(PRBHIT.Y) ' = THEO_Y
Xclsht.Range("D" & count).Value = Cdbl(PRBHIT.Z) ' = THEO_Z

   Set PRBHIT = FCmd.GetHit (Fcntr, FHITDATA_VECTOR, FDATA_THEO, FDATA_PART, AlignID, PLANE_TOP)
Xclsht.Range("E" & count).Value = Cdbl(PRBHIT.I) ' = THEO_I
Xclsht.Range("F" & count).Value = Cdbl(PRBHIT.J) ' = THEO_J
Xclsht.Range("G" & count).Value = Cdbl(PRBHIT.K) ' = THEO_K

   Set PRBHIT = FCmd.GetHit (Fcntr, FHITDATA_CENTROID, FDATA_MEAS, FDATA_PART, AlignID, PLANE_TOP)
Xclsht.Range("I" & count).Value = Cdbl(PRBHIT.X) 
Xclsht.Range("J" & count).Value = Cdbl(PRBHIT.Y)
Xclsht.Range("K" & count).Value = Cdbl(PRBHIT.Z)

   Set PRBHIT = FCmd.GetHit (Fcntr, FHITDATA_VECTOR, FDATA_MEAS, FDATA_PART, AlignID, PLANE_TOP)
Xclsht.Range("L" & count).Value = Cdbl(PRBHIT.I)
Xclsht.Range("M" & count).Value = Cdbl(PRBHIT.J)
Xclsht.Range("N" & count).Value = Cdbl(PRBHIT.K)

'Script is correct up To this point
     

Xcl.Visible=True

End If

Next

Set XclWrkbk.XclWorkbooks.SaveAs (VCSVPATH & VSERIALNUMBER & ".csv")


    Works like a charm... Only one problem left. I need the last line of code to save the file as another name. The "template" is a blank file that the script opens and writes to. I need to save the file as a csv in another folder as another name. There is an error being thrown in that line. Any ideas?

    Thanks for the help, guys.
  • You're welcome.

    Try using a debugging messagebox to see what the path actually is (so it is OK): 

    MsgBox VCSVPATH & VSERIALNUMBER & ".csv"


    Also, you might need to throw in a fileformat parameter to the SaveAs call so Excel knows what format to save it in?

  • 2) Type an "A" after the path to the executable. This switch puts it in auto mode. I believe Anders was responsible for this enhancement.

    I don't know about "responsible", but I did suggest it, and then it was there :-)
  • Also, you might need to throw in a fileformat parameter to the SaveAs call so Excel knows what format to save it in?

    Can those parameters be seen in VB express object browser with the PCDmis library added?

    Not sure what the syntax/code is for that.

    And thank you AndersI.
© 2024 Hexagon AB and/or its subsidiaries. Privacy Policy | Cloud Services Agreement