exécuter une macro apres un export

**teen6517** · 01/09/2010, 17h56

Bonjour tous le monde.

J'arrive a exporter le comptenue d'une requete grace au code suivent :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "vic", "C:\Documents and Settings\laborder\Bureau\table access\liste sélectionner.xls", -1
 
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "baglan", "C:\Documents and Settings\laborder\Bureau\table access\liste sélectionner.xls", -1
 
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "uti", "C:\Documents and Settings\laborder\Bureau\table access\liste sélectionner.xls", -1
 
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "lourdes", "C:\Documents and Settings\laborder\Bureau\table access\liste sélectionner.xls", -1

mon probleme c'est que les colonnes par defaut dans excel son trop petite

j'ai donc fait une macro sur Excel

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
Sub macro_mise_en_page_rsatirage_ap()
 
    Columns("A:A").ColumnWidth = 10
    Columns("B:B").ColumnWidth = 19
    Columns("C:C").ColumnWidth = 23
    Columns("D:D").ColumnWidth = 20
    Columns("E:E").ColumnWidth = 50
    Columns("F:F").ColumnWidth = 10
    Columns("G:G").ColumnWidth = 33
    Columns("H:H").ColumnWidth = 33
    Columns("I:I").ColumnWidth = 10
    Columns("J:J").ColumnWidth = 24
    Columns("K:K").ColumnWidth = 30
    Columns("L:L").ColumnWidth = 44
    Columns("M:M").ColumnWidth = 15
    Columns("N:N").ColumnWidth = 30
    Columns("O:O").ColumnWidth = 6
    Columns("P:P").ColumnWidth = 30
    Columns("Q:Q").ColumnWidth = 50
    Columns("R:R").ColumnWidth = 12
    Columns("S:S").ColumnWidth = 30
    Columns("T:T").ColumnWidth = 9
    Columns("U:U").ColumnWidth = 20
    Columns("V:V").ColumnWidth = 23
    Columns("W:W").ColumnWidth = 17
 
End Sub

mais je voudrais que cette macro de mise en page s'éxécute toute seul sur tous les 4 onglet de mon documlent pasque pour l'instent je suis obliget de lancer la macro manuellement sur chaque onglet

C'est possible???

merci

**tarnx** · 01/09/2010, 18h54

il faut ajouter une boucle pour parcourir les feuilles de ton classeur et modifier ta procédure pour passer la feuille en argument.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
For intFeuille = 1 to TonClasseur.Sheets.Count
 
    macro_mise_en_page_rsatirage_ap(tonClasseur.Sheets(intFeuille))
 
Next

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
Sub macro_mise_en_page_rsatirage_ap(xlsWksh as Worksheet)
With xlsWksh
    .Columns("A:A").ColumnWidth = 10
    ....
End With
End sub

Autrement, tu ajoute la boucle for dans ta procédure. Au choix.

**teen6517** · 02/09/2010, 09h15

merci pour ton aide mais je n'arrive pas a adapter ton code avec le mien

juste apres cette ligne:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "lourdes", "C:\Documents and Settings\laborder\Bureau\table access\liste sélectionner.xls", -1

qui se trouve derriere le bouton d'un formulaire qui sert a exporter mais requete

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
Private Sub cmd_export_Click()

sa me donne le code suivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
Private Sub cmd_export_Click()
    '*******************************************************************
    '*****  exportation des sélectionner dans un fichier .xls **********
    '*******************************************************************
    Dim nb_onglet As Integer
 
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "vic", "C:\Documents and Settings\laborder\Bureau\table access\liste sélectionner.xls", -1
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "baglan", "C:\Documents and Settings\laborder\Bureau\table access\liste sélectionner.xls", -1
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "uti", "C:\Documents and Settings\laborder\Bureau\table access\liste sélectionner.xls", -1
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "lourdes", "C:\Documents and Settings\laborder\Bureau\table access\liste sélectionner.xls", -1
 
    Me!affichage1 = "liste a recupérer dans : C:\Documents and Settings\laborder\Bureau\table access\liste sélectionner.xls"
 
    For nb_onglet = 1 To [liste sélectionner.xls].sheets.Count
 
        macro_mise_en_page_rsatirage_ap ([liste sélectionner.xls].sheets(nb_onglet))
 
    Next nb_onglet
 
