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
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
| '#Insertion dans Excel#'
'Dim oXL As clsExcelHost
Try
oXL = New clsExcelHost
Catch
MessageBox.Show("Excel n'est pas installé !", "SPGTRUL", MessageBoxButtons.OK, MessageBoxIcon.Error)
Exit Sub
End Try
Dim cptxls AsInteger
Dim cptfile AsInteger
Dim iLenght AsInteger = iLenght1x
Dim namexls AsString = "_(0-4)" (1 er fichiers)
cptfile = cptfile1
For cptxls = 1 To 2
Dim nbLignes AsInteger = cptfile + 1 'iLenght + cpt + 1
oXL.xlApp.WorkBooks.Open(LbDest.Text & "STP-C7Plan GT-" & Format(Now.Date, "d.M.yy") & namexls & ".xls")
oXL.xlApp.Range("A" & CStr(iLenght + 2) & ":G" & nbLignes).Select()
Dim StrTemp AsString
StrTemp = Mid(cbcolor.Text, InStr(cbcolor.Text, "(") + 1)
With oXL.xlApp.Selection.Interior
.ColorIndex = Microsoft.VisualBasic.Left(StrTemp, InStr(StrTemp, ")") - 1)
.Pattern = 1
End With
oXL.xlApp.Range("B1:B" & nbLignes).Select()
oXL.xlApp.Range("B" & nbLignes).Activate()
oXL.xlApp.Range("A1:G" & nbLignes).Sort(Key1:=oXL.xlApp.Range("B" & nbLignes), Order1:=1, Header _
:=0, OrderCustom:=1, MatchCase:=False, Orientation:=1 _
, DataOption1:=0)
oXL.xlApp.Rows("1:1").Select()
oXL.xlApp.Selection.Insert(Shift:=-4121)
oXL.xlApp.cells(1, 1) = "Name"
oXL.xlApp.cells(1, 2) = "CCandNDC"
oXL.xlApp.cells(1, 3) = "Country_Name"
oXL.xlApp.cells(1, 4) = "Operator_Name"
oXL.xlApp.cells(1, 5) = "Pointer_Name"
oXL.xlApp.cells(1, 6) = "STP"
oXL.xlApp.cells(1, 7) = "CeaseDate"
oXL.xlApp.Columns("A:A").EntireColumn.AutoFit()
oXL.xlApp.Columns("B:B").EntireColumn.AutoFit()
oXL.xlApp.Columns("C:C").EntireColumn.AutoFit()
oXL.xlApp.Columns("D:D").EntireColumn.AutoFit()
oXL.xlApp.Columns("E:E").EntireColumn.AutoFit()
oXL.xlApp.Columns("F:F").EntireColumn.AutoFit()
oXL.xlApp.Columns("G:G").EntireColumn.AutoFit()
oXL.xlApp.Rows("1:1").Select()
oXL.xlApp.Selection.AutoFilter()
oXL.xlApp.Selection.RowHeight = 24.75
oXL.xlApp.Range("A1:G1").Select()
oXL.xlApp.Range("F1").Activate()
With oXL.xlApp.Selection.Interior
.ColorIndex = 15
.Pattern = 1
End With
oXL.xlApp.Range("H2").Select()
oXL.xlApp.ActiveWindow.FreezePanes = True
oXL.xlApp.Rows(nbLignes + 1 & ":" & nbLignes + 1).Select()
oXL.xlApp.Selection.Delete(Shift:=-4162)
oXL.xlApp.Range("A1").Select()
oXL.xlApp.DisplayAlerts = False
oXL.xlApp.workbooks(1).Save()
oXL.xlApp.workbooks(1).close()
namexls = "_(6-9)" ?2ème fichier, le 3ème je ne sais pas sous quelle forme le mettre, le 3ème fichier lui devrait être namexls = "_(5)
cptfile = cptfile2
iLenght = iLenght2
Next
'END#Insertion dans Excel#' |
