Using iLogic to retrieve excel variables (price, cost) based on Part Numbers

[Anonymous]

Hello forum,

I've created a small program that does cost and pricing reports. I've been searching and trouble shooting a few lines of code that I have found on this forum (Thank you @Neuzzo for presenting the original bulk of the code). I had to modify the code to update the 'Estimated Cost' in iproperties of each part at the assembly level. But I've stitched up a few lines of code that worked for me when I'm running at the part level.

Now its a mess of code that doesn't work.

My code as it is right now:

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 


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

'Open Excel database
GoExcel.Open("\\SRV-DOC\company folder\program name\product family\pricing.xlsx", "sheet name")

'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 = oDoc.PropertySets.Item("Inventor User Defined Properties")
        oPartNumber = oDoc.PropertySets.Item("Design Tracking Properties").Item("Part Number").Value
                    
        'MessageBox.Show(oPartNumber)

    ErHa = "Define custom property collection"
	'Parameter.UpdateAfterChange = True 
	'index row 5 through 100 or 150 or 10,000 (too long processing time)
	GoExcel.Open("\\SRV-DOC\company folder\program name\product family\pricing.xlsx", "sheet name")
	For rowPN = 5 To 60
	'find first empty cell in column A
	If (GoExcel.CellValue("A" & rowPN) = iProperties.Value(oPartNumber, "Part Number")) Then
	'find the price of the part using the part number
	oPrice = GoExcel.CellValue("L" & rowPN)
 	End If
	Next
	'MessageBox.Show(oPrice)
	iProperties.Value(oPartNumber, "Estimated Cost") = oPrice
 
ErHa = "Define custom property collection"
	Parameter.UpdateAfterChange = True	      
        i = GoExcel.FindRow("\\SRV-DOC\company folder\program name\product family\pricing.xlsx", "sheet name", "Part Number") '"=", oPartNumber)
		
		If i = "-1" 
			GoTo oEnd
		End If
		
		Dim oPrice As String = GoExcel.CurrentRowValue("Price")
        If String.IsNullOrEmpty(oPrice) Then
        GetProperty(oPropSet, "Estimated Cost").Value = ""
        Else
        GetProperty(oPropSet, "Estimated Cost").Value = oPrice
        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
oEnd:
    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

Part numbers on my excel sheet are found on along the column A and begin on  A5 and its associated pricing are found under the column L.

The following iLogic code searches for the part number and its price:

	'index row 5 through 100 or 150 or 10,000 (too long processing time)
	GoExcel.Open("\\SRV-DOC\MURAFLEX\CONFIGURATOR\MSD-M-B101-00\SAS-003X-01.xlsx", "SAS-003X-XX")
	For rowPN = 5 To 60
	'find first empty cell in column A
	If (GoExcel.CellValue("A" & rowPN) = iProperties.Value(oPartNumber, "Part Number")) Then
	'find the price of the part using the part number
	oPrice = GoExcel.CellValue("L" & rowPN)
	End If
	Next

Then the code should match the part numbers on the excel sheet and return a price from excel:

	'MessageBox.Show(oPrice)
	'iProperties.Value(oPartNumber, "Estimated Cost") = oPrice
	  ErHa = "Define custom property collection"
	Parameter.UpdateAfterChange = True	      
        i = GoExcel.FindRow("\\SRV-DOC\MURAFLEX\CONFIGURATOR\MSD-M-B101-00\SAS-003X-01.xlsx", "SAS-003X-XX", "PART NUMBER") '"=", oPartNumber)
		'i = GoExcel.FindRow(ExcelFullName, "DISTINTA BASE", "Part Number", "=", oPartNumber)
		
		If i = "-1" 
			GoTo oEnd
		End If

And then the price found in excel should update the iproperties value Estimated Cost

		Dim oPrice As String = GoExcel.CurrentRowValue("PRICE")
        If String.IsNullOrEmpty(oPrice) Then
		'If String.IsNullOrEmpty(oCost) Then
        GetProperty(oPropSet, "Estimated Cost").Value = ""
        Else
        GetProperty(oPropSet, "Estimated Cost").Value = oPrice
		'GetProperty(oPropSet, "Estimated Cost").Value = oCost
		'GetProperty(oPropSet, "Tipo ricambio").Value = "C"
        End If

The problem is that whenever I run the iLogic code it doesn't update the Estimated Costs as expected and return an error.

Your help would be greatly appreciated by me and others that may benefit from this.

[WCrihfield]

@Anonymous 

It would look like this then:

iProperties.Value(oDoc, "Design Tracking Properties", "Cost") = oPrice

[Anonymous]

Ok, so I applied the changes and I have the following code... I'm still not getting any value under Estimated Cost for each part in the assembly

    ErHa = "Define custom property collection"
        Parameter.UpdateAfterChange = True
		Dim oXLfile As String = "\\SRV-DOC\MURAFLEX\CONFIGURATOR\MSD-M-B101-00\SAS-003X-01.xlsx"
		Dim oSheet As String = "SAS-003X-XX"
		GoExcel.TitleRow = 2
		GoExcel.FindRowStart = 5
		Dim oRow As Integer = GoExcel.FindRow(oXLfile, oSheet, "PART NUMBER", "=", oPartNumber)
		Dim oPrice As Double = GoExcel.CurrentRowValue("PRICE") 
		'MessageBox.Show(oPrice)
       
        Parameter.UpdateAfterChange = True       
        i = GoExcel.FindRow(ExcelFullName, "SAS-003X-XX", "PART NUMBER", "=", oPartNumber)
		
		If i = "-1" 
			GoTo PROSSIMO
		End If
		
		Dim COSTO As String = GoExcel.CurrentRowValue("PRICE")
        If String.IsNullOrEmpty(COSTO) Then
        GetProperty(oPropSet, "Cost").Value = ""
        Else
        GetProperty(oPropSet, "Cost").Value = COSTO
        End If
		
    ErHa = "Update the file"
        iLogicVb.UpdateWhenDone = True
	

