Community
Hi @petestrycharske. Since it looks like you may know your way around in iLogic and PartsLists for the most part, here is a bit of iLogic code that should point you in the right direction for identifying those columns and cells within your parts lists. The main point I'm showing within is how to use the column's PropertyType and check it against the PropertyTypeEnum variants to determine what type of data the column was designed to show, then record that column for use when looping through the cells of each row, so you know where that data will be in those rows. Of course, if you have not designed those columns into your parts list, you will not find them this way though, but you could probably add them, if not found, and needed.
oDDoc = ThisDrawing.Document
oSheet = oDDoc.ActiveSheet
oPList = oSheet.PartsLists.Item(1)
Dim oCol, oBaseQtyCol, oItemQtyCol, oUnitQtyCol As PartsListColumn
'find columns for quantities
For Each oCol In oPList.PartsListColumns
If oCol.PropertyType = PropertyTypeEnum.kBaseQuantityPartsListProperty Then
oBaseQtyCol = oCol
ElseIf oCol.PropertyType = PropertyTypeEnum.kUnitQuantityPartsListProperty Then
oUnitQtyCol = oCol
ElseIf oCol.PropertyType = PropertyTypeEnum.kItemQuantityPartsListProperty Then
oItemQtyCol = oCol
End If
Next
Dim oBaseQtyVal, oItemQtyVal, oUnitQtyVal As String
For Each oRow As PartsListRow In oPList.PartsListRows
'get Cell values corresponding to identified columns
oBaseQtyVal = oRow.Item(oBaseQtyCol).Value
oItemQtyVal = oRow.Item(oItemQtyCol).Value
oUnitQtyVal = oRow.Item(oUnitQtyCol).Value
Next
If this solved your problem, or answered your question, please click ACCEPT SOLUTION.
Or, if this helped you, please click (LIKE or KUDOS) 👍.
If you want and have time, I would appreciate your Vote(s) for My IDEAS :light_bulb: or you can Explore My CONTRIBUTIONS
Wesley Crihfield
(Not an Autodesk Employee)
Hi @petestrycharske. I believe I understand the situation now. However, in this situation, I believe that working with List objects to get the multiple columns of the same type may be an easier option than attempting to use Array objects, but that is mostly personal preference. With a List, I don't have to specify collection size ahead of time, and it will be 1 based, instead of 0 based, which is just more natural. I altered my iLogic code a bit to accommodate that unique situation. So now I have created 3 List(Of PartsListColumn) objects just ahead of looping through the columns. Each List is for one type of column, which there may be multiple of. And within the loop, if the column is the specific type, it gets added to the corresponding List object. Then later when it is looping through the rows, it now does a secondary loop through those lists of columns, as it does the final checks and changes. See if this idea works better for you.
oDDoc = ThisDrawing.Document
oSheet = oDDoc.ActiveSheet
oPList = oSheet.PartsLists.Item(1)
Dim oBaseQtyCols, oItemQtyCols, oUnitQtyCols As New List(Of PartsListColumn)
'find columns for quantities
For Each oCol As PartsListColumn In oPList.PartsListColumns
If oCol.PropertyType = PropertyTypeEnum.kBaseQuantityPartsListProperty Then
oBaseQtyCols.Add(oCol)
ElseIf oCol.PropertyType = PropertyTypeEnum.kUnitQuantityPartsListProperty Then
oUnitQtyCols.Add(oCol)
ElseIf oCol.PropertyType = PropertyTypeEnum.kItemQuantityPartsListProperty Then
oItemQtyCols.Add(oCol)
End If
Next
'if you really need an Array, instead of a List, just convert it like this
'oBQtyCols = oBaseQtyCols.ToArray
'List is 1 based (first Item index is 1), Array is 0 based (first Item index is 0)
For Each oRow As PartsListRow In oPList.PartsListRows
'get Cell values corresponding to identified columns
If oBaseQtyCols.Count > 0 Then
For X As Integer = 1 To oBaseQtyCols.Count
If Not oRow.Item(oBaseQtyCols.Item(X)).Static And _
oRow.Item(oBaseQtyCols.Item(X)).Value = "0" Then
oRow.Item(oBaseQtyCols.Item(X)).Value = "-"
End If
Next
End If
If oUnitQtyCols.Count > 0 Then
For X As Integer = 1 To oUnitQtyCols.Count
If Not oRow.Item(oUnitQtyCols.Item(X)).Static And _
oRow.Item(oUnitQtyCols.Item(X)).Value = "0" Then
oRow.Item(oUnitQtyCols.Item(X)).Value = "-"
End If
Next
End If
If oItemQtyCols.Count > 0 Then
For X As Integer = 1 To oItemQtyCols.Count
If Not oRow.Item(oItemQtyCols.Item(X)).Static And _
oRow.Item(oItemQtyCols .Item(X)).Value = "0" Then
oRow.Item(oItemQtyCols.Item(X)).Value = "-"
End If
Next
End If
NextIf this solved your problem, or answered your question, please click ACCEPT SOLUTION.
Or, if this helped you, please click (LIKE or KUDOS) 👍.
If you want and have time, I would appreciate your Vote(s) for My IDEAS :light_bulb: or you can Explore My CONTRIBUTIONS
Wesley Crihfield
(Not an Autodesk Employee)
This looks to be a VB.NET method. Once you have a string just type "." and the dropdown will bring up the list of methods available.
https://docs.microsoft.com/en-us/dotnet/api/system.string.contains?view=net-6.0
The equivalent in VBA is
InStr([ start ], string1, string2, [ compare ])
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/instr-function
Actually I likely have never tried to use this in VBA so I learned something in checking out the origins. As regards case sensitivity I believe both VB.NET and VBA are not case sensitive but I do prefer to keep things uniform regardless.
Glad I could help.
If I'm correct in my inspection of the two resulting codes, I believe you could greatly condense the code and maintain its current functionality. Since you are doing the same end task on every cell that is within any of those quantity type columns, why not just all the columns to the same single List object, using either a Select Case...Case block or If...ElseIf block, then using a single loop later for just that one List object, instead of different loops for 4 different lists. If we do this, we could cut the code down to at least 2/3 its original size.
Sort of like this:
Sub Main
'Define the necessary objects
Dim oDrawDoc As DrawingDocument = ThisDoc.Document
Dim oSheet As Sheet = oDrawDoc.ActiveSheet
Dim oPartsList As PartsList = oSheet.PartsLists.Item(1)
Dim oPLColumn As PartsListColumn
Dim oPLRow As PartsListRow
Dim oPLCell As PartsListCell
Dim oQtyCols As New List (Of PartsListColumn)
'Iterate through every column in the parts list, identify it as a Qty column
'then add it to a list
For Each oPLColumn In oPartsList.PartsListColumns
If oPLColumn.PropertyType = kQuantityPartsListProperty Or _
oPLColumn.PropertyType = kBaseQuantityPartsListProperty Or _
oPLColumn.PropertyType = kItemQuantityPartsListProperty Or _
oPLColumn.PropertyType = kUnitQuantityPartsListProperty Then
oQtyCols.Add(oPLColumn)
End If
Next
'Cycle through each row of the parts list and modify only the BOM columns
'using the list from above and the Cell_Checker subroutine
For Each oPLRow In oPartsList.PartsListRows
If oQtyCols.Count > 0 Then
For i = 0 To oQtyCols.Count - 1
Cell_Checker(oPLRow.Item(oQtyCols.Item(i)))
Next
End If
Next
iLogicVb.UpdateWhenDone = True
End Sub
Public Sub Cell_Checker (oPLCell As PartsListCell)
'Check to see if the cell has not been overriden and is set to 0, if so, replace the value with a "-"
Try
If oPLCell.Static = False And oPLCell.Value = 0 Then
oPLCell.Value = "-"
'Check to see if the underlying assembly quantity is 0 for Static cells and make the change to "-"
'if the assembly quantity is 0. Otherwise, leave the overriden value in place
Else If oPLCell.Static = True
Temp_Val = oPLCell.Value
oPLCell.Static = False
If oPLCell.Value = 0 Then
oPLCell.Value = "-"
Else
oPLCell.Value = Temp_Val
End If
End If
Catch
End Try
End Sub
Wesley Crihfield
(Not an Autodesk Employee)
Better yet. If we go with just checking if the column title contains "Qty", then we can condense it down even further, while still making use of the single List object and still using For Each loops instead of those pesky For I loops. Here is a fairly condensed version using that route.
Sub Main
'Define the necessary objects
Dim oDrawDoc As DrawingDocument = ThisDoc.Document
Dim oSheet As Sheet = oDrawDoc.ActiveSheet
Dim oPartsList As PartsList = oSheet.PartsLists.Item(1)
Dim oQtyCols As New List(Of PartsListColumn)
oQtyCols.AddRange(oPartsList.PartsListColumns.OfType(Of Inventor.PartsListColumn).Where(Function(c) c.Title.Contains("Qty")))
If oQtyCols.Count = 0 Then Exit Sub
For Each oPLRow As PartsListRow In oPartsList.PartsListRows
For Each oQtyCol In oQtyCols
oCell = oPLRow.Item(oQtyCol)
Try
If oCell.Static = False And oCell.Value = "0" Then 'the value is a String
oPLRow.Item(oQtyCol).Value = "-"
Else If oCell.Static = True
Temp_Val = oCell.Value
oCell.Static = False
If oCell.Value = 0 Then
oCell.Value = "-"
Else
oCell.Value = Temp_Val
End If
End If
Catch
End Try
Next
Next
iLogicVb.UpdateWhenDone = True
End Sub
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
