Announcements
Attention for Customers without Multi-Factor Authentication or Single Sign-On - OTP Verification rolls out April 2025. Read all about it here.
justinparfitt
in reply to: A.Acheson

Thank you very much for your help. Below is the final code.

 

Sub main
' set a reference to the assembly component definintion.
' This assumes an assembly document is open.
'----------change BOM normal to phantom for parts with description NOT ON BOM  
Dim oAsmCompDef As AssemblyComponentDefinition
oAsmCompDef = ThisApplication.ActiveDocument.ComponentDefinition

'Iterate through all of the occurrences

Dim CompOcc As ComponentOccurrence
For Each CompOcc In oAsmCompDef.Occurrences

If CompOcc.Definition.Document.PropertySets.Item("Design Tracking Properties").Item("Description").Value = "NOT ON BOM" Then
CompOcc.Definition.BOMStructure = 51971 'Phantom
Else
End If
Next


oDoc = ThisApplication.ActiveDocument
Dim oBOM As BOM
oBOM = oDoc.ComponentDefinition.BOM
'==========================================================================================
'You can change the output path by editing oPATH below
oPATH = PROJECT_PATH & JOB_NO & " - " & CLIENT & " - " & SITE & "\" 'If you change this, remember to keep a \ at the end

'==========================================================================================
'STRUCTURED BoM ===========================================================================
' the structured view to 'all levels'
oBOM.StructuredViewFirstLevelOnly = False
' Make sure that the structured view is enabled.
oBOM.StructuredViewEnabled = True
Dim oStructuredBOMView As BOMView
oStructuredBOMView = oBOM.BOMViews.Item("Structured")
' Export the BOM view to an Excel file
oStructuredBOMView.Export(oPATH + ThisDoc.FileName(False) + ".xls", kMicrosoftExcelFormat)






'==========================================================================================
'PARTS ONLY BoM ===========================================================================
'' Make sure that the parts only view is enabled.
'oBOM.PartsOnlyViewEnabled = True
'Dim oPartsOnlyBOMView As BOMView
'oPartsOnlyBOMView = oBOM.BOMViews.Item("Parts Only")
'' Export the BOM view to an Excel file
'oPartsOnlyBOMView.Export (oPATH + "BOM-PartsOnly.xls", kMicrosoftExcelFormat)
'==========================================================================================
'BOM to SQL
Dim row As Integer = 5
row = 5
Dim oBOMView As BOMView
oBOMView = oBOM.BOMViews.Item("Structured")

Dim PartNumber, Qty, StockNumber, Description As String
Dim bRow As BOMRow
bRows = oBOMView.BOMRows
	For Each bRow In bRows
	
	Dim rDoc As Document
	rDoc = bRow.ComponentDefinitions.Item(1).Document
	
	Dim docPropertySet As PropertySet
	docPropertySet = rDoc.PropertySets.Item("Design Tracking Properties")
	
	StockNumber = bRow.ItemNumber
	Qty = bRow.ItemQuantity
	Description = docPropertySet.Item("Description").Value
	PartNumber = docPropertySet.Item("Part Number").Value
	row = row + 1
	Dim sConn As String = String.Empty
	sConn="Provider=SQLOLEDB;Data Source=xxxxx;Initial Catalog=xxxx;User ID=xxxxx;Password=xxxx;Connect Timeout=30;Encrypt=True;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"

	'Enter Tablename and values to insert
	'If there are less values than columns in your table, you need to specify wich value corresponds to which column
	Dim sql As String = String.Empty
	sql = "INSERT INTO InventorBOM1 (JOB_NO,PROJECT_ID,ITEM,Qty,Description,StockNumber) VALUES ('"& JOB_NO &"','"& PROJECT_ID &"','"& PartNumber &"','"& Qty &"','"& Description &"','"& StockNumber &"')"
	
	ExtSql(sConn, sql) 
		MsgBox("Command executed", MsgBoxStyle.Information, "iLogic")
	Next

	
	
	
End Sub


Function ExtSql(ByVal sConn As String, ByVal sql As String) As Boolean
    Dim cnn As OleDbConnection
    Dim cmd As OleDbCommand
    cnn = New OleDbConnection(sConn)
    Try
        cnn.Open()
        cmd = New OleDbCommand
        cmd.Connection = cnn
        cmd.CommandType = System.Data.CommandType.Text
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        cnn.Close()
        cmd.Dispose()
    Catch ex As Exception
        cnn.Close()
        Return False
    End Try
    Return True
End Function