hexagon logo
Register Sign in
  • Replies 22 replies
  • Subscribers 31 subscribers
  • Views 457 views
  • Users 0 members are here
Options
Related

Another Excel Script Problem

Rookie
Hey, guys. Trying to make an excel script to output the dimensions that appear on the report. We have several that are not shown on the report that are used in the code for logic purposes.

Problem in code is where the xlsht.Range("D" + Count) = Nominal
code starts. No matter what I put after the equal sign, I get an OLE error.

The other "xlsht.Range" commands in the code work fine.

Suggestions?

xlsht.Range("D" + Count) = Nominal 

Sub Main

'Excel Declarations
       Dim xlApp As Object
       Dim xlwrkbks As Object
       Dim xlwrkbk As Object
       Dim xlsht As Object
       Dim Count As Integer


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






        'Variables From Part Program
        Dim VPART, VSERIALNUMBER, VPROGRAMID, VDATE, VTIME, VDESCRIPTION, VCMMOPERATOR, VLOCALCSV As Object
        Dim VREV As Object

        Set VREV = Part.GetVariableValue("VREV")
        Set VPART = Part.GetVariableValue("VPART")
        Set VDESCRIPTION = Part.GetVariableValue("VDESCRIPTION")
        Set VSERIALNUMBER = Part.GetVariableValue("VSERIALNUMBER")
        Set VPROGRAMID = Part.GetVariableValue("VPROGRAMID")
        Set VDATE = Part.GetVariableValue("VDATE")
        Set VTIME = Part.GetVariableValue("VTIME")
        Set VCMMOPERATOR = Part.GetVariableValue("VCMMOPERATOR")
        Set VLOCALCSV = Part.GetVariableValue("VLOCALCSV")

        'Open Excel And Template File
        Set xlApp = CreateObject("Excel.Application")
        Set xlwrkbks = xlApp.Workbooks
        Set xlwrkbk = xlApp.Workbooks.Open("D:\CMM_PROGRAMS\PC-DMIS_SUPPORT_FILES\EXCEL_FILES\TEMPLATE.CSV")
        Set xlsht = xlwrkbk.Worksheets("TEMPLATE")


        'EXCEL SHEET HEADER INFO
        xlsht.Range("A2").Value = "GE Aviation Batesville Composites Operation"
        xlsht.Range("A3").Value = "Part # :"
        xlsht.Range("A4").Value = "Date :"
        xlsht.Range("A5").Value = "Description :"
        xlsht.Range("A6").Value = "Serial # :"
        xlsht.Range("A7").Value = "Operator :"
        xlsht.Range("A8").Value = "Rev:"

        xlsht.Range("C3").Value = VPART.StringValue
        xlsht.Range("C4").Value = VDATE.StringValue
        xlsht.Range("C5").Value = VDESCRIPTION.StringValue
        xlsht.Range("C6").Value = VSERIALNUMBER.StringValue
        xlsht.Range("C7").Value = VCMMOPERATOR.StringValue
        xlsht.Range("C8").Value = VREV.StringValue

        xlsht.Range("A11").Value = "Dimension Name"
        xlsht.Range("D11").Value = "Nominal"
        xlsht.Range("E11").Value = "+TOL"
        xlsht.Range("F11").Value = "-TOL"
        xlsht.Range("G11").Value = "Measured"
        xlsht.Range("H11").Value = "MAX"
        xlsht.Range("I11").Value = "MIN"
        xlsht.Range("J11").Value = "Deviation"
        xlsht.Range("K11").Value = "OOT"
        xlsht.Range("L11").Value = "Bonus Tol"




        Count = Count + 1



       For Each Cmd In Cmds

            If Cmd.IsDimension Then

                Set DCmd = Cmd.DimensionCommand
                Set DCmdID = DCmd.ID

                If Not DCmd.OutputMode = "None" Then

                   xlsht.Range("D" + Count) = Nominal

                End If
            End If

        Next Cmd


        xlApp.Visible = True



