Exporting Assembly Structure to Excel

There was a recent question in the newsgroup about exporting the structure of an assembly to an Excel spreadsheet.  I’ve written a small VBA program that does this.  When executed it will prompt for a filename and then write the structure of the currently open assembly to the file.  The file created is a CSV file, which can be opened directly by Excel.  For each occurrence in the assembly it writes one row, with the first column containing the name of the occurrence as seen in the browser, and the second column containing the full filename of the part or assembly represented by that occurrence.  The data is written such that the structure of the assembly is preserved, which can be seen below.

It’s likely everyone using this will want different information or a different format for the output.  This is a good time to get your feet wet and learn a little bit about programming Inventor so you can make the small changes to have it write out the information you want.

For those of you unfamiliar with VBA here are the steps to using this program:

1. Open the VBA editor within Inventor (Alt-F11 or Tools –> VBA Editor). 
2. In the project tree, find the project named ApplicationProject
3. Expand the Modules folder.
4. Double click on the module named Module1.  This should open up the code window.
5. Copy and paste the code below into the code window.  If there’s some code already there, paste it at the bottom of any existing code.  The result is shown below.

Once pasted, you can run the program.  This posting describes a few ways to do that.

Here’s the code to copy and paste:

Public Sub WriteAssemblyStructure()         ' Check that an assembly is open.             Dim asmDoc As AssemblyDocument          Dim docType As DocumentTypeEnum          docType = ThisApplication.ActiveDocumentType          If docType  kAssemblyDocumentObject Then              MsgBox "An assembly must be active."              Exit Sub          Else              Set asmDoc = ThisApplication.ActiveDocument          End If                ' Get the name of the file to create.              Dim csvFilename As String           Dim saveDialog As FileDialog           Call ThisApplication.CreateFileDialog(saveDialog)           With saveDialog               .Filter = "CSV (Command delimited)(*.csv)|*.csv"               .DialogTitle = "Specify output filename"               .OptionsEnabled = False               .SuppressResolutionWarnings = True               .ShowSave                    If .FileName = "" Then                   Exit Sub               Else                   csvFilename = .FileName               End If           End With                ' Open the file.              Dim FilePointer As Integer           FilePointer = FreeFile           Open csvFilename For Output As #FilePointer                 ' Write the top-level assembly name.              Print #FilePointer, asmDoc.DisplayName & "," & _                asmDoc.FullFileName                 ' Read through the assembly and write the structure out.              Call WriteStructure(asmDoc.ComponentDefinition.occurrences, _                              1, FilePointer)                ' Close the file.      Close #FilePointer                 MsgBox "Completed writing assembly structure to: " & csvFilename       End Sub                        Private Sub WriteStructure( _                  ByVal occurrences As ComponentOccurrences, _                  ByVal Level As Integer, _                  ByVal FilePointer As Integer)                 ' Iterate through the occurrences in the current level.              Dim occ As ComponentOccurrence           For Each occ In occurrences               ' Write information for this occurrence to the file.                  Print #FilePointer, String$(Level, ",") & _                    occ.Name & "," & occ.Definition.Document.FullFileName                     ' If this occurrence is a subassembly iterate                  ' over its occurrences.                   If occ.DefinitionDocumentType = kAssemblyDocumentObject Then                   Call WriteStructure(occ.SubOccurrences, Level + 1, _                                      FilePointer)               End If           Next       End Sub