Catia V5 R19 - Macro: How to Create BOM - Balloons with references related to the all part

[projectautomotive]

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

 

[GELFS]

Hi ProjectAutomotive,
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.

 

[projectautomotive]

exel file is created by the macro.
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").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").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").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").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").EntireColumn.Delete
Sheets("Produzione").Columns("D").EntireColumn.Delete
Sheets("Produzione").Columns("D").EntireColumn.Delete
Sheets("Produzione").Columns("D").EntireColumn.Delete





Sheets("Acquisto").Select
sheet.Range("A1").Select

Sheets("Acquisto").Columns("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").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").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

 

[GELFS]

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

Sorry but I do not understand what you need to do.

-GEL
Imposible is nothing.

 

[projectautomotive]

Maybe the problem is not concerning directly the MACRO.
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.

 

[GELFS]

Hello ProjectAutomotive,

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.

 

[loki3000]

how is with this in catia v6? as i read here, the BOM is a source of many headaches, is it improved there?