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