End Sub
  • MS example of range. Are you using it correctly?

    Dim range1 As Excel.Range = vstoWorksheet.Range("C8")
    vstoWorksheet.Range("A6", range1).Value2 = "Range 3"

    maybe it should be:
    xlsht.Range("D", Count) = Nominal
  • xlsht.Range("A", Count) = DCmdID


    Says object does not have a default value.


    The below works great. 

    xlsht.Range("A2").Value = "GE Aviation Batesville Composites Operation"
        xlsht.Range("A3").Value = "Part # :"
        xlsht.Range("A4").Value = "Date :"
        xlsht.Range("A5").Value = "Description :"
        xlsht.Range("A6").Value = "Serial # :"
        xlsht.Range("A7").Value = "Operator :"
        xlsht.Range("A8").Value = "Rev:"

        xlsht.Range("C3").Value = VPART.StringValue
        xlsht.Range("C4").Value = VDATE.StringValue
        xlsht.Range("C5").Value = VDESCRIPTION.StringValue
        xlsht.Range("C6").Value = VSERIALNUMBER.StringValue
        xlsht.Range("C7").Value = VCMMOPERATOR.StringValue
        xlsht.Range("C8").Value = VREV.StringValue

        xlsht.Range("A11").Value = "Dimension Name"
        xlsht.Range("D11").Value = "Nominal"
        xlsht.Range("E11").Value = "+TOL"
        xlsht.Range("F11").Value = "-TOL"
        xlsht.Range("G11").Value = "Measured"
        xlsht.Range("H11").Value = "MAX"
        xlsht.Range("I11").Value = "MIN"
        xlsht.Range("J11").Value = "Deviation"
        xlsht.Range("K11").Value = "OOT"
        xlsht.Range("L11").Value = "Bonus Tol"


  • xlsht.Range("D" + Count) = Nominal


    Try:
    xlsht.Range("D" & Count) = Nominal

    Sent from my SPH-L710 using Tapatalk
  • For Each Cmd In Cmds

            If Cmd.IsDimension Then

                Set DCmd = Cmd.DimensionCommand
                Set DCmdID = DCmd.ID

                FeatureList(Fcntr) = DCmd.ID 
                Fcntr = Fcntr + 1

               ' If Not DCmd.OutputMode = "None" Then
                      
                      xlsht.Range("D" & Count).Value = Nominal
                  
               ' End If
            End If

        Next Cmd




    No errors but the spreadsheet is blank..

  • Try:
    xlsht.Range("D" & Count) = dcmd.Nominal




    Sent from my SPH-L710 using Tapatalk
  • Thanks DJ. It's only outputting 1 dimension so I assume it's not looping the way it should.


    The Bonus Tolerance call is telling me unknown name (Not sure of the syntax for Bonus Tolerance)

    And the A column for the dimension name won't put the name in.

    Here's a screenshot of what it's outputting: 





    Sub Main

