topic Re: Using iLogic to retrieve excel variables (price, cost) based on Part Numbers in Inventor Programming Forum

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

Anonymous — Tue, 16 Jun 2020 13:06:34 GMT

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.

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

Anonymous — Tue, 16 Jun 2020 13:35:26 GMT

I get the following error... I do have a column titled Part Number, what am I doing wrong?

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

WCrihfield — Tue, 16 Jun 2020 14:37:11 GMT

Do you know precisely where within your code the problem happens?

Have you tried placing a bunch of MsgBox("1"), MsgBox("2"), etc, throughout the code that will show you the last location that runs without errors, or some other similar debug technique?

It would be nearly impossible for us to test your code without having your files to experiment with.

But don't post your files if they contain private or proprietary info.

Perhaps if you created a very simplified version of your files that are attempting to do the same type of thing, you could post them here, for testing, so we can better help you solve your problem.

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

Anonymous — Tue, 16 Jun 2020 15:09:16 GMT

Hi WCrihfield

With regards to isolating the error...

The error occurs at

"Define custom property collection"

The message box appears when inventor reads this line of code.

However spending the last 5 hours trouble shooting this and at times the message box not appearing... I still can't get the estimated costs in iproperties to update.

With regards to your message:

Simply put, what I would like this code to do (running within an assembly) is to:

retrieve an excel file
find the column titled Part Number
match the part number of an assembly item with that of a part number in excel
find the price along the row of that part number
read the price in excel and...
write that same price in the iproperties "estimated cost" of each part of the assembly

I'm fairly new to VB and ilogic and so far I've been copying and pasting and modifying code to run tasks that I want.

I can't seem to get this one working.

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

marcin_otręba — Tue, 16 Jun 2020 15:16:02 GMT

i think in line :

 i = GoExcel.FindRow("\\SRV-DOC\company folder\program name\product family\pricing.xlsx", "sheet name", "Part Number") '"=", oPartNumber)

is error and should be :

i = GoExcel.FindRow("\\SRV-DOC\company folder\program name\product family\pricing.xlsx", "sheet name", "Part Number", "=", oPartNumber)

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

Anonymous — Tue, 16 Jun 2020 15:39:31 GMT

Ok, so I decided to start from the original source code and modify it....

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_NAME\PROGRAM_NAME\PRODUCT_FAMILY\PRODUCT_NUMBER.xlsx" Then

ExcelFullName = oFileDlg.FileName
End If

'Open Excel database
GoExcel.Open(ExcelFullName,"PRODUCT NUMBER")

'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       
       i = (GoExcel.FindRow(ExcelFullName, "PRODUCT NUMBER", "Part Number", "=", oPartNumber))
		'If i = "-1" 
		'	GoTo PROSSIMO
		'End If
		
		Dim COSTO As String = GoExcel.CurrentRowValue("Price")
        If String.IsNullOrEmpty(COSTO) Then
        GetProperty(oPropSet, "Estimated Cost").Value = ""
        Else
        GetProperty(oPropSet, "Estimated Cost").Value = COSTO
'		GetProperty(oPropSet, "Tipo ricambio").Value = "C"
        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
PROSSIMO:
    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

But now I get the following error:

So it can't find the column titled Part Number ... this title is found on cell A4.

Do I need to specify somewhere in the code where to start reading the part numbers, in this case A5?

Help

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

WCrihfield — Tue, 16 Jun 2020 15:36:26 GMT

Good catch @marcin_otręba . I saw that earlier, but since I didn't attempt to test the code, it didn't 'click'.

By the way, I see you using the line "ErHa = " several times throughout the code, but don't see where you set its Type as String. Is that causing any problems? (Usually doesn't, but depending on if you have certain options set for strictness, it might.)

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

marcin_otręba — Tue, 16 Jun 2020 15:37:26 GMT

can you share here your excel ?

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

WCrihfield — Tue, 16 Jun 2020 15:46:16 GMT

There may be a couple lines missing that may help clear this up.

Try setting the GoExcel.TitleRow and GoExcel.FindRowStart. Those help when your column titles aren't on the top row, and when your first data entries aren't on the second row.

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

Anonymous — Tue, 16 Jun 2020 16:15:58 GMT

