ilogic code to add virtual parts from a spreadsheet, make parameter then pattern

[kpk7VYPK]

I am on the cusp of completing this code but i am stuck. 

The idea is that the code inserts a list of virtual parts from an excel spreadsheet, creates a user parameter called Tot_"filename" then create a pattern using the created parameter as the Quantity. My code creates the selected part, parameter and pattern but the pattern quantity isnt directly refering to the created parameter.

I would also prefer if the code created all the virtual parts at once without the user selection but i cant get it to work that way. 

Any help would be grealty appreciated. 

Dim MyArrayList As New ArrayList
MyArrayList = GoExcel.CellValues("C:\VaultWorkspace\Designs\PROJ Skiold\Personlige\KPK\Penning\Virtual Parts.xlsx", "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
Else
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("B" & MyRow )
            oProp2 = GoExcel.CellValue("A" & MyRow )

            Exit For
            End If
Next

'get quantity from user
iQTY = "1"
'& 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
Else
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 = asmOcc.Name.Split(":")(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
                                'asmOcc.Delete
                                'Else
                       ' End If
            'Else
            End If
Next
 
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)
            Try
            iProperties.Value(sVirtPart & ":1", "Project", "Description") = oProp1
                Catch 'catch error when oProp1 = nothing
                End Try
            Try
            iProperties.Value(sVirtPart & ":1", "Project", "Part Number") = oProp2
                Catch 'catch error when oProp2 = nothing
                End Try
            Try
           iProperties.Value(sVirtPart & ":1", "Summary", "Title") = oProp1
                Catch 'catch error when oProp2 = nothing
                End Try
Else
Return
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
Loop

'create parameter for pattern
Dim oParams As Parameters
Dim oPartDoc As AssemblyDocument = ThisDoc.Document
Dim oPartCompDef As AssemblyComponentDefinition = oPartDoc.ComponentDefinition
oParams = oPartCompDef.Parameters

Dim oUserParams As UserParameters = oParams.UserParameters

Try
  p = Parameter("Tot_" & CStr(oProp2))
Catch
  oQty=oUserParams.AddByExpression("Tot_" & CStr(oProp2),"1", "ul")
End Try

'Make Pattern

Dim oADoc As AssemblyDocument = ThisApplication.ActiveDocument
Dim oADef As AssemblyComponentDefinition = oADoc.ComponentDefinition
Dim oOccs As ComponentOccurrences = oADef.Occurrences
Dim oTO As TransientObjects = ThisApplication.TransientObjects
Dim oObjects As ObjectCollection = oTO.CreateObjectCollection
oObjects.Add(oOccs.ItemByName(CStr(oProp2) & ":1"))
'oObjects.Add(oOccs.ItemByName("P2"))
Dim oRowEntity As Object = oADef.WorkAxes.Item(3)
Dim oColumnEntity As Object = oADef.WorkAxes.Item(2)

Dim oPatt As RectangularOccurrencePattern
oPatt = oADef.OccurrencePatterns.AddRectangularPattern(oObjects, oColumnEntity, True, oQty, 1)
oPatt.Name = "Component Pattern" & " " & cstr(oProp2)

[A.Acheson]

Hi @kpk7VYPK 

I didn't test all the code but down the very end when you are setting the pattern you are supplying the parameter object and not the parameter value. 

This should be 

oQty.Value

And also you don't set the oQty.Value from excel so your value will either be the value set if it allready exists and or the value of 1 used and creation. 

Ideally you would declare your user parameter early on and not use late bindings so if you have a mistake like this you can see the error more directly in the more info section of the error message.

Dim oQty As UserParameter
Try
  oQty = oUserParams.Item("Tot_" & CStr(oProp2))
Catch
  oQty = oUserParams.AddByExpression("Tot_" & CStr(oProp2),"1", "ul")
End Try

'oQty.Value = ".....From excel"