Automation Excel : mise en forme

[sweetmercy]

Bonjour à tous,

J'exporte le résultat de requetes Access vers Excel et je souhaite faire une petite mise en forme des fichiers créés.

Pour le moment j'ai bricolé le code ci-dessous.

Mon problème est sur le centrage des valeurs dans la colonne B:B en ligne 36 qui bloque et là je ne sais plus trop quoi faire.

Quelqu'un pour un petit coup de pouce ?

Merci d'avance

Code :

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
Function exportationexcel()
 
NOMFICH = "FICHIER.xls"
CHEMINRESULT = Application.CurrentProject.Path & "\RESULTAT\"
 
 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "REQUETE99", CHEMINRESULT & NOMFICH & "", True, "ESSAIS99"
 
'ouverture du fichier créé et application de la macro de mise en forme
Dim xlApp As Object
Dim xlSheet As Object
 
 
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    xlApp.workbooks.Open (CHEMINRESULT & NOMFICH & "")
 
    'selection de toute la feuille
    xlApp.cells.Select
 
    'choix de la police, de la taille, mise en gas de la première ligne
    xlApp.Selection.Font.Name = "ARIAL"
    xlApp.Selection.Font.Size = 8
    xlApp.rows("1:1").Select
    xlApp.Selection.Font.Bold = True
 
    'mise en forme automatique des largeurs de colonne
 
    xlApp.cells.EntireColumn.AutoFit
    xlApp.cells(1, 1).Select
 
    'vérouillage des volets à partir de la ligne2
    xlApp.Range("A2").Select
    xlApp.ActiveWindow.FreezePanes = True
 
'centrage des valeurs dans la colonne B
    xlApp.columns("B:B").Select
    xlApp.HorizontalAlignment = xlCenter
 
 
    'sauvegarde du classeur
    xlApp.ActiveWorkbook.Save
 
End Function

[pgz]

Bonsoir.

Tu peux faire

Code :

1
2
'centrage des valeurs dans la colonne B
    xlApp.columns("B:B").HorizontalAlignment = xlCenter

Ce n'est pas très propre puisqu'on espère que c'est la bonne feuille qui est active, mais cela devrait déjà aller mieux.

Remarque : dans la partie qui marche déjà, les .Select :

• ne sont pas indispensables
• allourdissent le code
• pénalisent l'intellisense
• ralentit l'exécution.

Que des avantages, quoi!

J'ai noté aussi que tu déclares les objets de façon bien générique. Es-tu un farouche partisan du late binding?
Tu ne te sers pas d'xlSheet, alors que c'était une bonne intention.

Bon d'accord : tu poses une question et j'en profite pour faire des remarques. Mais c'est pour t'aider, pas pour t'embêter.

Cordialement,

PGZ

Par exemple

[sweetmercy]

Bonjour et merci pour ta réponse

Oui, je sais, je ne suis qu'un petit bricoleur en VBA !

J'ai toujours un soucis :
"Erreur d'execution 1004"
"Impossible de définir la propriété HorizontalAlignment de la classe Range"

Suis trop nul !

[pgz]

Bonjour.

Tu as modifié ton code avec les indications que je t'ai données?

A+

PGZ

[sweetmercy]

Oui, j'ai repris ta ligne de code.
A priori il faudrait déclarer une contante xlCenter =-4108

??

[Philippe JOCHMANS]

Bonjour

Envoyé par sweetmercy

Oui, j'ai repris ta ligne de code.
A priori il faudrait déclarer une contante xlCenter =-4108

??

En effet, le fait que tu ne coches pas la référence Excel, fait que les constantes Excel n'existe pas dans ton code.

En général dans ce cas, je fais un module où je déclares toutes les constantes dont j'ai besoin.

Philippe

[pgz]

Bonjour.

Veux-tu essayer ce code ? Mais tu dois cocher la référence XL.

Code :

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
Function fExportationExcel() As Boolean
Dim xlApp As Excel.Application, xlWkb As exel.Workbook, xlSheet As Excel.Worksheet, xlRange As Excel.Range
Dim sNomFich As String, sCheminResult As String
 
On Error GoTo lblErreur
 
sNomFich = "FICHIER.xls"
sCheminResult = Application.CurrentProject.Path & "\RESULTAT\"
 
 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "REQUETE99", sCheminResult & sNomFich & "", True
 
'ouverture du fichier créé et application de la macro de mise en forme
 
 
    Set xlApp = New Excel.Application
    xlApp.Visible = True
    Set xlWkb = xlApp.Workbooks.Open(sCheminResult & sNomFich & "")
    Set xlSheet = xlWkb.Worksheets(1)
    Set xlRange = xlSheet.UsedRange
 
    'choix de la police, de la taille, mise en gas de la première ligne
    xlRange.Font.Name = "ARIAL"
    xlRange.Font.Size = 8
    xlSheet.Rows("1:1").Font.Bold = True
 
    'mise en forme automatique des largeurs de colonne
 
    xlRange.EntireColumn.AutoFit
 
    'vérouillage des volets à partir de la ligne2
    xlSheet.Range("A2").Select
    xlApp.ActiveWindow.FreezePanes = True
 
'centrage des valeurs dans la colonne B
    xlSheet.Columns("B:B").HorizontalAlignment = xlCenter
 
 
    'sauvegarde du classeur
    xlWkb.Save
 
fExportationExcel = True
 
lblSortie:
    Set xlRange = Nothing
    Set xlSheet = Nothing
    Set xlSheet = Nothing
    Set xlWkb = Nothing
    Set xlApp = Nothing
    Exit Function
 
lblErreur:
    fExportationExcel = False
    GoTo lblSortie
 
End Function

Pour voir...

J'en ai fait une fonction booléenne qui renvoie Vrai si elle s'est exécutée sans erreur. Pour tester, tu a intérêt à faire au pas à pas car le code ne s'arrête plus sur erreur, puisqu'il y a un embryon de gestion d'erreur.


Dans la méthode TransferSpreadSheet, j'ai enlevé l'argument Etendue. La doc dit

...
Lorsque vous effectuez une exportation vers une feuille de calcul, vous devez laisser cet argument vierge...

Pour info, tu aurais pu utiliser une autre méthode, sans TransferSpreadSheet :

• tu crées un jeu d'enregistrement sur la table ou requête
• tu crées un classeur XL
• Tu copies les données dans la première feuille avec la méthode CopyFromRecordset.

Cordialement,

PGZ

[sweetmercy]

Hello & merci à vous 2 pour votre support.

Mon soucis est que ce code sera exécuté potentiellement sur plusieurs machines, alors cocher la reférence Xl ne me semble pas trop adapté dans ce cas.

J'ai bon ?

[pgz]

Bonjour

Envoyé par sweetmercy

Mon soucis est que ce code sera exécuté potentiellement sur plusieurs machines, alors cocher la reférence Xl ne me semble pas trop adapté dans ce cas.

J'ai bon ?

Ouh la! Voilà un sujet à débat
Il faudrait en savoir beaucoup plus, mais tu peux résoudre seul cette question. Un peu de lecture Utilisation de liaison anticipée et liaison tardive dans Automation, la question expliquée par le support Microsoft.

Bien cordialement,

PGZ