Hello guys, how are you doing?
I need do a customized TXT file create with some data the Part List and some data from Sheet.
Our ERP software read this TXT and register the Assemby in our database.
The final TXT result must be like red lines below:
C 100000-GS-005 100000-GS-005 5 Conjunto placas CJ
E 100000-GS-005 100000-GS-056 01
E 100000-GS-005 100000-GS-055 01
The columns where each text starts must be column 1, column 3, column 18, column 33, column 71.
Letter "C" means that this line is an Assembly
Letter "E" means that is a part.
Number "5" in the first line not change.
The number "01" in the lines 2 and 3, means the quantity of each part on the assembly.
Any comments, sugestions and questions will be welcome.
Than you.
Ladimir Abdala
BRAZIL
Hi,
you can use next VBA code. You still need to add the assy info and export to txt file (isn't hard to do). You also need to change the delimiter I guess (ERP probably expects comma, tab, fixed width or other delimiter). Just give it a try.
Have fun
Public Sub BOMQuery()
' Set a reference to the assembly document.
' This assumes an assembly document is active.
Dim oDoc As AssemblyDocument
Set oDoc = ThisApplication.ActiveDocument
Dim FirstLevelOnly As Boolean
If MsgBox("First level only?", vbYesNo) = vbYes Then
FirstLevelOnly = True
Else
FirstLevelOnly = False
End If
' Set a reference to the BOM
Dim oBOM As BOM
Set oBOM = oDoc.ComponentDefinition.BOM
' Set whether first level only or all levels.
If FirstLevelOnly Then
oBOM.StructuredViewFirstLevelOnly = True
Else
oBOM.StructuredViewFirstLevelOnly = False
End If
' Make sure that the structured view is enabled.
oBOM.StructuredViewEnabled = True
'Set a reference to the "Structured" BOMView
Dim oBOMView As BOMView
Set oBOMView = oBOM.BOMViews.Item("Structured")
Debug.Print "Item"; Tab(15); "Quantity"; Tab(30); "Part Number"; Tab(70); "Description"
Debug.Print "----------------------------------------------------------------------------------"
'Initialize the tab for ItemNumber
Dim ItemTab As Long
ItemTab = -3
Call QueryBOMRowProperties(oBOMView.BOMRows, ItemTab)
End Sub
Private Sub QueryBOMRowProperties(oBOMRows As BOMRowsEnumerator, ItemTab As Long)
ItemTab = ItemTab + 3
' Iterate through the contents of the BOM Rows.
Dim i As Long
For i = 1 To oBOMRows.Count
' Get the current row.
Dim oRow As BOMRow
Set oRow = oBOMRows.Item(i)
'Set a reference to the primary ComponentDefinition of the row
Dim oCompDef As ComponentDefinition
Set oCompDef = oRow.ComponentDefinitions.Item(1)
Dim oPartNumProperty As Property
Dim oDescripProperty As Property
If TypeOf oCompDef Is VirtualComponentDefinition Then
'Get the file property that contains the "Part Number"
'The file property is obtained from the virtual component definition
Set oPartNumProperty = oCompDef.PropertySets _
.Item("Design Tracking Properties").Item("Part Number")
'Get the file property that contains the "Description"
Set oDescripProperty = oCompDef.PropertySets _
.Item("Design Tracking Properties").Item("Description")
Debug.Print Tab(ItemTab); oRow.ItemNumber; Tab(17); oRow.ItemQuantity; Tab(30); _
oPartNumProperty.Value; Tab(70); oDescripProperty.Value
Else
'Get the file property that contains the "Part Number"
'The file property is obtained from the parent
'document of the associated ComponentDefinition.
Set oPartNumProperty = oCompDef.Document.PropertySets _
.Item("Design Tracking Properties").Item("Part Number")
'Get the file property that contains the "Description"
Set oDescripProperty = oCompDef.Document.PropertySets _
.Item("Design Tracking Properties").Item("Description")
Debug.Print oCompDef.Document.FullFileName
Debug.Print Tab(ItemTab); oRow.ItemNumber; Tab(17); oRow.ItemQuantity; Tab(30); _
oPartNumProperty.Value; Tab(70); oDescripProperty.Value
'Recursively iterate child rows if present.
If Not oRow.ChildRows Is Nothing Then
Call QueryBOMRowProperties(oRow.ChildRows, ItemTab)
End If
End If
Next
ItemTab = ItemTab - 3
End Sub
This is what I use. This gets called from iLogic and reads the parts list thenwrites the information in a matrix of hidden iProperties. I then write the properties to excel using iLogic. This code hides detail parts and excludes them from the matrix because I use another macro that handles them a little differently (reads from the title blocks). I then have an excel macro that formats the information for printing and uploading to our ERP. The previous posters solution is probably a lot simpler but I need a lot of data to be written in multiple places. I can supply the other modules (including the excel part) if you think this is more of what you need.
Public Sub PartsListQuery() 'This sub is designed to read stock part lists and write them to a drawings iProperties in an indexed format (i.e. 1,1 = qty per asm for part 1) ' Set a reference to the drawing document. ' This assumes a drawing document is active. Dim oDrawDoc As DrawingDocument Set oDrawDoc = ThisApplication.ActiveDocument Dim invCustomPropertySet As PropertySet Dim invCustomPropertySetC As PropertySet Set invCustomPropertySetC = oDrawDoc.PropertySets.Item("User Defined Properties") ' Dim oPropSets As PropertySets ' Set oPropSets = oDrawDoc.PropertySets ' Call oPropSets.Add("AutoBOM") Dim rowNum As Integer Dim oPartList As PartsList Set oPartList = oDrawDoc.activeSheet.PartsLists.Item(1) Dim PartCount As Integer Dim invProperty As Property Dim oRow As PartsListRow Dim oCell As PartsListCell Dim partQty As Integer Dim i As Integer Dim j As Integer Dim seq As String If ThisApplication.ActiveDocument.DocumentType <> kDrawingDocumentObject Then MsgBox ("You must have a drawing document active") Exit Sub End If 'oPartList.Sort ("STOCK SEQUENCE NUMBER") 'oPartList.Renumber 'oPartList.SaveItemOverridesToBOM On Error GoTo code: Call CreateAutoBOMPropertySet Set invCustomPropertySet = oDrawDoc.PropertySets.Item("AutoBOM") GoTo code2: code: 'MsgBox ("1") On Error GoTo 0 Set invCustomPropertySet = oDrawDoc.PropertySets.Item("AutoBOM") Err.Clear 'MsgBox ("2") code2: On Error GoTo noList: PartCount = oPartList.PartsListRows.count If PartCount = 0 Then PartCount = MsgBox("Parts list is empty!", vbCritical, "Error") Exit Sub End If 'MsgBox (PartCount) rowNum = 0 partQty = 0 'On Error GoTo noProp: 'On Error GoTo 0 On Error Resume Next For i = 1 To PartCount ' Get the current row. Set oRow = oPartList.PartsListRows.Item(i) Set oCell = oRow.Item(2) 'MsgBox ("4i" & i) If oCell.Value = "STK" Or oCell.Value = "FA" Or oCell.Value = "PP" Then oRow.Visible = True rowNum = rowNum + 1 ' Iterate through each column in the row. For j = 1 To 10 ' Get the current cell. Set oCell = oRow.Item(j) ' MsgBox ("i" & i & "5j" & j) Set invProperty = invCustomPropertySet.Add(oCell.Value, rowNum & "," & j) ' invCustomPropertySet.Item(rowNum & "," & j).Value = oCell.Value ' MsgBox ("i" & i & "6j" & j) Next j ElseIf oCell.Value <> "" Then Set oCell = oRow.Item(10) seq = oCell.Value ' MsgBox ("5i" & i) Set oCell = oRow.Item(1) ' partQty = partQty + 1 ' MsgBox ("6i" & i) Set invProperty = invCustomPropertySet.Add(oCell.Value, seq & " qty") invCustomPropertySetC.Item(seq & " qty").Value = oCell.Value ' MsgBox ("7i" & i) Set invProperty = invCustomPropertySet.Add(oCell.Value, seq & " qty") invCustomPropertySet.Item(seq & " qty").Value = oCell.Value oRow.Visible = False ' MsgBox ("8i" & i) Else oRow.Visible = False End If Next i 'On Error GoTo noProp2: 'On Error GoTo 0 On Error Resume Next invCustomPropertySet.Item("partQty").Value = rowNum 'invCustomPropertySet.Item("partCount").Value = PartCount Set invProperty = invCustomPropertySet.Add(rowNum, "partQty") 'Set invProperty = invCustomPropertySet.Add(PartCount, "partCount") Exit Sub noList: MsgBox ("parts list not found!") Exit Sub noProp: MsgBox ("Custom properties not found!") Exit Sub noProp2: MsgBox ("Custom properties not found! 2nd section.") End Sub