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
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
| Private Sub btn_export_Click()
Call ExportVersXls
End Sub
Sub ExportVersXls()
' pour éviter les problèmes de référence, en utilise le Late Binding (liaison tardive en français):
Dim oApp As Object
Dim oWbk As Object
Dim oWSht As Object
Dim oRst As Recordset
Dim Qdf As DAO.QueryDef
Dim oDb As Database
Dim i As Long
'Définition des applications excel et access
Set oDb = CurrentDb()
Set oApp = CreateObject("excel.application")
oApp.Visible = True ' pour contrôler le bon fonctionnement, on rend visible l'instance Excel,
' l'instruction peut être enlevée après la phase de tests
Set oWbk = oApp.Workbooks.Open("\\srv01326\EpargneCommun\Epargne-Retraite\Rentes\9_APPELS_DE_FONDS_AXA\RESULTATS\Export.xlsx") ' modifier le chemin du fichier si besoin
Set oWSht1 = oWbk.Worksheets("R_Annuités_Rentes")
Set oWSht3 = oWbk.Worksheets("R_CNRCC")
Set oWSht4 = oWbk.Worksheets("R_Coherence_Sexes")
Set oWSht5 = oWbk.Worksheets("R_Date_Emission")
Set oWSht6 = oWbk.Worksheets("R_Deces")
Set oWSht7 = oWbk.Worksheets("R_Echéances_Fractionement")
Set oWSht8 = oWbk.Worksheets("R_Fractionnement_Rente")
Set oWSht9 = oWbk.Worksheets("R_Infos_Credirentier")
Set oWSht11 = oWbk.Worksheets("R_Rentes_Paliers")
Set oWSht12 = oWbk.Worksheets("R_Rentes_Terminee")
Set oWSht13 = oWbk.Worksheets("R_Taux_Reversion")
Set oWSht14 = oWbk.Worksheets("R_Taux_Technique")
Set oWSht2 = oWbk.Worksheets("R_Capital_Constitutif")
Set oWSht10 = oWbk.Worksheets("R_Rapprochement_annuelle")
Set oWSht15 = oWbk.Worksheets("R_Types_Rentes")
' définition du jeu de données avec une requête enregistrée
Set Qdf1 = oDb.QueryDefs("R_Annuités_Rentes")
Set Qdf3 = oDb.QueryDefs("R_CNRCC")
Set Qdf4 = oDb.QueryDefs("R_Coherence_Sexes")
Set Qdf5 = oDb.QueryDefs("R_Date_Emission")
Set Qdf6 = oDb.QueryDefs("R_Deces")
Set Qdf7 = oDb.QueryDefs("R_Echéances_Fractionement")
Set Qdf8 = oDb.QueryDefs("R_Fractionnement_Rente")
Set Qdf9 = oDb.QueryDefs("R_Infos_Credirentier")
Set Qdf11 = oDb.QueryDefs("R_Rentes_Paliers")
Set Qdf12 = oDb.QueryDefs("R_Rentes_Terminee")
Set Qdf13 = oDb.QueryDefs("R_Taux_Reversion")
Set Qdf14 = oDb.QueryDefs("R_Taux_Technique")
Set Qdf2 = oDb.QueryDefs("R_Capital_Constitutif")
Set Qdf10 = oDb.QueryDefs("R_Rapprochement_annuelle")
Set Qdf15 = oDb.QueryDefs("R_Types_Rentes")
Set oRst1 = Qdf1.OpenRecordset
Set oRst2 = Qdf2.OpenRecordset
Set oRst3 = Qdf3.OpenRecordset
Set oRst4 = Qdf4.OpenRecordset
Set oRst5 = Qdf5.OpenRecordset
Set oRst6 = Qdf6.OpenRecordset
Set oRst7 = Qdf7.OpenRecordset
Set oRst8 = Qdf8.OpenRecordset
Set oRst9 = Qdf9.OpenRecordset
Set oRst10 = Qdf10.OpenRecordset
Set oRst11 = Qdf11.OpenRecordset
Set oRst12 = Qdf12.OpenRecordset
Set oRst13 = Qdf13.OpenRecordset
Set oRst14 = Qdf14.OpenRecordset
Set oRst15 = Qdf15.OpenRecordset
' suppression des anciennes données de la feuille
oWSht1.Activate
oWSht1.Cells.Select
oWSht2.Activate
oWSht2.Cells.Select
oWSht3.Activate
oWSht3.Cells.Select
oWSht4.Activate
oWSht4.Cells.Select
oWSht5.Activate
oWSht5.Cells.Select
oWSht6.Activate
oWSht6.Cells.Select
oWSht7.Activate
oWSht7.Cells.Select
oWSht8.Activate
oWSht8.Cells.Select
oWSht9.Activate
oWSht9.Cells.Select
oWSht10.Activate
oWSht10.Cells.Select
oWSht11.Activate
oWSht11.Cells.Select
oWSht12.Activate
oWSht12.Cells.Select
oWSht13.Activate
oWSht13.Cells.Select
oWSht14.Activate
oWSht14.Cells.Select
oWSht15.Activate
oWSht15.Cells.Select
oApp.Selection.Delete
' entête de colonnes sur la 1ère ligne
For i = 0 To oRst1.Fields.Count - 1
oWSht1.Range("A1").Offset(0, i) = oRst1(i).Name
Next i
For i = 0 To oRst2.Fields.Count - 1
oWSht2.Range("A1").Offset(0, i) = oRst2(i).Name
Next i
For i = 0 To oRst3.Fields.Count - 1
oWSht3.Range("A1").Offset(0, i) = oRst3(i).Name
Next i
For i = 0 To oRst4.Fields.Count - 1
oWSht4.Range("A1").Offset(0, i) = oRst4(i).Name
Next i
For i = 0 To oRst5.Fields.Count - 1
oWSht5.Range("A1").Offset(0, i) = oRst5(i).Name
Next i
For i = 0 To oRst6.Fields.Count - 1
oWSht6.Range("A1").Offset(0, i) = oRst6(i).Name
Next i
For i = 0 To oRst7.Fields.Count - 1
oWSht7.Range("A1").Offset(0, i) = oRst7(i).Name
Next i
For i = 0 To oRst8.Fields.Count - 1
oWSht8.Range("A1").Offset(0, i) = oRst8(i).Name
Next i
For i = 0 To oRst9.Fields.Count - 1
oWSht9.Range("A1").Offset(0, i) = oRst9(i).Name
Next i
For i = 0 To oRst10.Fields.Count - 1
oWSht10.Range("A1").Offset(0, i) = oRst10(i).Name
Next i
For i = 0 To oRst11.Fields.Count - 1
oWSht11.Range("A1").Offset(0, i) = oRst11(i).Name
Next i
For i = 0 To oRst12.Fields.Count - 1
oWSht12.Range("A1").Offset(0, i) = oRst12(i).Name
Next i
For i = 0 To oRst13.Fields.Count - 1
oWSht13.Range("A1").Offset(0, i) = oRst13(i).Name
Next i
For i = 0 To oRst14.Fields.Count - 1
oWSht14.Range("A1").Offset(0, i) = oRst14(i).Name
Next i
For i = 0 To oRst15.Fields.Count - 1
oWSht15.Range("A1").Offset(0, i) = oRst15(i).Name
Next i
' copie des données à partir de la 2ème ligne
oWSht1.Range("A2").CopyFromRecordset oRst1
oWSht2.Range("A2").CopyFromRecordset oRst2
oWSht3.Range("A2").CopyFromRecordset oRst3
oWSht4.Range("A2").CopyFromRecordset oRst4
oWSht5.Range("A2").CopyFromRecordset oRst5
oWSht6.Range("A2").CopyFromRecordset oRst6
oWSht7.Range("A2").CopyFromRecordset oRst7
oWSht8.Range("A2").CopyFromRecordset oRst8
oWSht9.Range("A2").CopyFromRecordset oRst9
oWSht10.Range("A2").CopyFromRecordset oRst10
oWSht11.Range("A2").CopyFromRecordset oRst11
oWSht12.Range("A2").CopyFromRecordset oRst12
oWSht13.Range("A2").CopyFromRecordset oRst13
oWSht14.Range("A2").CopyFromRecordset oRst14
oWSht15.Range("A2").CopyFromRecordset oRst15
' fermeture des instances ouvertes
oRst1.Close
oRst2.Close
oRst3.Close
oRst4.Close
oRst5.Close
oRst6.Close
oRst7.Close
oRst8.Close
oRst9.Close
oRst10.Close
oRst11.Close
oRst12.Close
oRst13.Close
oRst14.Close
oRst15.Close
oWbk.Close True ' sauvegarde du classeur
Set oRst1 = Nothing
Set oRst2 = Nothing
Set oRst3 = Nothing
Set oRst4 = Nothing
Set oRst5 = Nothing
Set oRst6 = Nothing
Set oRst7 = Nothing
Set oRst8 = Nothing
Set oRst9 = Nothing
Set oRst10 = Nothing
Set oRst11 = Nothing
Set oRst12 = Nothing
Set oRst13 = Nothing
Set oRst14 = Nothing
Set oRst15 = Nothing
Set oDb = Nothing
Set oWSht1 = Nothing
Set oWSht2 = Nothing
Set oWSht3 = Nothing
Set oWSht4 = Nothing
Set oWSht5 = Nothing
Set oWSht6 = Nothing
Set oWSht7 = Nothing
Set oWSht8 = Nothing
Set oWSht9 = Nothing
Set oWSht10 = Nothing
Set oWSht11 = Nothing
Set oWSht12 = Nothing
Set oWSht13 = Nothing
Set oWSht14 = Nothing
Set oWSht15 = Nothing
Set oWbk = Nothing
Set oApp = Nothing
End Sub |
Exporter résultats de requêtes dans le même fichier Excel
Répondre avec citation
Partager