Monday, July 29, 2013

Ilogic - Add Standard Virtual Parts From an Excel File

You have a number of standard Virtual parts that you find yourself adding over and over. You'd like to have the ability to add them based on a predefined list.

You saw the post about adding these parts from a text file, but you'd like to be able to add standard iProperties for these virtual parts also. This way your Bill of Materials information for the virtual parts can be set when the virtual part is created. So you were wanting to read data from an XLS file that contains all of the information that you typically add for virtual parts.

Here is an example iLogic rule that will read a *.xls file and present the contents to the user in an input box list. The user is then asked to enter a quantity for the virtual part, and then the virtual part occurrences are added to the assembly. If one or more occurrences of the virtual part exist in the assembly, the iLogic rule deletes them and just adds back the total number needed.

As the virtual parts are added, the iProperty information found in the *.xls file is added also. 
In this example the parts are placed by using the A column, which is the description. Then the other columns are read in and used to populate the virtual part's iProperties.

An example XLS file list with iProperty information.

Dim MyArrayList As New ArrayList
MyArrayList = GoExcel.CellValues("U:\iLogic examples\Virtual Part List.xls", "Sheet1", "A2", "A1000")
Dim sVirtPart As String
'get user input from list
sVirtPart = InputListBox("Select a virtual part to add.", _
MyArrayList, MyArrayList.Item(0), "iLogic", "Standard Virtual Parts")
'check for empty input in the case where the user cancels out of the input box
If sVirtPart = "" Then
Return 'end rule
End if

'get iProperties from the XLS file
For MyRow = 2 To 1000 'index row 2 through 1000
                'find the cell in column A that matches the user selection
                 If sVirtPart = (GoExcel.CellValue("A" & MyRow)) Then
            'get the iProperty from the XLS file for each column
                oProp1 = GoExcel.CellValue("A" & MyRow )
            oProp2 = GoExcel.CellValue("B" & MyRow )
            oProp3 = GoExcel.CellValue("C" & MyRow)
            oProp4 = GoExcel.CellValue("D" & MyRow)
            oProp5 = GoExcel.CellValue("E" & MyRow)
            Exit For
            End If

'get quantity from user
iQTY = InputBox("Enter the TOTAL number of:" _
& vblf & "        ''" & sVirtPart & "''" _
& vblf & "to place in the assembly." _
& vblf &  vblf & "Note: Enter 0 to delete all existing instances.", "iLogic", "1")
'check for empty input in the case where the user cancels out of the input box
If iQTY = "" Then
Return 'end rule
End if

'define assembly
Dim asmDoc As AssemblyDocument
asmDoc = ThisApplication.ActiveDocument
'define assembly Component Definition
Dim oAsmCompDef As AssemblyComponentDefinition
oAsmCompDef = ThisApplication.ActiveDocument.ComponentDefinition

'Iterate through all of the occurrences in the assembly
Dim asmOcc As ComponentOccurrence
For Each asmOcc  In oAsmCompDef.Occurrences
                'get name of occurence only (sees only everything left of the colon)
                Dim oOcc As Object
            oOcc =":")(0)
            'look at only virtual components
                If TypeOf asmOcc.Definition Is VirtualComponentDefinition Then
                        'compare name selected from list to the
                                'existing virtual parts
                                If oOcc = sVirtPart Then
                        'delete existing virtual parts if name matches
                        End if
            End If
Dim occs As ComponentOccurrences
occs = asmDoc.ComponentDefinition.Occurrences
Dim identity As Matrix
identity = ThisApplication.TransientGeometry.CreateMatrix

'create first instance of the virtual part
Dim virtOcc As ComponentOccurrence
if  iQTY >= 1 Then
virtOcc = occs.AddVirtual(sVirtPart, identity)
            iProperties.Value(sVirtPart & ":1", "Project", "Description") = oProp1
                Catch 'catch error when oProp1 = nothing
                End Try
            iProperties.Value(sVirtPart & ":1", "Project", "Part Number") = oProp2
                Catch 'catch error when oProp2 = nothing
                End Try
            iProperties.Value(sVirtPart & ":1", "Project", "Revision Number") = oProp3
                Catch 'catch error when oProp3 = nothing
                End Try
            iProperties.Value(sVirtPart & ":1", "Project", "Vendor") = oProp4
                Catch 'catch error when oProp4 = nothing
                End Try
            iProperties.Value(sVirtPart & ":1", "Summary", "Comments") = oProp5
                Catch 'catch error when oProp5 = nothing
                End Try
End if

'add next instance starting at instance2 (if applicable)
Dim index As Integer
index = 2
Do While index <= iQTY
occs.AddByComponentDefinition(virtOcc.Definition, identity)
index += 1