hexagon logo
Register Sign in
  • Replies 13 replies
  • Subscribers 221 subscribers
  • Views 284 views
  • Users 0 members are here
Options
Related

Script help

A-machine-insp
I'm at my new job and I need some help with a script we have written into every program. The script saves results as an Excel doc but it saves it to wherever the program is running from so we have these docs saved on multiple different computers. Part of my new job is organizing all of our digital data so I am making jobs folders for each part on the network and I want to direct the script to save the Excel doc there instead of on the local computer. Now... I am no expert in scripting (I really have no experience with it) so if someone would be willing to look through this and help me with changes, it would be much appreciated. The company and I both know that the script in each program would have to be changed and there is no issues with me spending the time to do it. 

Sub Main (strVariable As String, reasonVar As String)


'xl Declarations
Dim xlApp As Object
Dim xlWorkbooks As Object
Dim xlWorkbook As Object
Dim xlSheet As Object
Dim count As Integer


'pcdlrn declarations And Open ppg
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 DimID As String
Dim fs As Object
Dim ReportDim As String
Dim CheckDim As String


'Check To see If results file exists
FilePath = "C:\Excel Data\" '.xlsm And .bas files location
DataPath = App.ActivePartProgram.Path 'program location path
Set fs = CreateObject("Scripting.FileSystemObject")
ResFileExists = fs.fileexists(DataPath & Part.partname & " " & strVariable & " " & reasonVar & ".xlsm") 'check program folder For .xlsm file


'Open Excel And Base form
Set xlApp = CreateObject("Excel.Application")
Set xlWorkbooks = xlapp.Workbooks
If ResFileExists = False Then
TempFilename = FilePath & "Loop Template Column.xlsm"
Else
TempFilename = DataPath & Part.partname & " " & strVariable & " " & reasonVar & ".xlsm"
End If
Set xlWorkbook = xlWorkbooks.Open(TempFilename)
Set xlSheet = xlWorkbook.Worksheets("Sheet1")


If ResFileExists = False Then
RCount=6
CCount=3
xlSheet.Range("B1").Value = Part.PartName
xlSheet.Range("E4").Value = Date() & " " & Time()
xlSheet.Range("D1").Value = strVariable
xlSheet.Range("C2").Value = reasonVar



For Each Cmd In Cmds
'Eliminate DATDEF's
If Cmd.Type <> 1299 Then
'Do Dimensions
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 = "STATS" Then
If DCmd.ID = "" Then
xlSheet.Cells(RCount,4).Value = DimID & " . "& DCmd.AxisLetter
Else
xlSheet.Cells(RCount,4).Value = DCmd.ID & " . " & "M"
End If
xlSheet.Cells(RCount,1).Value = DCmd.Nominal
xlSheet.Cells(RCount,2).Value = DCmd.Plus
xlSheet.Cells(RCount,3).Value = DCmd.Minus
'Measured Or Deviation With check For True Position
If DCmd.AxisLetter <> "TP" Then
xlSheet.Cells(RCount,5).Value = DCmd.Measured
Else
xlSheet.Cells(RCount,5).Value = DCmd.Deviation
End If
'Add Min/Max For Profile dimensions
If Cmd.Type = 1118 Or Cmd.Type = 1105 Then
RCount=RCount+1
xlSheet.Cells(RCount,4).Value = DCmd.ID & "." & "Max"
xlSheet.Cells(RCount,1).Value = DCmd.Nominal
xlSheet.Cells(RCount,2).Value = DCmd.Plus
xlSheet.Cells(RCount,3).Value = DCmd.Minus
xlSheet.Cells(RCount,5).Value = DCmd.Max
RCount=RCount+1
xlSheet.Cells(RCount,4).Value = DCmd.ID & "." & "Min"
xlSheet.Cells(RCount,1).Value = DCmd.Nominal
xlSheet.Cells(RCount,2).Value = DCmd.Plus
xlSheet.Cells(RCount,3).Value = DCmd.Minus
xlSheet.Cells(RCount,5).Value = DCmd.Min
End If
RCount=RCount+1
End If
End If
End If
'Do GDT
If Cmd.Type = 184 Then
ReportDim = Cmd.GetText ( OUTPUT_TYPE, 0)
If ReportDim = "BOTH" Or ReportDim = "STATS" Then
xlSheet.Cells(RCount,4).Value = Cmd.GetText (ID, 0) & "." & "FCF"
xlSheet.Cells(RCount,1).Value = "0"
xlSheet.Cells(RCount,2).Value = Cmd.GetText (LINE2_PLUSTOL, 1)
xlSheet.Cells(RCount,3).Value = "0"
xlSheet.Cells(RCount,5).Value = Cmd.GetText (LINE2_DEV, 1)
RCount=RCount+1
End If
End If
End If
Next Cmd


