iLogic: select parts in assembly from excel list - add custom iProp from excel

[Neuzzo]

Hi, i need an idea or a portion of code or some to work to solve this problem.

I've an assembly with around 1000/1500 parts and an excel list with code of each part and price and type of part (if is a spare parts or consumable parts).

I've found a go.excel function to open the excel file, but how i can do this?

In a simple way:

Run iLogic rule

iLogic open excel file

iLogic start read column "A" of excel (the part number), select the corrisponding part in the assembly and update the iProp with value of column "B" and "C"?

All help is much apreciated. THanks in advance

[Neuzzo]

I've found some codes and i try to mod.

But there is an error when i run the rule

"Impossible to run cast of object type "System.string" on type "Inventor.Propertyset"

How i can fix it?

Thanks

Sub Main ()
'Create variables'set a reference to the assembly component definintion
Dim oAssDoc As AssemblyDocument = ThisApplication.ActiveDocument
Dim ExcelFullName As String
Dim FileName As String 
'Set Excel database

'present a File Selection dialog
'Dim oFileDlg As Inventor.FileDialog = Nothing
'InventorVb.Application.CreateFileDialog(oFileDlg)
'oFileDlg.InitialDirectory = oOrigRefName
'oFileDlg.CancelError = True
''On Error Resume Next
'oFileDlg.ShowOpen()
'If Err.Number <> 0 Then
'Return
'ElseIf oFileDlg.FileName <> "" Then
'ExcelFullName = oFileDlg.FileName
'End If

ExcelFullName = "C:\Users\danny\Desktop\consumabili.xlsx"

'Open Excel database
GoExcel.Open(ExcelFullName,"DISTINTA BASE")

'Iterate through each referenced document'Dim oOcc As ComponentOccurrence
    For Each oDoc As Document In oAssDoc.AllReferencedDocuments
    ErHa = "Start"
    Try
        'Extract Part Number of active occurrence 
        Dim oPropSet As PropertySet = iProperties.Value("Project", "Part number")	
        'oSC = oPropSet.Item("Part Number")
        'oPN = oPropSet.Item("PART NO")
        'oPartNumber = oSC.Value ' & "-" & oPN.Value 
                    
        'MessageBox.Show(oPartNumber, "Title")

    ErHa = "Define custom property collection"
        Parameter.UpdateAfterChange = True       
        i = GoExcel.FindRow(ExcelFullName, "DISTINTA BASE", "Part Number", "=", oPropSet)
        
        Dim PREZZO As String = GoExcel.CurrentRowValue("Unit price €")
        If String.IsNullOrEmpty(PREZZO) Then
        GetProperty(oPropSet, "Estimated Cost").Value = ""
        Else
        GetProperty(oPropSet, "Estimated Cost").Value = PREZZO
        End If
      
        ErHa = "Update the file"
        iLogicVb.UpdateWhenDone = True
        
        Catch ex As Exception
        MsgBox("Part: " & oDoc.DisplayName & vbLf & "Code-Part: " & ErHa & vbLf & "Error: " & ex.Message)
  
    End Try
   
    Next 
'Close Excel database
GoExcel.Close

End Sub

Private ErHa As String = vbNullString

Function GetProperty(oPropset As PropertySet, iProName As String) As Inventor.Property
    ErHa = "GetProperty: " & iProName
    Dim iPro As Inventor.Property
    Try
        'Attempt to get the iProperty from the document
        iPro = oPropset.Item(iProName)
    Catch
        'Assume error means not found, so create it
        iPro = oPropset.Add("", iProName)
    End Try
    Return iPro
End Function

[Neuzzo]

I've found a solution if anoyone need it:

Sub Main ()
'Crea una variabile per il documento
Dim oAssDoc As AssemblyDocument = ThisApplication.ActiveDocument
Dim ExcelFullName As String
Dim FileName As String 

'Selezionare file excel
Dim oFileDlg As Inventor.FileDialog = Nothing
InventorVb.Application.CreateFileDialog(oFileDlg)
oFileDlg.InitialDirectory = oOrigRefName
oFileDlg.CancelError = True
oFileDlg.ShowOpen()
If Err.Number <> 0 Then
Return
ElseIf oFileDlg.FileName <> "" Then
ExcelFullName = oFileDlg.FileName
End If

'Apri foglio Excel
GoExcel.Open(ExcelFullName,"DISTINTA BASE")

'Cicla su ogni parte dell'assieme 
    For Each oDoc As Document In oAssDoc.AllReferencedDocuments
    ErHa = "Start"
    Try
        'Estrae il numero parte e lo setta senza estensione
        Dim oPropSet As PropertySet = oDoc.PropertySets.Item("Inventor User Defined Properties")	
        oPartNumber = Left(oDoc.DisplayName, Len(oDoc.DisplayName) - 4) '(Left(oDoc.DisplayName) -4)
                    
        'MessageBox.Show(oPartNumber)

    ErHa = "Define custom property collection"
        Parameter.UpdateAfterChange = True       
        i = GoExcel.FindRow(ExcelFullName, "DISTINTA BASE", "Part Number", "=", oPartNumber)
		
		Dim COSTO As String = GoExcel.CurrentRowValue("Unit price €")
        If String.IsNullOrEmpty(COSTO) Then
        GetProperty(oPropSet, "Costo").Value = ""
        Else
        GetProperty(oPropSet, "Costo").Value = COSTO
        End If
		
'        GetProperty(oPropSet, "Costo").Value = GoExcel.CurrentRowValue("Unit price €")

    ErHa = "Update the file"
        iLogicVb.UpdateWhenDone = True
        Catch ex As Exception
        MsgBox("Part: " & oDoc.DisplayName & vbLf & "Code-Part: " & ErHa & vbLf & "Error: " & ex.Message)
    'Catch    
    
    End Try
   
    Next 
'Chiudi foglio excel
GoExcel.Close

End Sub

Private ErHa As String = vbNullString

Function GetProperty(oPropset As PropertySet, iProName As String) As Inventor.Property
    ErHa = "GetProperty: " & iProName
    Dim iPro As Inventor.Property
    Try
        'Attempt to get the iProperty from the document
        iPro = oPropset.Item(iProName)
    Catch
        'Assume error means not found, so create it
        iPro = oPropset.Add("", iProName)
    End Try
    Return iPro
End Function