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