07-10-2020
09:40 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
07-10-2020
09:40 AM
I have provided the updated code below that has fixed all the bugs I was having. I needed to convert some of the cell types from Excel to strings and others to numbers to ensure my looping worked.
Although, taking you suggestion, I may steer away from Excel come the future.
'Public Declarations for use in all functions Dim PartPropertiesFileAddr As String = "W:\Projects-Walters-Wolf\2019\19-04_MSCM_Samm\4-Production_Drawings\19-04_Part_iProperties.xlsm" Dim FastenerPropertyFileAddr As String = "C:\Users\aboaro\Downloads\Fastener_Project_Properties.xlsm" Dim oPartDictionary = New Dictionary(Of String, Dictionary(Of String, Integer)) Dim oAssy As AssemblyDocument = ThisDoc.Document Dim oAsmCompDef As AssemblyComponentDefinition = oAssy.ComponentDefinition Dim List = New String(){"B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T","U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM"} 'Find Max Iterations in Parts Sheet GoExcel.Open(PartPropertiesFileAddr,"Parts") Dim PartsMaxIterations As Integer = GoExcel.CellValue("F1") For Rows = 4 To PartsMaxIterations 'Compare the Material Setting to weed out non-screwed/fastened pieces If GoExcel.CellValue("D" & Rows) = "Aluminum" Then Dim PartName As String = GoExcel.CellValue("A" & Rows) Dim EmptyDictionary As New Dictionary(Of String, Integer) oPartDictionary.Add(PartName, EmptyDictionary) End If Next GoExcel.Close Dim oFastDictionary As New Dictionary(Of String, Integer) GoExcel.Open(FastenerPropertyFileAddr, "Fasteners") Dim oNumFast As Integer = GoExcel.CellValue("J" & 1) Dim oNumExtr As Integer = GoExcel.CellValue("K" & 1) 'Row number matched to specific Extrusion For extrusionID = 4 To oNumExtr ' Holds name of Extrusion in Each Iteration Dim oUnitExtrusion As String = GoExcel.CellValue("A" & extrusionID) ' If the unit has the extrusion checked in excel sheet, then fill out fastener dictionary If oPartDictionary.Keys.Contains(oUnitExtrusion) Then For fastenerID = 2 To List.Length ' Selects Column Letter FastenerCol = List(fastenerID - 2) 'Number of Fasteners FastQuantity = GoExcel.CellValue(FastenerCol & extrusionID) If FastQuantity <> 0 Then ' Grab ID of every Fastener that does have a # of parts Dim ofastenerName As String = CStr(GoExcel.CellValue(FastenerCol & 3)) 'MsgBox(ofastenerName) -> show fastener name 'MsgBox(FastQuantity) -> show Fastener Quantity 'Add fastener to the fastener dictionary for specific part oFastDictionary.Add(ofastenerName, FastQuantity) End If Next ' End Fastener Check oPartDictionary(oUnitExtrusion) = oFastDictionary oFastDictionary = New Dictionary(Of String, Integer) End If 'End searching for the part Next GoExcel.Close 'Loop through the assembly to check how many virtual parts are to be made 'Once the Level 1 Dictionary is created, the FastenerDictionary needs to be generated 'Dim i As Integer = 0 ' Holding Index Variable '' Loop through all the Parts For Each oOcc As ComponentOccurrence In oAsmCompDef.Occurrences 'Only look to check if the part itself is not already virtual If Not TypeOf oOcc.Definition Is VirtualComponentDefinition Then On Error Resume Next Dim identity As Matrix = ThisApplication.TransientGeometry.CreateMatrix oDocFile = oOcc.Definition.Document oDocFileName = oDocFile.displayname 'Set your delimiter Delimiter = InStr(oDocFileName, "-") Description = Left(oDocFileName, Delimiter - 1) MsgBox(Description) ' Check if Part Dictionary contains the part for the model If oPartDictionary.ContainsKey(Description) Then ' If it does have the Part, then get the Fastener dictionary and create the number of virtual parts ' This for loop uses the oOcc to find keys in the map instead of looping through the map MsgBox(oPartDictionary(Description).Keys.Count) For Each oFastenerType As String In oPartDictionary(Description).Keys 'MsgBox(oPartDictionary(Description)(oFastenerType)) For i = 0 To oPartDictionary(Description)(oFastenerType) ' ' Loop through each fastener type within the Fastener dictionary ^ ' ' Virtual Part Parameters FastenerName = oFastenerType & ":" & (i + 1) 'oProp1 = oFastenerType ' 'Create a new component occurrence Dim oNewOcc As ComponentOccurrence oNewOcc = oAsmCompDef.Occurrences.AddVirtual(FastenerName, identity) Dim oCVirtualCompDef As VirtualComponentDefinition oCVirtualCompDef = oNewOcc.Definition 'Create Virtual Component Definition 'Dim oCVirtualCompDef As VirtualComponentDefinition 'oCVirtualCompDef = oNewOcc.Definition 'iProperties.Value(FastenerName & ":1", "Project", "Description") = oProp1 ' i = i + 1 Next Next End If End If Next