Here's a simplified version of my excel... Please see attached.

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

marcin_otręba — Tue, 16 Jun 2020 16:22:50 GMT

The reason is that you have first row blank.

Also

i = GoExcel.FindRow("C:\Vault\PRODUCT_NUMBER.xlsx", "Sheet1", "Part Number", "=", "XXX-00XX-0X")

is case sensitive and in excel you have upper cases.

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

WCrihfield — Tue, 16 Jun 2020 16:30:40 GMT

Dim oXLfile As String = "C:\Temp\PRODUCT_NUMBER.xlsx"
Dim oSheet As String = "Sheet1"
GoExcel.TitleRow = 2
GoExcel.FindRowStart = 3
Dim oRow As Integer = GoExcel.FindRow(oXLfile, oSheet, "PART NUMBER", "=", "XXX-00XX-0X")
Dim oPrice As Double = GoExcel.CurrentRowValue("PRICE")
MsgBox(oPrice)

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

Anonymous — Tue, 16 Jun 2020 16:32:34 GMT

Hi W!

Ok so from what I understand, the error occurs above when I haven't defined the starting row and title row when calling out excel.

Using GoExcel.TitleRow and GoExcel.FindRowStart should fix the error.

So in the "Define custom property collection" I should write the following (sorry I'm writing outloud):

If GoExcel.TitleRow("A" & 2) AND GoExcel.FindRowStart("A" & 3) Then, match part numbers

Is this correct?

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

WCrihfield — Tue, 16 Jun 2020 16:37:44 GMT

No. Don't write it as if you're asking a question of the code. Those are setting statements. You have to tell it where your Title row is (Row # 2 in the example file). and you have to tell it where your first row of data starts (Row # 3 in your example file). The same way I have it in my last example code. After you have set those settings, the FindRow knows where to start looking, and it knows where to start looking for the Title row, when you specify it.

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

WCrihfield — Tue, 16 Jun 2020 16:39:37 GMT

By the way, this process doesn't like empty rows, so you may have to put a check in there to make sure the value is not Nothing, so it won't throw an error when trying to process a blank row.

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

Anonymous — Tue, 16 Jun 2020 16:51:31 GMT

Ok fantasic!!

You're both right, when calling excel its case sensitive and that its important to reference the title row and starting row before reading the any excel value.

So far the code is as follows:

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
    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")
		'MsgBox(oPrice)
       i = (GoExcel.FindRow(ExcelFullName, "Sheet1", "PART NUMBER", "=", oPartNumber))
		'If i = "-1" 
		'	GoTo PROSSIMO
		'End If
		
		Dim COSTO As String = GoExcel.CurrentRowValue("PRICE")
        If String.IsNullOrEmpty(COSTO) Then
        GetProperty(oPropSet, "Estimated Cost").Value = ""
        Else
        GetProperty(oPropSet, "Estimated Cost").Value = COSTO
        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
PROSSIMO:
    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

But when I right click on each part to see its iproperties the 'Estimate Cost' is not updating. What am I missing here?

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

Anonymous — Tue, 16 Jun 2020 16:55:07 GMT

I think I need to fit this line of code somewhere...

'set iProperty to value from excel

iProperties.Value("Project", "Estimated Cost") = oPrice

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

WCrihfield — Tue, 16 Jun 2020 17:08:39 GMT

When you're in an assembly, trying to set values of iProperties within its sub assemblies or parts, you can't just say "iProperties.Value(SetName, PropName) = oValue". That will just try to put it in the assembly. You would need to either use something like "iProperties.Value(oComponentName, SetName, PropName) = " or "oPropSet.Item("PropName").Value =

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

WCrihfield — Tue, 16 Jun 2020 17:11:09 GMT

@Anonymous

I would just put it in right where you have MsgBox(oPrice) commented out.

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

WCrihfield — Tue, 16 Jun 2020 17:21:29 GMT

@Anonymous

I think I have found another kink to straighten out. It is misleading, but the iProperty you are trying to assign a value to is called "Estimated Cost" within the iProperties dialog box, but its official name is just "Cost" in the iProperties list.

So it's in the "Design Tracking Properties" set, and the PropertyName is "Cost".

The "Cost Center" is as it should be..."Cost Center", though.