Community
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.
Wesley Crihfield
(Not an Autodesk Employee)
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
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.)
Wesley Crihfield
(Not an Autodesk Employee)
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.
Wesley Crihfield
(Not an Autodesk Employee)
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.
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)
Wesley Crihfield
(Not an Autodesk Employee)
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?
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.
Wesley Crihfield
(Not an Autodesk Employee)
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.
Wesley Crihfield
(Not an Autodesk Employee)
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 =
Wesley Crihfield
(Not an Autodesk Employee)
@Anonymous
I would just put it in right where you have MsgBox(oPrice) commented out.
Wesley Crihfield
(Not an Autodesk Employee)
@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.
Wesley Crihfield
(Not an Autodesk Employee)
Forums Links
Can't find what you're looking for? Ask the community or share your knowledge.
Post to forums
