Macro to build an assembly from a spreadsheet/text with part numbers (using Vault)?

[mpuckettXRFV8]

As the title suggests, I'm wondering if it's possible to build a macro that would read a list of parts from a document of some variety (spreadsheet or text, I could probably work with either) and automatically start an assembly with those parts included.  I've done some searching online and managed to find a couple of pieces of code that are close to what I'm going for, but there is a bit of a snag on them.

My ideal setup would be to have a simple list of part numbers/assemblies in either spreadsheet or text format like below:

123455

SubAssembly4

196745

Part1

Part7

The goal would be to write a macro that would read in that list of parts, search for them in Vault, then import them into an assembly if they are found/exist.  The main issue I have with the code I've found so far is that most snippets want the full path to the part in the text/spreadsheet, so instead of just being Part1, they want it to be "C:\CAD Models\Project5\Part1.ipt".  This is an issue for a couple of reasons.  The first is that I'm trying to use our ERP software to output a BOM from a sales order, and it knows nothing about the files, just the part numbers.  The second is that I would prefer to open them from Vault anyway.  Our vault is also structured in such a way that we have different folders for a range of part numbers, they aren't all collectively in a single folder (so part xyz might be in Vault folder "Parts T-Z" and part abc might be in folder "Parts A-F" for instance, so I need to search all the subfolders in Vault for the part number in the list.

In my head this seems like a very straightforward thing (read in a list of parts and auto-generate an assembly from it), but in practice I'm having a hard time figuring out where to start and if it's even possible with the setup currently in use at my company.  Any advice or ideas regarding this one?

[mat_hijs]

First of all, I have never used Vault, so I have no idea how to incorporate that. That being said, I think you're going to need to make some decisions first.

• What if a component isn't found?
• What if there are multiple files with the same name in different folders?
• What if there is an assembly and a part file with the same name?
• Where in the assembly should your component be placed and in what orientation?
• What view representation or model state should be used if there are multiple?
• Should there be any constraints or will you ground the components in place?
• ...

I have written some code that will read an Excel file, place the components at the specified location with the specified rotation(s) and ground them in place.

Here's a very simple example of what my input may be, these coordinates are calculated using lookups and other formula's.:

In my code I do specify the full path, but as you stated it shouldn't be too hard to make it search in a specific set of folders.

[mpuckettXRFV8]

All fair points and some of those I have considered and was still thinking about.

As far missing components, I fully expect those and would be fine just skipping over them.  Basically the idea would be "if it exists, add it".

Multiple files could be more interesting.  I don't believe we should have those, but I'm sure something could come up.  My knee jerk answer to that would probably be loading the first one it finds and then displaying a message box that states "multiple copies found".  Or maybe it could prompt you first that multiple objects were found and you can pick from the list.  Would have to look into that one some more.

Similar idea with the part/assembly with a common name, though based on what I'm trying to load, I think that should be less likely.  While this could be extended to be a more universal tool, at the moment I'm going for a more focused approach that would just load subassemblies, which have a unique naming setup.

Positioning did come to mind, but the way we build our subassemblies the goal would be that they should all theoretically load in at 0,0,0 and be at the correct visual position, same story with orientations.

View representation/state is an interesting one I didn't think about.  It's a new concept for me with Inventor (I like it the more I use it and think about applications for it), but I think I would probably default to the "default" state for everything to start with.  That would be enough to get things going.

Constraints I have considered as well.  Ideally I would lock all the origin planes of each subassembly together, so automating that would save me time, though if I had to make them manually it wouldn't be the worst thing ever.

For a little more context of the application, I'm working on vehicle design with options and subassemblies.  My end goal is I want to take a sales order that calls for certain options and be able to build a full vehicle model from our subassemblies with a macro.  The subassemblies all have a specific naming convention (part number of the assembly + _00) and are to be built in such a way that when positioned at 0,0,0 at the default orientation (front plane facing forward, etc.), they are correctly position relative to each other.  So all I'm really trying to do is save myself the work of opening up a vehicle BOM and individually loading the subassemblies one by one into a full vehicle assembly.  Bonus points for automatically creating all the constraints to lock the origin planes of each subassembly to the main assembly origin planes, though that is probably a simple enough macro to build separately if needed.

My biggest hurdle at the moment is just trying to figure out how to search a series of subfolders for a single part number within a macro (preferably via Vault).  Actually loading the models into an assembly seems fairly straightforward.  I'm just not sure how to do a "search" within a macro for a part number.

[mat_hijs]

If I can find some time in the next couple of days I'll try to write some code to get you started.

