VBA programming in Microsoft Access, referencing Apprentice, to extract BoM data, roadblock

[mcmadmac]

I feel like I'm sitting in the driver's seat of a powerful and well appointed car. I've managed to start the engine, but the gears are hidden, in plain sight.

The challenge: without Inventor installation, merge the BOM data from several hundred assembly and fabrication drawings with data in a Microsoft Access application.

My current version of the solution is to install Inventor View (installed inventor View 1.0 Build 287, Release 2022.2.1) which includes Apprentice components and write VBA code in Access to batch extract data. The data should come in the same format that Inventor uses when exporting structured BoM to Excel, less the thumbnails.

So far I am able to open an Assembly

as follows, with a couple rudimentary checks to see that it is working, with the exception as noted

Something is amiss - the runtime error states the Name property of ComponentOccurrence is a method, Which makes no sense, unless one of you knows something that I can't put my finger on.

Function OpenFileInApprentice(strfilepath As String) As Boolean

Dim osvr As New Inventor.ApprenticeServerComponent
Dim odoc As Inventor.ApprenticeServerDocument
Dim colOccurences As ComponentOccurrences
Dim oCompOccurrence As Inventor.ComponentOccurrence
    	Set odoc = osvr.Open(strfilepath)
    	Debug.Print odoc.DocumentType
    	Debug.Print odoc.DisplayName
	Debug.Print "Occurences: "; colOccurences.Count
	    For Each oCompOccurrence In colOccurences
	        ' Display information about the current component.
	        Debug.Print oCompOccurrence.Name  ' generates run time error - Method 'Name' of object ComponentOccurrence failed
	    Next

' routine practice - release all open objects

set oCompOccurrence = Nothing
set colOccurences = Nothing
Set odoc = Nothing
Set osvr = Nothing

End Function

Above code returns
 12291 
AM22P3006.iam
Occurences:  25 

[A.Acheson]

Not a big user of the apprentice so hopefully someone can correct me if I'm wrong but I don't think there is any access to the Inventor BOM object through the apprentice API. All that is accessible is iproperties. The Inventor API has access to the BOM Object where you would do an export etc.

Here is the short help file on apprentice

[mcmadmac]

I can't prove yet whether A.Acheson is right or wrong with respect to what Apprentice can do. I have however read in other posts both within this forum and in the blogs that Apprentice has the means to read BOM datat - e.g., https://modthemachine.typepad.com/my_weblog/training-material/.

[A.Acheson]

I think I remembered incorrectly. I wasn't able to access the BOM export method. It seems the export does not happen, perhaps it isn't supported or is incorrectly accessed. Again hopefully others can check this as it would be the easiest method to extract the BOM.

Alternatively you can access the BOMViews then BOMRows rows and extract those line by line. The below code is written to be run as a  vb.script. It is the same method to how you would do this in VBA/ VB.Net. Here is the link to the BOM API VBA Sample. Hope this helps your progress.

Dim filename 

Call GetInformation

Sub GetInformation()
	
   filename = InputBox("Input FileName & Path & ExtTo Search For Information", "Enter Path Information", "C:\Users\Desktop\WFH\Samples\Bom Export\Bom Export.iam")
    If filename = "" Then
        WScript.Quit
    End If

    Dim invApprenticeApp
    Set invApprenticeApp = CreateObject("Inventor.ApprenticeServer")

    Dim invApprenticeDoc
    Set invApprenticeDoc = invApprenticeApp.Open(filename)

    Dim oFM
    Set oFM = invApprenticeApp.FileManager
    
    Dim oMainFile
    Set oMainFile = oFM.Files(filename)

    If oFM Is Nothing Then
        Exit Sub
    End If

    PartNumber = invApprenticeDoc.PropertySets.Item("Design Tracking Properties").Item("Part Number").Value
    
    'MsgBox (PartNumber)
  	Call BOMExport(invApprenticeDoc)
    invApprenticeApp.Close
    Set invApprenticeApp = Nothing
        
End Sub

Sub BOMExport(invApprenticeDoc)
	' Set a reference to the BOM
	Dim oBOM 
	Set oBOM = invApprenticeDoc.ComponentDefinition.BOM
 
	MsgBox (oBOM.RequiresUpdate)
	
	'Set a reference to the "Structured" or "Parts Only" BOMView

 	Dim oBOMView 
	Set oBOMView = oBOM.BOMViews.Item("Parts Only")
   	
 	Dim oBOMRows
	Set oBOMRows = oBOMView.BOMRows
	
	Dim oRow 
	Dim row 
	
	For row = 1 To oBOMRows.Count 
	    
	    ' Get the current row.
	    Set oRow = oBOMRows.Item(Row)

	    'Set a reference to the primary ComponentDefinition of the row
	    Dim oCompDef
		Set oCompDef = oRow.ComponentDefinitions.Item(1)

        'Get the file property that contains the "Part Number"
        'The file property is obtained from the parent document of the associated ComponentDefinition.
        Dim oPartNumProperty 
		Set oPartNumProperty = oCompDef.Document.PropertySets.Item("Design Tracking Properties").Item("Part Number")

        'Get the file property that contains the "Description"
		Dim oDescripProperty
        Set oDescripProperty = oCompDef.Document.PropertySets.Item("Design Tracking Properties").Item("Description")

		MsgBox ("RowItem#: " & oRow.ItemNumber & _
				vbCrLf & "oPartNumProperty : " & oPartNumProperty.Value & _
				vbCrLf & "oDescripProperty : " & oDescripProperty.Value & _
				vbCrLf & "Total Qty : " & oRow.TotalQuantity)
	Next
        
        'Not working. is the export supported?
	'Dim myXLS_File
	'myXLS_File = "C:\Users\Desktop\WFH\Samples\Bom Export\BOM-PartsOnly.xlsx"
	'oBOMView.Export myXLS_File, kMicrosoftExcelFormat

  End Sub

[mcmadmac]

I get "Automation error"  in the line setting reference to a ComponentDefinition or trying anything that references ComponentDefinitions.

eg same automation error (-2147467259) on the line...

Debug.Print oRow.ComponentDefinitions.Count

I can see that there are 3 from the line Debug.Print oRow.ComponentOccurrences.Count

But then the line Debug.Print oRow.ComponentOccurrences.Item(1).Name produces the same automation error.

I can test and get results for the number of rows in the BOMRows Enumerator.

I can see that BOM has two views, "Unnamed" and "Structured"

I am not ready to throw in the towel on this.  I remain somewhat confused.

[A.Acheson]

Can you post the code your getting the errors in? The version I posted is very simular to the API sample.

[mcmadmac]

I 've posted, and reposted the code. When I return to the forum it not there.

[A.Acheson]

Maybe you can edit one of your existing posts. and insert it in there. 

[mcmadmac]

I think I forgot to select the language before hitting post. When it didnt show I thought it was a refresh or vetting delay