Code:
Dim e3: Set e3 = CreateObject( "CT.Application" )
Dim prj: Set prj = CreateObject( "CT.Job" )
Dim dev: Set dev = CreateObject( "CT.Device" )
Dim cmp: Set cmp = CreateObject( "CT.Component")
Dim App: Set App = CreateObject("CT.Application")
Class CComponent
Private Sub Class_Initialize()
count = 1
terminal = False
Set devnamlst = CreateObject("Scripting.Dictionary")
End Sub
Dim count, name, supplier, devnamlst, terminal, filter, description, location
End Class
Dim oDicCmp: Set oDicCmp = CreateObject("scripting.dictionary")
'Excelvorlage
Dim Excelvorlage
Excelvorlage = "W:\Parts.xltm"
devcnt = prj.GetAllDeviceIds( devids )
For d = 1 To devcnt
dev.SetId devids( d )
' device oder component?
cmpnam = dev.GetComponentName
If( cmpnam <> "" ) Then
' component in Liste?
If( Not oDicCmp.Exists( cmpnam ) ) Then
' ist nicht in der Liste
' Neue Instanz component class
Set ccmp = New CComponent
' Klasse füllen
ccmp.name = cmpnam
ccmp.supplier = dev.GetComponentAttributeValue( "Supplier" )
ccmp.description = dev.GetComponentAttributeValue ( "Description" )
ccmp.location = dev.GetLocation
ccmp.devnamlst.Add dev.Getid, dev
ccmp.Filter = dev.HasAttribute("NotInBOM")
App.putinfo 0, dev.HasAttribute("NotInBOM")
oDicCmp.Add cmpnam, ccmp
If( dev.IsTerminal ) Then ccmp.terminal = True
Else
' increase the amount of devices
Set ccmp = oDicCmp( cmpnam )
ccmp.count = ccmp.count + 1
ccmp.devnamlst.Add dev.Getid, null
End If
' Nächstes Schleifenelement
End if
Next
Dim oExcel: Set oExcel = WScript.CreateObject("Excel.Application")
' Neue Excel Tabelle mit Vorlage erzeugen
oExcel.Workbooks.Add Excelvorlage
oExcel.ActiveWorkbook.SaveAs prj.GetPath & prj.GetName & "_Rev." & prj.GetAttributeValue("rmChangeIndex") & "_BOM"
oExcel.Visible = 1
' Überschriften in der Tabelle einfügen
oExcel.Cells( 4, 1 ) = "Ort"
oExcel.Cells( 4, 2 ) = "Anzahl"
oExcel.Cells( 4, 3 ) = "Ident-Nr."
oExcel.Cells( 4, 4 ) = "Beschreibung"
oExcel.Cells( 4, 5 ) = "Hersteller"
oExcel.Cells( 4, 6 ) = "Betriebsmittelkennzeichen"
oExcel.Cells( 4, 7 ) = "Länge (m)"
' Zeilenzähler auf 7 setzen
eline = 5
' Schleife über die Schlüssel in der Bauteilliste
For Each ccmp In oDicCmp.Items
' Daten in die Exceltabelle übertragen
if ccmp.filter = 0 then
oExcel.Cells( eline, 1 ) = ccmp.location
oExcel.Cells( eline, 2 ) = ccmp.count
oExcel.Cells( eline, 3 ) = "'" & ccmp.name
oExcel.Cells( eline, 4 ) = ccmp.description
oExcel.Cells( eline, 5 ) = ccmp.supplier
' Betriebsmittel in die Exceltabelle übertragen
tmpstr = ""
If( Not ccmp.terminal ) Then
For Each key In ccmp.devnamlst.keys
dev.SetId key
tmpstr = tmpstr & dev.Getname & ","
next
oExcel.Cells( eline, 6 ) = Left( tmpstr, Len( tmpstr ) - 1 )
End If
' Länge der Betriebsmittel in die Exceltabelle übertragen
tmpstr2 = ""
If ( Not ccmp.terminal ) Then
For Each key In ccmp.devnamlst.keys
dev.SetId key
Dim ATTLength, ATTLengthDouble
ATTLength = dev.GetAttributeValue("Length")
if ATTLength <> "" then
ATTLengthDouble = CDBl (ATTLength)
ATTlengthDouble = round (ATTlengthDouble / 1000,2)
Length = ATTLengthDouble & "m, "
else
Length = " "
end if
tmpstr2 = tmpstr2 & Length
next
oExcel.Cells( eline, 7 ) = Left( tmpstr2, Len( tmpstr2 ) - 1 )
End If
' Zeilenzähler erhöhen
eline = eline + 1
App.putinfo 0, eline
' Nächstes Schleifenelement
end if
Next
oExcel.Columns( "A:G" ).AutoFit
oExcel.Application.Run "Sortieren"
App.putinfo 0, "Skript fertig!"
Set App = Nothing
Set oExcel = Nothing
Set dev = Nothing
Set prj = Nothing
Set e3 = Nothing