'Excel Declarations
       Dim xlApp As Object
       Dim xlwrkbks As Object
       Dim xlwrkbk As Object
       Dim xlsht As Object
       Dim Count As Integer


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






        'Variables From Part Program
        Dim VPART, VSERIALNUMBER, VPROGRAMID, VDATE, VTIME, VDESCRIPTION, VCMMOPERATOR, VLOCALCSV As Object
        Dim VREV As Object

        Set VREV = Part.GetVariableValue("VREV")
        Set VPART = Part.GetVariableValue("VPART")
        Set VDESCRIPTION = Part.GetVariableValue("VDESCRIPTION")
        Set VSERIALNUMBER = Part.GetVariableValue("VSERIALNUMBER")
        Set VPROGRAMID = Part.GetVariableValue("VPROGRAMID")
        Set VDATE = Part.GetVariableValue("VDATE")
        Set VTIME = Part.GetVariableValue("VTIME")
        Set VCMMOPERATOR = Part.GetVariableValue("VCMMOPERATOR")
        Set VLOCALCSV = Part.GetVariableValue("VLOCALCSV")

        'Open Excel And Template File
        Set xlApp = CreateObject("Excel.Application")
        Set xlwrkbks = xlApp.Workbooks
        Set xlwrkbk = xlApp.Workbooks.Open("D:\CMM_PROGRAMS\PC-DMIS_SUPPORT_FILES\EXCEL_FILES\TEMPLATE.CSV")
        Set xlsht = xlwrkbk.Worksheets("TEMPLATE")


        'EXCEL SHEET HEADER INFO
        xlsht.Range("A2").Value = "GE Aviation Batesville Composites Operation"
        xlsht.Range("A3").Value = "Part # :"
        xlsht.Range("A4").Value = "Date :"
        xlsht.Range("A5").Value = "Description :"
        xlsht.Range("A6").Value = "Serial # :"
        xlsht.Range("A7").Value = "Operator :"
        xlsht.Range("A8").Value = "Rev:"

        xlsht.Range("C3").Value = VPART.StringValue
        xlsht.Range("C4").Value = VDATE.StringValue
        xlsht.Range("C5").Value = VDESCRIPTION.StringValue
        xlsht.Range("C6").Value = VSERIALNUMBER.StringValue
        xlsht.Range("C7").Value = VCMMOPERATOR.StringValue
        xlsht.Range("C8").Value = VREV.StringValue

        xlsht.Range("A11").Value = "Dimension Name"
        xlsht.Range("D11").Value = "Nominal"
        xlsht.Range("E11").Value = "+TOL"
        xlsht.Range("F11").Value = "-TOL"
        xlsht.Range("G11").Value = "Measured"
        xlsht.Range("H11").Value = "MAX"
        xlsht.Range("I11").Value = "MIN"
        xlsht.Range("J11").Value = "Deviation"
        xlsht.Range("K11").Value = "OOT"
        xlsht.Range("L11").Value = "Bonus Tol"




        Count = Count + 13

 


       For Each Cmd In Cmds

            If Cmd.IsDimension Then

                Set DCmd = Cmd.DimensionCommand
                Set DCmdID = DCmd.ID

                FeatureList(Fcntr) = DCmd.ID 
                Fcntr = Fcntr + 1

               ' If Not DCmd.OutputMode = "None" Then
                      
                      xlsht.Range("A" & Count).Value = DCmd.ID
                      xlsht.Range("D" & Count).Value = DCmd.Nominal
                      xlsht.Range("E" & Count).Value = DCmd.Plus
                      xlsht.Range("F" & Count).Value = DCmd.Minus
                      xlsht.Range("G" & Count).Value = DCmd.Measured
                      xlsht.Range("H" & Count).Value = DCmd.Max
                      xlsht.Range("I" & Count).Value = DCmd.Min
                      xlsht.Range("J" & Count).Value = DCmd.Deviation
                      xlsht.Range("K" & Count).Value = DCmd.OutTol
                      xlsht.Range("L" & Count).Value = DCmd.BonusTol




                  
               ' End If
            End If

        Next Cmd


        xlApp.Visible = True






End Sub
  • I'm going to have to bail on you for the day - sorry.

    I will say that some dimensions need to ba handled differently than others - legacy position and location dimensions for example.

    Here is a link to a post by AndersI that explains the situation and has really clean code that outputs to a .csv file. It should be easy enough to adapt this to drop into Excel.

    Good luck!

    http://www.pcdmisforum.com/showthread.php?24690-Help-with-OLE-Automation&p=356141&viewfull=1#post356141
  • a couple things:
    1) what is Nominal? Where is it declared & what is the value?
    2) not all dimensions will have an ID. You may have to check the type of dimension. Legacy Location contains:
    a) a start dimension (cmd.IsDimension = TRUE) - cmd.ID returns the ID.
    b) each axis within that dimension (cmd.IsDimension = TRUE), cmd.ID will return a blank
    c) end location (cmd.IsDimension = FALSE)
  • Find out what value the variable is with a MsgBox,

    msgbox = "DcmdId.Type", the message will be an integer. Look it up through the help menu (number value will indicate whether it is a double, string, etc)

    Then in your code assign that variable with the default value it is looking for

    xlsht.Range("A", Count) = DCmdID.Double or .String or whatever

    That's what that error message is looking for, the variable type method after the variable name.
© 2024 Hexagon AB and/or its subsidiaries. Privacy Policy | Cloud Services Agreement