• I will ignore components that were not found, but give a warning so you'll at least know.
• Depending on the number of components and the number of folders and files it may impact performance a lot if you want a warning that multiple components with the same name were found because it means you'll need to go through every file instead of stopping when one was found. For now I'll start by simply using the first found component but this could be changed later.
• I will prioritize subassemblies over parts if they have the same name.
• I will place every component with the Default view representation and the Primary model state.
• If every component always needs to be at 0,0,0 I will ground them in place instead of creating flush constraints since constraints may slow your assembly down considerably. This could also be changed easily afterwards.
• I will ignore the whole Vault thing as I have never used that before.

[mat_hijs · Accepted solution]

I decided to go one step further and actually made it so that you can choose which file you want to use if there's multiple. The Excel file should just have the part numbers in column A, no header. If you uncomment some lines it will also log how many files were found to the iLogic logger. You'll have to change the top level directory name in the code.

I assumed that since every component will be placed at 0,0,0 that there will never be 2 of the same component. If you have 2 rows with the same part number in your Excel file, it will ignore the second one.

Here's the code.

Sub Main
	'[ Select Excel File
	' Select an Excel File
	Dim sExcelFileName As String
	Try
		sExcelFileName = OpenExcelFilesDialog(False).Item(0)
	Catch
		' Show an Error Message
		MessageBox.Show("Error selecting Excel File", iLogicVb.RuleName, MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
		Exit Sub
	End Try
	']
	
	'[ Open Excel File
	' Check if the Excel File exists
	If System.IO.File.Exists(sExcelFileName) Then
		GoExcel.Open(sExcelFileName, "Sheet1")
	Else
		' Show an Error Message
		MessageBox.Show("Excel File doesn't exist", iLogicVb.RuleName, MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
		Exit Sub
	End If
	
	' Set the Title Row
	Dim oTitleRow As Integer = GoExcel.TitleRow
	
	' Set the First Data Row
	Dim oRowStart As Integer = 1
	
	' Declare the Current Data Row
	Dim oRowCurrent As Integer
	
	' Declare the Last Data Row
	Dim oRowEnd As Integer
	
	' Loop through all rows
	For oRowCurrent = oRowStart To 1048576
		' Check if the row is empty (in column A)
		If GoExcel.CellValue("A" & oRowCurrent) = "" Then
			' Set the Last Data Row
			oRowEnd = oRowCurrent - 1
			Exit For
		End If
	Next
	']
	
	'[ Get Part Numbers from Excel
	' Create a New List
	Dim sPartNumbersList As New List(Of String)
	
	' Loop through all Rows
	For oRowCurrent = oRowStart To oRowEnd
		' Get Part Number from Excel
		Dim sPartNumber As String = GoExcel.CellValue("A" & oRowCurrent)
		
		' Add the Part Number to the List if it's not already in there
		If Not (sPartNumbersList.Contains(sPartNumber)) Then sPartNumbersList.Add(sPartNumber)
	Next
	']
	
	'[ Get Files and place Occurrences
	' Set the Top Level Directory Path
	Dim sTopLevelDirectoryPath As String = "C:\Temp"
	
	' Loop through all Part Numbers
	For Each sPartNumber As String In sPartNumbersList
		Dim sFilePath As String = ""
		Dim sFilePaths As String() = System.IO.Directory.GetFiles(sTopLevelDirectoryPath, sPartNumber & ".*", System.IO.SearchOption.AllDirectories)
		
		Select Case sFilePaths.Length
			Case 0
				' Show a Warning Message
				MessageBox.Show("No files with Part Number """ & sPartNumber & """ were found, the component will not be placed", iLogicVb.RuleName, MessageBoxButtons.OK, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1)
				'Logger.Info(sPartNumber & ": 0 found <==")
				sFilePath = ""
			Case 1
				' Show a Warning Message
				'MessageBox.Show("Exactly one file with Part Number " & sPartNumber & " was found, the component will be placed", iLogicVb.RuleName, MessageBoxButtons.OK, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1)
				'Logger.Info(sPartNumber & ": 1 found")
				sFilePath = sFilePaths(0)
			Case >1
				' Show a Warning Message
				'MessageBox.Show("More than one files with Part Number """ & sPartNumber & """ were found, the first found component will be placed", iLogicVb.RuleName, MessageBoxButtons.OK, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1)
				'Logger.Info(sPartNumber & ": " & sFilePaths.Length & " found <==")
				
				' Create a List
				Dim sFoundFilePathsList As New List(Of String)
				
				' Add the Found File Paths to the List
				For Each sFoundFilePath As String In sFilePaths
					sFoundFilePathsList.Add(sFoundFilePath)
				Next
				
				' Select the Diameter
				sFilePath = InputListBox("Choose a File Path", sFoundFilePathsList, sFoundFilePathsList.Item(0), Title := sPartNumber, ListName := "Found File Paths")
		End Select
		
		If sFilePath <> "" Then PlaceOccurrence(sFilePath)
	Next
	']
	
	'[ Update and Close
	' Update the Document
	InventorVb.DocumentUpdate()
	
	' Close the Excel File
	GoExcel.Close
	']
End Sub

Function OpenExcelFilesDialog(Optional ByVal bMultiSelect As Boolean = False) As List(Of String)
    Dim sResult As List(Of String) = Nothing

    Try
        Dim oOpenFileDialog As New OpenFileDialog

        With oOpenFileDialog
            .Filter = "Excel Files (*.xls, *.xlsx, *.xlsm)|*.xls;*.xlsx;*.xlsm|All Files (*.*)|*.*"
            .FilterIndex = 1
            .Title = "Select Excel File"
            .Multiselect = bMultiSelect
        End With

        If bMultiSelect = True Then oOpenFileDialog.Title = "Select Excel Files"

        If oOpenFileDialog.ShowDialog = System.Windows.Forms.DialogResult.OK Then
            Try

            Catch oFileException As Exception
                Throw oFileException
            End Try
        End If

        Dim oTempList As New List(Of String)
        Dim oFile As String = Nothing
        For Each oFile In oOpenFileDialog.FileNames
            oTempList.Add(oFile)
        Next

        sResult = oTempList
    Catch oException As Exception
		MessageBox.Show(oException.ToString, iLogicVb.RuleName, MessageBoxButtons.OK, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1)
    End Try

    Return sResult
End Function

Function PlaceOccurrence(ByVal sFilePath As String) As ComponentOccurrence
	Dim oResult As ComponentOccurrence

	' Set a reference to the Assembly Document
	Dim oAsmDoc As AssemblyDocument = ThisApplication.ActiveDocument

	' Set a reference to the Assembly Component Definition
	Dim oAsmCompDef As AssemblyComponentDefinition = oAsmDoc.ComponentDefinition

	' Create a Matrix
	Dim oMatrix As Matrix = ThisApplication.TransientGeometry.CreateMatrix()

	' Set a reference to the Component Occurrences
	Dim oOccurrences As ComponentOccurrences = oAsmCompDef.Occurrences

	' Place a Component Occurrence
	Dim oOcc As ComponentOccurrence = oOccurrences.Add(sFilePath, oMatrix)

	' Ground the Component Occurrence
	oOcc.Grounded = True

	' Set the Default View Representation if the Component Occurrence is an Assembly
	If oOcc.DefinitionDocumentType = DocumentTypeEnum.kAssemblyDocumentObject Then oOcc.SetDesignViewRepresentation("Default", , True)

	' Return the Component Occurrence
	oResult = oOcc

	Return oResult
End Function

[mpuckettXRFV8]

Awesome, thanks!  I'll get it loaded up and try some tests with it to see what I can come up with.  I know for the time being I can probably make it load from local folders for testing.  Vault more or less copies stuff to a local folder on the C:\ drive for me, so that can get me started.  Long term I'll have to see if I can get it working as I believe the files don't exist on the local drive until the first time you pull them from Vault, so if it's a subassembly I've never opened before it might miss it.  This should get me started pretty good though and to the point I can start playing and tweaking it.  Thanks agian!

[mpuckettXRFV8]

I'm getting an error on run that's not even getting into the main routine to start with.  The dialog box just says "Compile error: Sub or function not defined" and it's highlighting the very first Try statement.

I also realized I never mentioned what version of Inventor I'm running, assuming that makes a difference.  We're currently on Inventor Professional 2024.  I installed the matching VBA plugin from Autodesk as well, so I'm pretty sure it should be ready to go, but entirely possibly I'm missing something.

[mat_hijs]

This should work in Inventor 2024. However, this code was written for iLogic, not for VBA. Converting this to VBA would take some time, but I see no reason to use VBA over iLogic, especially since we can now add iLogic rules to the ribbon as buttons. Try creating an external iLogic rule and pasting this code in there. Let me know if you still have issues.

[mpuckettXRFV8]

Ah, okay, easy enough.  I was mostly just thinking VBA at the time because a macro made more sense than a "rule", though you can manually trigger either, so not a big deal.  It does run now, though I did get an error.  I think I see what's going on, but haven't dug into it yet:

Looks like it's trying to compare the value in an Excel cell to blank to determine when the list is over, but not sure where it's getting the double from (unless I'm looking at the wrong line of code).  Not sure if comments count as lines as far as the debugger is concerned.

[mpuckettXRFV8 · Accepted solution]

Did a light tweak to the If statement and got it running.  I changed the if statement to the following:

If String.IsNullOrEmpty(GoExcel.CellValue("A" & oRowCurrent)) Then

That seemed to work fine and get it going, so will keep playing with it and tweaking it to get what I'm going for.  Thanks again for laying the groundwork for this!  Easy enough to follow along in the code, so it makes enough sense to me. 

[mat_hijs]

I'm not sure why this wasn't an issue for me but I'm glad you found the solution.