I have changed Estimated Cost to Cost, but still not getting any dollar value for each part.

[WCrihfield]

I don't think you would need to open each document before trying to modify their iProperties, because they are all being referenced by the assembly, but you could try using this line right after your initial For Each oDoc loop, just in case it makes a difference for you:

ThisApplication.Documents.Open(oDoc.FullFileName,False)

Then after you've assigned a value to its iProperties, try oDoc.Save, before you do the oDoc.Update.

[Anonymous]

I've been trying multiple things to get this to work... I can't seem to get the Estimate Cost to update.

My current code:

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 


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 <> "SRV-DOC\company folder\program name\product family\pricing.xlsx" Then
ExcelFullName = oFileDlg.FileName
End If

'Open Excel database
GoExcel.Open(ExcelFullName,"Sheet1")

'Iterate through each referenced document'Dim oOcc As ComponentOccurrence
    For Each oDoc As Document In oAssDoc.AllReferencedDocuments
	ThisApplication.Documents.Open(oDoc.FullFileName,False)

    ErHa = "Start"
    Try
        'Extract Part Number of active occurrence 
        Dim oPropSet As PropertySet = oDoc.PropertySets.Item("Inventor User Defined Properties")
        oPartNumber = oDoc.PropertySets.Item("Design Tracking Properties").Item("Part Number").Value
                    
        'MessageBox.Show(oPartNumber)

    ErHa = "Define custom property collection"
        Parameter.UpdateAfterChange = True
		Dim oXLfile As String = "SRV-DOC\company folder\program name\product family\pricing.xlsx"
		Dim oSheet As String = "Sheet1"
		GoExcel.TitleRow = 2
		GoExcel.FindRowStart = 5
		Dim oRow As Integer = GoExcel.FindRow(oXLfile, oSheet, "PART NUMBER", "=", oPartNumber)
		Dim oPrice As Double = GoExcel.CurrentRowValue("PRICE") 
		'MessageBox.Show(oPrice)
     
		iProperties.Value(oDoc, "Design Tracking Properties", "Cost") = oPrice
			
        Parameter.UpdateAfterChange = True       
        i = GoExcel.FindRow(oXLfile, oSheet, "PART NUMBER", "=", oPartNumber)
		
		If i = "-1" 
		GoTo LAST
		End If
		
		Dim oCost As String = GoExcel.CurrentRowValue("PRICE")
        If String.IsNullOrEmpty(oCost) Then
        GetProperty(oPropSet, "Cost").Value = ""
        Else
        GetProperty(oPropSet, "Cost").Value = oCost
        End If
		
		oDoc.Save
		
    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
	
LAST:
    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

The value I want this to return and update...:

The estimated costs...

[WCrihfield]

Try this code:

It bypasses the whole dialog box part and the whole 'GetProperty' function.  Then it defines the property set right up front (because it is always there).  I think you may have been trying to get the property from the wrong property set.  The "Cost" property is not in the "Inventor User Defined Properties" set, it's in the "Design Tracking Properties" set.

Dim oAssDoc As AssemblyDocument = ThisApplication.ActiveDocument
Dim oXLfile As String = "\\SRV-DOC\company folder\program name\product family\pricing.xlsx"
Dim oSheet As String = "Sheet1"
	GoExcel.Open(oXLfile,oSheet)

	For Each oDoc As Document In oAssDoc.AllReferencedDocuments
			Dim oProjProps As PropertySet = oDoc.PropertySets.Item("Design Tracking Properties")
	        oPartNumber = oProjProps.Item("Part Number").Value
			GoExcel.TitleRow = 2
			GoExcel.FindRowStart = 5
			Dim oRow As Integer = GoExcel.FindRow(oXLfile, oSheet, "PART NUMBER", "=", oPartNumber)
			If GoExcel.CurrentRowValue("PRICE") Is Nothing Then Resume Next
			Dim oPrice As Double = GoExcel.CurrentRowValue("PRICE")
			oProjProps.Item("Cost").Value = oPrice	
			oDoc.Save
    Next 
GoExcel.Close

[WCrihfield · Accepted solution]

@Anonymous 

Or if you want to keep the iProperty overwrite in place that overwrites the Cost value to nothing when the Excel cell for cost if blank, then you can probably do it this way.

	Dim oAssDoc As AssemblyDocument = ThisApplication.ActiveDocument
	Dim oXLfile As String = "\\SRV-DOC\company folder\program name\product family\pricing.xlsx"
	Dim oSheet As String = "Sheet1"

	GoExcel.Open(ExcelFullName,"Sheet1")

	For Each oDoc As Document In oAssDoc.AllReferencedDocuments
			Dim oProjProps As PropertySet = oDoc.PropertySets.Item("Design Tracking Properties")
	        oPartNumber = oProjProps.Item("Part Number").Value
			GoExcel.TitleRow = 2
			GoExcel.FindRowStart = 5
			Dim oRow As Integer = GoExcel.FindRow(oXLfile, oSheet, "PART NUMBER", "=", oPartNumber)
			If GoExcel.CurrentRowValue("PRICE") Is Nothing Then
				oProjProps.Item("Cost").Value = 0
				Resume Next
			End If
			Dim oPrice As Double = GoExcel.CurrentRowValue("PRICE")
			oProjProps.Item("Cost").Value = oPrice	
			oDoc.Save
    Next 
GoExcel.Close

[Anonymous]

AMAZING!

It works! Thank you W!