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 71
| Private Sub CommandButton2_Click()
Dim myItems As Outlook.Items
Dim myItem As Object
Dim wb As Excel.Workbook
Dim appxl As Excel.Application
Dim xla As Object
Dim a As String
Dim b As String
Dim c As String
Dim bus As String
Dim fichier As String
Dim fichier1 As String
Dim i As Long
Dim j As Long
b = Now
b = Format(b, "YYYY")
fichier1 = "commande.xlsx"
fichier = "C:\commande.xlsx"
Set appxl = Excel.Application '
j = appxl.Workbooks.Count ' compte nmbre fichier excel ouverts
If j = 0 Then GoTo 2 ' si 0 alors ouvre le fichier commande goto 2:
For i = 1 To j
c = appxl.Workbooks(i).Name
If c = fichier1 Then
GoTo 1
End If
Next i
2:
Set wb = appxl.Workbooks.Open(fichier)
appxl.Visible = True
1:
Set wb = appxl.ActiveWorkbook
wb.Sheets(b).Range("A65536").End(xlUp).Offset(1, 0) = UserForm1.Label4.Caption
wb.Sheets(b).Range("A65536").End(xlUp).Offset(0, 9) = UserForm1.Label2.Caption
If Not InStr(1, UserForm1.Label6.Caption, "le bus N°", 1) = 0 Then
bus = Mid(UserForm1.Label6.Caption, 11, Len(UserForm1.Label6.Caption))
wb.Sheets(b).Range("A65536").End(xlUp).Offset(0, 11) = bus
wb.Sheets(b).Range("A65536").End(xlUp).Offset(0, 8) = "Bus"
End If
If Not InStr(1, UserForm1.Label6.Caption, "N° d'Ordre", 1) = 0 Then
a = UserForm1.MultiPage1.Pages(0).Label8.Caption
wb.Sheets(b).Range("A65536").End(xlUp).Offset(0, 11) = a
a = UserForm1.MultiPage1.Pages(0).Label25.Caption
wb.Sheets(b).Range("A65536").End(xlUp).Offset(0, 8) = a
a = UserForm1.MultiPage1.Pages(0).Label11.Caption
wb.Sheets(b).Range("A65536").End(xlUp).Offset(0, 14) = a
End If
MsgBox "Infos ajoutées dans tableau"
Unload UserForm1 |
Partager