Catia V5 R19 - Macro: How to Create BOM - Balloons with references related to the all part
Catia V5 R19 - Macro: How to Create BOM - Balloons with references related to the all part
(OP)
Hi :)
I'm new here. I'm sorry if I made english mistakes.
The macro I want to create is not exactly easy to explain.
starting from a product launch the macro to the BOM.
the exel file has two sheets.
the first sheet contains the components of construction with materials, dimension ecc.
the second sheet contains the components of purchase
all components have a reference number created by the macro, but this sequential numbering is no good to me.
I would like to submit a precise numbering in any part of the product, so that these numbers will appear directly in the BOM when starting the Macro.
Is there anyone who can help me
I'm new here. I'm sorry if I made english mistakes.
The macro I want to create is not exactly easy to explain.
starting from a product launch the macro to the BOM.
the exel file has two sheets.
the first sheet contains the components of construction with materials, dimension ecc.
the second sheet contains the components of purchase
all components have a reference number created by the macro, but this sequential numbering is no good to me.
I would like to submit a precise numbering in any part of the product, so that these numbers will appear directly in the BOM when starting the Macro.
Is there anyone who can help me





RE: Catia V5 R19 - Macro: How to Create BOM - Balloons with references related to the all part
I'm not sure that I understand what you need.
The excel file is created or used by your macro.
Please explain and show some code of your macro.
-GEL
Imposible is nothing.
RE: Catia V5 R19 - Macro: How to Create BOM - Balloons with references related to the all part
I want the macro to give me precise numbers of references,
but do not know how to give precise numbers for individual part of product
the code of your macro is this:
Language="VBSCRIPT"
Sub CATMain()
Set productDocument1 = CATIA.ActiveDocument
Set product1 = productDocument1.Product
Set assemblyConvertor1 = product1.GetItem("BillOfMaterial")
Dim arrayOfVariantOfBSTR1(10)
arrayOfVariantOfBSTR1(0) = "Number"
arrayOfVariantOfBSTR1(1) = "Quantity"
arrayOfVariantOfBSTR1(2) = "Nomenclature"
arrayOfVariantOfBSTR1(3) = "Source"
arrayOfVariantOfBSTR1(4) = "Part Number"
arrayOfVariantOfBSTR1(5) = "Product Description"
arrayOfVariantOfBSTR1(6) = "Note"
arrayOfVariantOfBSTR1(7) = "Dimensioni"
arrayOfVariantOfBSTR1(8) = "Materiale"
arrayOfVariantOfBSTR1(9) = "Stato"
arrayOfVariantOfBSTR1(10) = "Peso"
assemblyConvertor1.SetSecondaryFormat arrayOfVariantOfBSTR1
Dim mydir
mydir = CATIA.ActiveDocument.Path
'msgbox mydir
Dim NomePartDocument
NomePartDocument=productDocument1.Name
NomePartDocumentX = Replace(NomePartDocument, ".CATProduct", "")
Dim NomeFile As String
NomeFile="Distinta" + "_" + NomePartDocumentX + ".xls"
sFileOutput = mydir + "\" +NomeFile
assemblyConvertor1.Print "XLS", sFileOutput, product1
StartExcel(sFileOutput)
End Sub
Sub StartEXCEL(sFileOutput)
'//---------------------------------------------------------------------------
'// Default path of the excel file template
'//---------------------------------------------------------------------------
'strCATCommandPath = mydir
'excelTemplate = NomeFile
excelTemplatePath =sFileOutput
Err.Clear
On Error Resume Next
Set excel = GetObject (,"EXCEL.Application")
If Err.Number <> 0 Then
Err.Clear
'
'Set oFileOutput = CATIA.FileSystem.CreateFile(sFileOutput, True)
'
'Set oStream = oFileOutput.OpenAsTextStream("ForWriting")
'oStream.Close
''
'
'Dim oFilOu As File
' Set oFilOu = CATIA.FileSystem.GetFile(sFileOutput)
' Set oStream = oFilOu.OpenAsTextStream("ForReading")
'
'
Set excel = CreateObject ("EXCEL.Application")
End If
excel.Application.Visible = TRUE
set workbooks = excel.Application.WorkBooks
set workbook = workbooks.Add(excelTemplatePath)
If Err.Number <> 0 Then
Dim strMessage
strMessage = "Error Loading Template File:" + excelTemplatePath + Chr(13)
strMessage = strMessage + Chr(13) + "Check the following...." + Chr(13)
strMessage = strMessage + "(1)Template File has read-write capability" + Chr(13)
strMessage = strMessage + "(2)Template File path is valid"
msgbox (strMessage)
End If
set sheets = workbook.Worksheets
set sheet = sheets("Foglio1")
dim CCC
CCC=1
dim erased
erased=0
dim control
control=0
Riga=1
Colonna=1
Testo=sheet.Cells(Riga, Colonna)
TextCorrect As String
TextCorrect="Number"
Do Until (control=1)
If (Testo=TextCorrect) Then
control =1
Else
Riga=Riga+1
Testo=sheet.Cells(Riga, Colonna)
End If
Loop
Do Until(CCC=Riga)
sheet.Rows("1:1").Delete
CCC=CCC+1
Loop
'#########################
'cambiare i nomi con le diciture in Italiano#
'#########################
'sheet.Cells("1", "1")
'Replace(NomePartDocument, ".CATProduct", "")
'###############
'Rinomina il primo foglio #
'###############
Sheets("Foglio1").Select
Sheets("Foglio1").Name = "Parameters Verify"
'######################
'verifica numero elementi in distinta #
'######################
righrow=2
rightcolumn=2
righttext= Sheets("Parameters Verify").Cells(righrow, rightcolumn)
billnumber=0
Do Until(righttext = "")
billnumber=billnumber+1
righrow=righrow+1
righttext= Sheets("Parameters Verify").Cells(righrow, rightcolumn)
'msgbox righttext
loop
'msgbox billnumber
sheet.Rows("1:1").Font.Bold = True
sheet.Columns("A:A").EntireColumn.AutoFit
sheet.Columns("B:B").EntireColumn.AutoFit
sheet.Columns("C:C").EntireColumn.AutoFit
sheet.Columns("D:D").EntireColumn.AutoFit
sheet.Columns("E:E").EntireColumn.AutoFit
sheet.Columns("F:F").EntireColumn.AutoFit
sheet.Columns("G:G").EntireColumn.AutoFit
sheet.Columns("H:H").EntireColumn.AutoFit
sheet.Columns("I:I").EntireColumn.AutoFit
sheet.Columns("J:J").EntireColumn.AutoFit
sheet.Columns("K:K").EntireColumn.AutoFit
Sheets("Parameters Verify").Cells(1,1)="RIF."
Sheets("Parameters Verify").Cells(1,2)="Q.TA"
Sheets("Parameters Verify").Cells(1,3)="DENOMINAZIONE"
Sheets("Parameters Verify").Cells(1,4)="PROVENIENZA"
Sheets("Parameters Verify").Cells(1,5)="DIMENSIONI"
Sheets("Parameters Verify").Cells(1,6)="FORNITORE"
Sheets("Parameters Verify").Cells(1,7)="NOTE"
Sheets("Parameters Verify").Cells(1,8)="DIMENSIONI"
Sheets("Parameters Verify").Cells(1,9)="MATERIALE"
Sheets("Parameters Verify").Cells(1,10)="STATO"
Sheets("Parameters Verify").Cells(1,11)="PESO"
Sheets.Add
Sheets("Parameters Verify").Select
sheet.Range("A1:O500").Copy
Sheets("Foglio1").Paste
Sheets("Foglio1").Name = "Produzione"
sheet.Sheets("Produzione").Select
Sheets.Add
Sheets("Foglio2").Paste
Sheets("Foglio2").Name = "Acquisto"
Sheets("Produzione").Move After
Sheets.Add
Sheets("Foglio3").Paste
sheet.Application.CutCopyMode = False
Sheets("Foglio3").Name = "Da Verificare!"
Sheets("Produzione").Move After
Sheets("Parameters Verify").Select
sheet.Cells(10,10).Select
'##################
'Giustifica di tutte le colonne #
'##################
Sheets("Produzione").Select
sheet.Range("A1").Select
Sheets("Produzione").Columns("A:A").EntireColumn.AutoFit
Sheets("Produzione").Columns("B:B").EntireColumn.AutoFit
Sheets("Produzione").Columns("C:C").EntireColumn.AutoFit
Sheets("Produzione").Columns("D:D").EntireColumn.AutoFit
Sheets("Produzione").Columns("E:E").EntireColumn.AutoFit
Sheets("Produzione").Columns("F:F").EntireColumn.AutoFit
Sheets("Produzione").Columns("G:G").EntireColumn.AutoFit
Sheets("Produzione").Columns("H:H").EntireColumn.AutoFit
Sheets("Produzione").Columns("I:I").EntireColumn.AutoFit
Sheets("Produzione").Columns("J:J").EntireColumn.AutoFit
Sheets("Produzione").Columns("K:K").EntireColumn.AutoFit
Sheets("Acquisto").Select
sheet.Range("A1").Select
Sheets("Acquisto").Columns("A:A").EntireColumn.AutoFit
Sheets("Acquisto").Columns("B:B").EntireColumn.AutoFit
Sheets("Acquisto").Columns("C:C").EntireColumn.AutoFit
Sheets("Acquisto").Columns("D:D").EntireColumn.AutoFit
Sheets("Acquisto").Columns("E:E").EntireColumn.AutoFit
Sheets("Acquisto").Columns("F:F").EntireColumn.AutoFit
Sheets("Acquisto").Columns("G:G").EntireColumn.AutoFit
Sheets("Acquisto").Columns("H:H").EntireColumn.AutoFit
Sheets("Acquisto").Columns("I:I").EntireColumn.AutoFit
Sheets("Acquisto").Columns("J:J").EntireColumn.AutoFit
Sheets("Acquisto").Columns("K:K").EntireColumn.AutoFit
Sheets("Da Verificare!").Select
sheet.Range("A1").Select
Sheets("Da Verificare!").Columns("A:A").EntireColumn.AutoFit
Sheets("Da Verificare!").Columns("B:B").EntireColumn.AutoFit
Sheets("Da Verificare!").Columns("C:C").EntireColumn.AutoFit
Sheets("Da Verificare!").Columns("D:D").EntireColumn.AutoFit
Sheets("Da Verificare!").Columns("E:E").EntireColumn.AutoFit
Sheets("Da Verificare!").Columns("F:F").EntireColumn.AutoFit
Sheets("Da Verificare!").Columns("G:G").EntireColumn.AutoFit
Sheets("Da Verificare!").Columns("H:H").EntireColumn.AutoFit
Sheets("Da Verificare!").Columns("I:I").EntireColumn.AutoFit
Sheets("Da Verificare!").Columns("J:J").EntireColumn.AutoFit
Sheets("Da Verificare!").Columns("K:K").EntireColumn.AutoFit
Sheets("Parameters Verify").Select
sheet.Range("F2:F500").NumberFormat = "@"
'####################
'Cancellazione righe Produzione #
'####################
Sheets("Produzione").Select
RigaProd=2
ColonnaProd=4
TestoProd= Sheets("Produzione").Cells(RigaProd, ColonnaProd)
'msgbox TestoProd
TextCorrectProd As String
TextCorrectProd="Made"
dim stringRow As String
dim residui
residui=(billnumber+10)
Do Until (RigaProd=residui)
TestoProd=Sheets("Produzione").Cells(RigaProd, ColonnaProd)
'msgbox " Riga" &RigaProd
'msgbox TestoProd
'msgbox "Residui " &residui
'
If (TestoProd=TextCorrectProd) Then
RigaProd=RigaProd+1
TestoProd= Sheets("Produzione").Cells(RigaProd, ColonnaProd)
Else
Sheets("Produzione").Rows(RigaProd).Delete
residui=residui-1
TestoProd=Sheets("Produzione").Cells(RigaProd, ColonnaProd)
End If
TestoProd=Sheets("Produzione").Cells(RigaProd, ColonnaProd)
Loop
'###################
'Cancellazione righe Acquisto #
'###################
Sheets("Acquisto").Select
RigaAcqui=2
ColonnaAcqui=4
TestoAcqui= Sheets("Acquisto").Cells(RigaAcqui, ColonnaAcqui)
TextCorrectAcqui As String
TextCorrectAcqui="Bought"
residui=(billnumber+10)
Do Until (RigaAcqui=residui)
TestoAcqui=Sheets("Acquisto").Cells(RigaAcqui, ColonnaAcqui)
If (TestoAcqui=TextCorrectAcqui) Then
RigaAcqui=RigaAcqui+1
TestoAcqui= Sheets("Acquisto").Cells(RigaAcqui, ColonnaAcqui)
Else
Sheets("Acquisto").Rows(RigaAcqui).Delete
residui=residui-1
TestoAcqui=Sheets("Acquisto").Cells(RigaAcqui, ColonnaAcqui)
End If
TestoAcqui=Sheets("Acquisto").Cells(RigaAcqui, ColonnaAcqui)
Loop
'##################
'Cancellazione righe Unknow #
'##################
Sheets("Da Verificare!").Select
RigaUnk=2
ColonnaUnk=4
TestoUnk= Sheets("Da Verificare!").Cells(RigaUnk, ColonnaUnk)
TextCorrectUnk As String
TextCorrectUnk="Unknown"
residui=(billnumber+10)
Do Until (RigaUnk=residui)
TestoUnk=Sheets("Da Verificare!").Cells(RigaUnk, ColonnaUnk)
If (TestoUnk=TextCorrectUnk) Then
RigaUnk=RigaUnk+1
TestoUnk= Sheets("Da Verificare!").Cells(RigaUnk, ColonnaUnk)
Else
Sheets("Da Verificare!").Rows(RigaUnk).Delete
residui=residui-1
TestoUnk=Sheets("Da Verificare!").Cells(RigaUnk, ColonnaUnk)
End If
TestoUnk=Sheets("Da Verificare!").Cells(RigaUnk, ColonnaUnk)
Loop
Sheets("Produzione").Select
sheet.Range("A1").Select
Sheets("Produzione").Columns("D:D").EntireColumn.Delete
Sheets("Produzione").Columns("D:D").EntireColumn.Delete
Sheets("Produzione").Columns("D:D").EntireColumn.Delete
Sheets("Produzione").Columns("D:D").EntireColumn.Delete
Sheets("Acquisto").Select
sheet.Range("A1").Select
Sheets("Acquisto").Columns("D:D").EntireColumn.Delete
Sheets("Acquisto").Columns("G:G").EntireColumn.Delete
Sheets("Acquisto").Columns("G:G").EntireColumn.Delete
Sheets("Acquisto").Columns("G:G").EntireColumn.Delete
Sheets("Acquisto").Columns("G:G").EntireColumn.Delete
Sheets("Produzione").Columns("C:C").EntireColumn.NumberFormat = "@"
Sheets("Produzione").Columns("D:D").EntireColumn.NumberFormat = "@"
Sheets("Produzione").Columns("E:E").EntireColumn.NumberFormat = "@"
Sheets("Produzione").Columns("F:F").EntireColumn.NumberFormat = "@"
Sheets("Produzione").Columns("G:G").EntireColumn.NumberFormat = "@"
Sheets("Produzione").Select
'''''''colonna 5
RigaUnk=2
Do Until (RigaUnk=billnumber+10)
TestoUnk=Sheets("Produzione").Cells(RigaUnk, 5)
If (TestoUnk="1,173") Then
Sheets("Produzione").Cells(RigaUnk, 5)="1.1730"
RigaUnk=RigaUnk+1
ElseIf (TestoUnk="1,2311") Then
Sheets("Produzione").Cells(RigaUnk, 5)="1.2311"
RigaUnk=RigaUnk+1
ElseIf (TestoUnk="1,2312") Then
Sheets("Produzione").Cells(RigaUnk, 5)="1.2312"
RigaUnk=RigaUnk+1
ElseIf (TestoUnk="1,2343") Then
Sheets("Produzione").Cells(RigaUnk, 5)="1.2343"
RigaUnk=RigaUnk+1
Else
RigaUnk=RigaUnk+1
End If
Loop
Sheets("Acquisto").Columns("C:C").EntireColumn.NumberFormat = "@"
Sheets("Acquisto").Columns("D:D").EntireColumn.NumberFormat = "@"
Sheets("Acquisto").Columns("E:E").EntireColumn.NumberFormat = "@"
Sheets("Acquisto").Columns("F:F").EntireColumn.NumberFormat = "@"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''workbook.SaveAs sFileOutput,
excel.Application.DisplayAlerts = False
Call workbook.SaveAs(sFileOutput, True)
excel.Application.DisplayAlerts = False
excel.Application.DisplayAlerts = True
End Sub
RE: Catia V5 R19 - Macro: How to Create BOM - Balloons with references related to the all part
-GEL
Imposible is nothing.
RE: Catia V5 R19 - Macro: How to Create BOM - Balloons with references related to the all part
I would like to assign a specific reference number to specific CATPart of .CATProduct.
In order to obtain an exel file where every components will have the reference numbers choose by me.
RE: Catia V5 R19 - Macro: How to Create BOM - Balloons with references related to the all part
So, in a product you generated numbering with the aid of [Generate Numbering] command.
After that CATIA assigned to each instance a number(0,1,2... always starting from 1) or a letter(A,B,C,... always starting from A). This number is called Instance Number (<> InstanceName)
Now, if we open the [Properties] dialog box of a child product we can see the assigned to it Instance Number, but it is not editable.
To the best of my knowledge, this property of the Product Class is not explosed to automation (other than CAA?) and there are reasons for that.
Of course, there are other ways to have 'InstanceNumber', which is editable and appear in the BoM. In this scenario, the user shall do all the work manually or create automation which will not allow inconsistencies between model, drawing and relevant BoM.
-GEL
Imposible is nothing.
RE: Catia V5 R19 - Macro: How to Create BOM - Balloons with references related to the all part