1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70
| Option Explicit
Public Sub calcul()
Dim xlapp As Excel.Application
Dim xlsheet As Excel.Worksheet
Dim xlBook As Excel.Workbook
Dim X As Integer
Dim anzahlPal As Integer
Dim Auflage As Long, exPal As Long, ExVB As Long, VBlage As Long, lagPal As Long
Dim aufTrag As String, matchCode As String, Split As String, Verpackung As String
Dim Adresse As String, straSse As String, oRt As String
Dim belege As Long, auflageohne As Long
'On Error GoTo gestionerreur
Set xlapp = Excel.Application
Set xlBook = xlapp.ActiveWorkbook
If USFEintrag.belege = "" Then USFEintrag.belege.Value = "0" Else
aufTrag = USFEintrag.Objekt
matchCode = USFEintrag.Auftragnr & " / " & USFEintrag.HeftNr
Split = USFEintrag.Split
Auflage = USFEintrag.Auflagegesamt
belege = USFEintrag.belege
Adresse = USFEintrag.Adresse.Column(0)
straSse = USFEintrag.Adresse.Column(1)
oRt = USFEintrag.Adresse.Column(2)
auflageohne = Auflage - belege
Verpackung = USFEintrag.Verpackung
Select Case USFEintrag.ExVB
Case Is = ""
anzahlPal = InputBox("Anzahl von Paletten")
If anzahlPal < 1 Then
MsgBox "Ich brauche min. 1 Pal."
Exit Sub
Else
End If
Case Else
ExVB = USFEintrag.ExVB
If USFEintrag.VBlage = "" Then USFEintrag.VBlage.Value = "1" Else
If USFEintrag.Lage = "" Then USFEintrag.Lage.Value = "1" Else
exPal = ExVB * VBlage * lagPal
anzahlPal = WorksheetFunction.RoundUp((auflageohne / exPal), 0)
End Select
'Ajouter une feuille de calcul
Set xlsheet = xlBook.Worksheets.Add
xlsheet.name = "pzt_Liste" <------------------------------
'le titre - Entete
xlsheet.Cells(1, 1) = "Auftrag"
xlsheet.Cells(1, 2) = matchCode & "_" & aufTrag
xlsheet.Cells(2, 1) = "Split"
xlsheet.Cells(2, 2).NumberFormat = "@"
xlsheet.Cells(2, 2) = Split
xlsheet.Cells(2, 4) = "Auflage"
xlsheet.Cells(2, 5) = Auflage
xlsheet.Cells(2, 5).NumberFormat = "###,###"
xlsheet.Cells(2, 8).FormulaLocal = "=summe(B:B)"
xlsheet.Cells(2, 8).NumberFormat = "###,###"
xlsheet.Cells(1, 7) = Date$
xlsheet.Cells(1, 8) = Time$
xlsheet.Cells(1, 9) = Environ("Username")
xlsheet.Cells(3, 1) = "Palette Nr." |
Partager