Else

'Find first Open column.
CCount=5
Found=0
Do Until Found = 1
CCount = CCount + 1
If xlSheet.Cells(4,CCount).Value = "" Then
Found=1
End If
Loop

xlSheet.Cells(4,CCount).Value = Date() & " " & Time()
xlSheet.Cells(5,CCount).Value = " Part " & CCount - 4

'Fill In measured data
RCount = 6
For Each Cmd In Cmds
'Eliminate DATDEF's
If Cmd.Type <> 1299 Then
'Do Dimensions
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 = "STATS" Then
'Measured Or Deviation With check For True Position
If DCmd.AxisLetter <> "TP" Then
xlSheet.Cells(RCount,CCount).Value = DCmd.Measured
Else
xlSheet.Cells(RCount,CCount).Value = DCmd.Deviation
End If
'Add Min/Max For Profile dimensions
If Cmd.Type = 1118 Or Cmd.Type = 1105 Then
RCount=RCount+1
xlSheet.Cells(RCount,CCount).Value = DCmd.Max
RCount=RCount+1
xlSheet.Cells(RCount,CCount).Value = DCmd.Min
End If
Rcount=Rcount+1
End If
End If
End If
'Do GDT
If Cmd.Type = 184 Then
ReportDim = Cmd.GetText ( OUTPUT_TYPE, 0)
If ReportDim = "BOTH" Or ReportDim = "STATS" Then
xlSheet.Cells(RCount,CCount).Value = Cmd.GetText (ID, 0) & "." & "FCF"
xlSheet.Cells(RCount,CCount).Value = "0"
xlSheet.Cells(RCount,CCount).Value = Cmd.GetText (LINE2_PLUSTOL, 1)
xlSheet.Cells(RCount,CCount).Value = "0"
xlSheet.Cells(RCount,CCount).Value = Cmd.GetText (LINE2_DEV, 1)
RCount=RCount+1
End If
End If
End If
Next Cmd
End If


'Save And Cleanup
Set xlSheet = Nothing
SaveName = DataPath & Part.partname & " " & strVariable & " " & reasonVar & ".xlsm"
If ResFileExists = False Then
xlWorkBook.SaveAs SaveName
Else
xlWorkBook.Save
End If
xlWorkbook.Close
Set xlWorkbook = Nothing
xlWorkbooks.Close
Set xlWorkbooks = Nothing
xlApp.Quit
Set xlApp = Nothing

LabelEnd:

End Sub
Parents


  • So would I need to just change these 2 lines to the location I want it saved at? 

    FilePath = "C:\Excel Data\" '.xlsm And .bas files location
DataPath = App.ActivePartProgram.Path 'program location path

    FilePath seems to be the location of a blank template that measurement data is being added to. It looks like the script is looking for previous runs of the same part. If the current execution is the first time that part had been measured, it will open that template and make a copy in the same folder as the part is being run from. From there it will add measurement data. If you want a centralized location to read/write files to/from on the network, you will want to copy over the template file named "Loop Template Column.xlsm" to that new location and update the path in your script.

    Also, DataPath is a variable used to find the path that the current measuring routine is being run from. If you want all the reports to go to the same place, change that to a direct assignment rather than getting the location of the current measuring routine. The line would look something like:

    DataPath = "\\SomePlaceOnNetwork\ReportsOrSomething\ " 'Location of Reports on Network
Reply


  • So would I need to just change these 2 lines to the location I want it saved at? 

    FilePath = "C:\Excel Data\" '.xlsm And .bas files location
DataPath = App.ActivePartProgram.Path 'program location path

    FilePath seems to be the location of a blank template that measurement data is being added to. It looks like the script is looking for previous runs of the same part. If the current execution is the first time that part had been measured, it will open that template and make a copy in the same folder as the part is being run from. From there it will add measurement data. If you want a centralized location to read/write files to/from on the network, you will want to copy over the template file named "Loop Template Column.xlsm" to that new location and update the path in your script.

    Also, DataPath is a variable used to find the path that the current measuring routine is being run from. If you want all the reports to go to the same place, change that to a direct assignment rather than getting the location of the current measuring routine. The line would look something like:

    DataPath = "\\SomePlaceOnNetwork\ReportsOrSomething\ " 'Location of Reports on Network
Children
No Data
© 2024 Hexagon AB and/or its subsidiaries. Privacy Policy | Cloud Services Agreement