12-13-2022
08:29 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
12-13-2022
08:29 AM
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