End Sub

mais je sa ne marche pas la compil ne veut pas se faire je supose que mon probleme vien du nom de mon classeur c peut etre pas sa qu'il attant mais je suis a cour d'idée

C'est quoi la bonne syntaxe?
merci

**teen6517** · 02/09/2010, 16h30

Désoler de remonter le post

Aucune solution a mon probleme?

Il n'est pas possible d'exporter les resultat de 4 requettes depuis un formulaire vers un classeur excel dans 4 onglets différent tout en formatent les colonnes de ces 4 onglet sur une largeur personalisé !!! ???

je peus deja réaliser l'export dans les 4 onglets
Il me manque le fait de pouvoir formater la largeur des colonnes personne ne c'est faire sa??

svp

Merci de votre aide

Invité · 02/09/2010, 17h36

Bonjour

C'est tout à fait possible, mais comme tu es sous Access il faut piloter Excel pour cela.

As-tu une déclaration d'objet Excel, etc ... As-tu été voir dans la FAQ Access en tapant le mot Excel ?

Philippe

**teen6517** · 03/09/2010, 09h34

merci philippe je vien de trouver de nouveau tuto (ce sur l'export je les avais deja) mais j'ai enfin trouvais ceux qui parle de la parti qui m'interresse

ils ont l'air pas coton du tout mais j'ais enfin une piste

merci

je reviendrai a l'ocasion si j'ai besoin d'un coup de pouce
ou je posteré la solution pour les suivants en mettent résolut

**teen6517** · 03/09/2010, 11h30

apres quelque test est recherche , je revien vers vous

voici mon nouveau code

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
Private Sub cmd_export_Click()
    '*******************************************************************
    '*****  exportation des sélectionner dans un fichier .xls **********
    '*******************************************************************
    Dim oAppExcel As Excel.Application
    Dim oClasseur As Excel.Workbook
    Dim oFeuille As Excel.Worksheet
    Dim oCell As Excel.Range
    Dim txtChemin As String
    Dim nb_feuille As Integer
 
    txtChemin = "C:\Documents and Settings\laborder\Bureau\table access\liste sélectionner.xls"
 
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "vic", txtChemin, -1
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "baglan", txtChemin, -1
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "uti", txtChemin, -1
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "lourdes", txtChemin, -1
 
    'Ouvre le fichier excel
    Set oAppExcel = CreateObject("Excel.Application")
    Set oClasseur = oAppExcel.Workbooks.Open(txtChemin)
 
    'Sélectionne la premiere feuille
 
        Set oFeuille = oClasseur.Worksheets(1)
 
        Columns("A:A").ColumnWidth = 10
        Columns("B:B").ColumnWidth = 19
        Columns("C:C").ColumnWidth = 23
        Columns("D:D").ColumnWidth = 20
        Columns("E:E").ColumnWidth = 50
        Columns("F:F").ColumnWidth = 10
        Columns("G:G").ColumnWidth = 33
        Columns("H:H").ColumnWidth = 33
        Columns("I:I").ColumnWidth = 10
        Columns("J:J").ColumnWidth = 24
        Columns("K:K").ColumnWidth = 30
        Columns("L:L").ColumnWidth = 44
        Columns("M:M").ColumnWidth = 15
        Columns("N:N").ColumnWidth = 30
        Columns("O:O").ColumnWidth = 6
        Columns("P:P").ColumnWidth = 30
        Columns("Q:Q").ColumnWidth = 50
        Columns("R:R").ColumnWidth = 12
        Columns("S:S").ColumnWidth = 30
        Columns("T:T").ColumnWidth = 9
        Columns("U:U").ColumnWidth = 20
        Columns("V:V").ColumnWidth = 23
        Columns("W:W").ColumnWidth = 17
 
    'Ferme Excel
    oClasseur.Save
    oClasseur.Close
    oAppExcel.Quit
    Set oAppExcel = Nothing
    Set oClasseur = Nothing
 
    Me.affichage1 = "liste a recupérer dans : " & txtChemin
 
End Sub

Ce code marche super bien pour donner la mise en page au 1er onglet

Mais je n'arrive pas a lui faire faire la mise en page sur les 3 autres onglets
je remplace cette ligne

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

        Set oFeuille = oClasseur.Worksheets(1)

par

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

        Set oFeuille = oClasseur.Worksheets(2)

donc normalement l'onglet 1 ne devrait pas avoir de mise en page, seulement l'onglet 2

Mais non c toujour l'onglet 1 qui a la mise a page

Moi j'avais penser a un code du genre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
    For num_onglet = 1 to 4
        Set oFeuille = oClasseur.Worksheets(num_onglet)
        '/***********************/
        '/***Mise en page de l'onglet**/
        '/***********************/
    next num_onglet

Mais sa ne marche pas, il n'y a que l'onglet 1 qui a la mise en forme

Je peus vous soliciter pour finaliser mon projet??
Merci

**tarnx** · 03/09/2010, 13h59

Essaye comme ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
For intSheet = 1 to 4
 
    Set oFeuille = oClasseur.Worksheets(intSheet)
 
    With oFeuille    
        .Columns("A:A").ColumnWidth = 10
        .Columns("B:B").ColumnWidth = 19
        .Columns("C:C").ColumnWidth = 23
        .Columns("D:D").ColumnWidth = 20
        .......
    End With
 
Next

**teen6517** · 06/09/2010, 10h59

Bonjour tous le monde

Le code Tarnx marche tres bien pour la premiere partie de la mise en page qui consiste a redimancionner la largeur des colonnes

Par contre Je cherche en plus de la redimension de la collone, alligner les comtenu des colonne a Gauche mais le titre de la colonne centrer

Avant le code de Tarnx pour un onglet j'utilisai le code suivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
 
 
    Columns("A:A").ColumnWidth = 10
    Columns("B:B").ColumnWidth = 19
    Columns("C:C").ColumnWidth = 23
    Columns("D:D").ColumnWidth = 20
    Columns("E:E").ColumnWidth = 50
    Columns("F:F").ColumnWidth = 10
    Columns("G:G").ColumnWidth = 33
    Columns("H:H").ColumnWidth = 33
    Columns("I:I").ColumnWidth = 10
    Columns("J:J").ColumnWidth = 24
    Columns("K:K").ColumnWidth = 30
    Columns("L:L").ColumnWidth = 44
    Columns("M:M").ColumnWidth = 15
    Columns("N:N").ColumnWidth = 30
    Columns("O:O").ColumnWidth = 6
    Columns("P:P").ColumnWidth = 30
    Columns("Q:Q").ColumnWidth = 50
    Columns("R:R").ColumnWidth = 12
    Columns("S:S").ColumnWidth = 30
    Columns("T:T").ColumnWidth = 9
    Columns("U:U").ColumnWidth = 20
    Columns("V:V").ColumnWidth = 23
    Columns("W:W").ColumnWidth = 17
 
    Columns("A:A").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Range("A1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Columns("B:B").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Range("B1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Columns("C:C").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Range("C1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Columns("D:D").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Range("D1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Columns("E:E").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Range("E1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Columns("F:F").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Range("F1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Columns("G:G").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Range("G1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Columns("H:H").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Range("H1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Columns("I:I").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Range("I1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Columns("J:J").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Range("J1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Columns("K:K").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Range("K1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Columns("L:L").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Range("L1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Columns("M:M").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Range("M1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Columns("N:N").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Range("N1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Columns("O:O").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Range("O1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Columns("P:P").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Range("P1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Columns("Q:Q").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Range("Q1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Columns("R:R").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Range("R1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Columns("S:S").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Range("S1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Columns("T:T").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Range("T1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Columns("U:U").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Range("U1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Columns("V:V").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Range("V1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Columns("W:W").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Range("W1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With

Ce code marcher tres bien mais pour un seul onglet
J'ai modifier le code pour le metre dans une boucle afin qu'il s'exécute dans tout les onglets sa ma donner :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
 
    'Ouvre le fichier excel
    Set oAppExcel = CreateObject("Excel.Application")
    Set oClasseur = oAppExcel.Workbooks.Open(txtChemin)
 
    For intSheet = 1 To 4
 
        Set oFeuille = oClasseur.Worksheets(intSheet)
 
        With oFeuille
            .Columns("A:A").ColumnWidth = 10
            .Columns("B:B").ColumnWidth = 19
            .Columns("C:C").ColumnWidth = 23
            .Columns("D:D").ColumnWidth = 20
            .Columns("E:E").ColumnWidth = 50
            .Columns("F:F").ColumnWidth = 10
            .Columns("G:G").ColumnWidth = 33
            .Columns("H:H").ColumnWidth = 33
            .Columns("I:I").ColumnWidth = 10
            .Columns("J:J").ColumnWidth = 24
            .Columns("K:K").ColumnWidth = 30
            .Columns("L:L").ColumnWidth = 44
            .Columns("M:M").ColumnWidth = 15
            .Columns("N:N").ColumnWidth = 30
            .Columns("O:O").ColumnWidth = 6
            .Columns("P:P").ColumnWidth = 30
            .Columns("Q:Q").ColumnWidth = 50
            .Columns("R:R").ColumnWidth = 12
            .Columns("S:S").ColumnWidth = 30
            .Columns("T:T").ColumnWidth = 9
            .Columns("U:U").ColumnWidth = 20
            .Columns("V:V").ColumnWidth = 23
            .Columns("W:W").ColumnWidth = 17
 
            .Columns("A:A").Select
            With Selection
                .HorizontalAlignment = xlLeft
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Range("A1").Select
            With Selection
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Columns("B:B").Select
            With Selection
                .HorizontalAlignment = xlLeft
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Range("B1").Select
            With Selection
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Columns("C:C").Select
            With Selection
                .HorizontalAlignment = xlLeft
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Range("C1").Select
            With Selection
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Columns("D:D").Select
            With Selection
                .HorizontalAlignment = xlLeft
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Range("D1").Select
            With Selection
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Columns("E:E").Select
            With Selection
                .HorizontalAlignment = xlLeft
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Range("E1").Select
            With Selection
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Columns("F:F").Select
            With Selection
                .HorizontalAlignment = xlLeft
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Range("F1").Select
            With Selection
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Columns("G:G").Select
            With Selection
                .HorizontalAlignment = xlLeft
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Range("G1").Select
            With Selection
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Columns("H:H").Select
            With Selection
                .HorizontalAlignment = xlLeft
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Range("H1").Select
            With Selection
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Columns("I:I").Select
            With Selection
                .HorizontalAlignment = xlLeft
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Range("I1").Select
            With Selection
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Columns("J:J").Select
            With Selection
                .HorizontalAlignment = xlLeft
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Range("J1").Select
            With Selection
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Columns("K:K").Select
            With Selection
                .HorizontalAlignment = xlLeft
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Range("K1").Select
            With Selection
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Columns("L:L").Select
            With Selection
                .HorizontalAlignment = xlLeft
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Range("L1").Select
            With Selection
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Columns("M:M").Select
            With Selection
                .HorizontalAlignment = xlLeft
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Range("M1").Select
            With Selection
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Columns("N:N").Select
            With Selection
                .HorizontalAlignment = xlLeft
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Range("N1").Select
            With Selection
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Columns("O:O").Select
            With Selection
                .HorizontalAlignment = xlLeft
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Range("O1").Select
            With Selection
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Columns("P:P").Select
            With Selection
                .HorizontalAlignment = xlLeft
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Range("P1").Select
            With Selection
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Columns("Q:Q").Select
            With Selection
                .HorizontalAlignment = xlLeft
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Range("Q1").Select
            With Selection
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Columns("R:R").Select
            With Selection
                .HorizontalAlignment = xlLeft
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Range("R1").Select
            With Selection
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Columns("S:S").Select
            With Selection
                .HorizontalAlignment = xlLeft
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Range("S1").Select
            With Selection
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Columns("T:T").Select
            With Selection
                .HorizontalAlignment = xlLeft
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Range("T1").Select
            With Selection
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Columns("U:U").Select
            With Selection
                .HorizontalAlignment = xlLeft
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Range("U1").Select
            With Selection
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Columns("V:V").Select
            With Selection
                .HorizontalAlignment = xlLeft
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Range("V1").Select
            With Selection
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Columns("W:W").Select
            With Selection
                .HorizontalAlignment = xlLeft
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
            .Range("W1").Select
            With Selection
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .ShrinkToFit = False
                .MergeCells = False
            End With
 
        End With
 
    Next
 
    'Ferme Excel
    oClasseur.Save
    oClasseur.Close
    oAppExcel.Quit
    Set oAppExcel = Nothing
    Set oClasseur = Nothing

Je n'est pas tous inséré d'un coup J'ai d'abbord testé la boucle avec seulement la redimension des colonne et sa marche super

Mais quand g voulu integret la partie qui sert a aligner a gauche puis centrer pour les titres de colonnes sa plante

Le debogage me dit qu'il a un probleme avec la ligne de sélection range

mais l'aide n'arrive pas a m'aider

Merci de votre aide

**teen6517** · 07/09/2010, 15h50

j'ai trouvé une solution toute simple

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "vic", txtChemain, -1
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "baglan", txtChemain, -1
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "uti", txtChemain, -1
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "lourdes", txtChemain, -1
 
    'Ouvre le fichier excel
    Set oAppExcel = CreateObject("Excel.Application")
    Set oClasseur = oAppExcel.Workbooks.Open(txtChemain)
 
    For intSheet = 1 To 4
 
        Set oFeuille = oClasseur.Worksheets(intSheet)
 
        With oFeuille
            .Columns("A:A").ColumnWidth = 10
            .Columns("A:A").HorizontalAlignment = xlLeft
            .Range("A1").HorizontalAlignment = xlCenter
 
            .Columns("B:B").ColumnWidth = 19
            .Columns("B:B").HorizontalAlignment = xlLeft
            .Range("B1").HorizontalAlignment = xlCenter
 
            .Columns("C:C").ColumnWidth = 23
            .Columns("C:C").HorizontalAlignment = xlLeft
            .Range("C1").HorizontalAlignment = xlCenter
 
            .Columns("D:D").ColumnWidth = 20
            .Columns("D:D").HorizontalAlignment = xlCenter
            .Range("D1").HorizontalAlignment = xlCenter
 
            ect .......
 
        End With
 
    Next
 
    'Ferme Excel
    oClasseur.Save
    oClasseur.Close
    oAppExcel.Quit
    Set oAppExcel = Nothing
    Set oClasseur = Nothing

voila en esperant que je serais pas le seul a m'en servir

merci a tous le monde

**Deustalos** · 09/09/2010, 12h09

Hello;

Dans le pilotage d'Excel par Access prend l'habitude de bien définir TON APPLICATION; TON CLASSEUR ET TA FEUILLE. Je parle par expérience

Voila un petit exemple; ton niveau de détail dépend de ce que tu veux faire. Dans ton cas la définition de la feuille est importante.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlsheet As Excel.Worksheet
 
Set xlApp = New Excel.Application
      xlApp.Visible = True                   'rend l'appplication excel visible ou non
Set xlBook = xlApp.Workbooks.Open(Chemin)
Set xlsheet = xlBook.Worksheets(Nom de la feuille)
 
xlsheet.Rows("1:28").Delete Shift:=xlUp
xl.Book.xlsheet.Cells(1, 1) = "........."
xlApp.xlBook.xlsheet.Range("I:I").Replace What:=.......

Bon courage. (Peux-tu passer en résolut)

Alex

exécuter une macro apres un export [AC-2000]

VBA Access

Discussions similaires

Partager

Partager