Voir le flux RSS

User

[Actualité] Piloter Excel depuis Access : les bonnes pratiques

Noter ce billet
par , 03/03/2020 à 19h35 (1362 Affichages)
Ce billet s'adresse principalement aux utilisateurs d'Access qui ne connaissent pas très bien le modèle objet d'Excel et toutes les astuces nécessaires pour bien piloter le tableur.

On s'est tous retrouvés un jour à vouloir faire un export dans un fichier Excel avec des problèmes de lenteur, de fichier déjà ouvert ou de processus déjà chargé en mémoire, avec comme conséquence une procédure qui ne finit pas, ou carrément un plantage de l'application.

On va donc voir comment on peut contourner ces problèmes avec un exemple simple d'exportation d'une source de données Access dans un classeur.

Introduction :

On dispose d'une table ou d'une requête côté Access que l'on souhaite exporter dans un fichier Excel.
Pour une meilleure compréhension des choses, j'utilise du Early Binding qui nécessite de déclarer les variables objet dans un type précis, et de cocher la référence Microsoft Excel XX.X Object Library.

Les bonnes pratiques

  1. Bien déclarer les variables objet en début de code.
  2. Utiliser aussi une gestion d'erreur pour libérer la mémoire en cas d'erreur.
  3. Utiliser l'instance du classeur si celui-ci est déjà ouvert.
  4. Pour optimiser le temps d'exécution du code, désactiver en début de procédure le calcul automatique, le rafraîchissement de l'écran et les événements, puis les rétablir en fin de procédure.
  5. Bien libérer les variables objet en fin de procédure.


1/ - Déclarer les variables objet en début de code

Code VBA : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
    Dim xlapp As Excel.Application ' variable Objet pour faire référence à l'application Excel
    Dim xlwbk As Excel.Workbook ' variable Objet pour faire référence au classeur
    Dim xlwsh As Excel.Worksheet ' variable Objet pour faire référence à la feuille
 
    Dim dbs As DAO.Database ' variable pour faire référence à la base courante
    Dim rst As DAO.Recordset ' variable pour faire référence au recordset lié à la table Access

2/ - Utiliser une gestion d'erreur

Elle va permettre en particulier de libérer la mémoire en cas d'erreur :

Code VBA : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
    On Error GoTo err_ExportData
    ...
    ...
err_ExportData:
 
    If Err.Number <> 0 Then ' si une erreur s'est produite
        MsgBox (Err.Description) ' affiche une description de l'erreur
    End If
 
    ' On libère les variables objet ici

3/ - Utiliser l'instance du fichier Excel si celui-ci est déjà ouvert

3.1/ - Création de l'objet application xlapp :

Code VBA : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
    On Error Resume Next ' instruction pour ignorer l'erreur qui pourrait se déclencher sur la ligne suivante
    Set xlapp = GetObject(, "Excel.Application") ' on récupère l'instance Excel ouverte : si pas d'instance la variable est à nothing
    On Error GoTo err_ExportData ' reprend la gestion d'erreur normale en annulant l'effet de l'instruction On error resume next
    If xlapp Is Nothing Then ' si pas d'instance d'Excel de créée
        Set xlapp = CreateObject("Excel.Application") ' on crée une nouvelle instance
    End If

3.2/ - Création de l'objet xlwbk pour le classeur

Le fichier est déjà ouvert, comment éviter les problèmes de conflit en écriture ?

Pour cela, on va utiliser une instruction qui va permettre de récupérer l'instance du classeur déjà ouvert :

Code VBA : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
    On Error Resume Next ' instruction pour ignorer l'erreur qui pourrait se déclencher sur la ligne suivante
    Set xlwbk = xlapp.Workbooks.Item(ExtractFileName(cheminFichierDestination)) ' on récupère l'instance du classeur ouvert : si pas d'instance la variable est à nothing
    On Error GoTo err_ExportData ' reprend la gestion d'erreur normale en annulant l'effet de l'instruction On error resume next
 
    If xlwbk Is Nothing Then ' si pas d'instance du classeur de déjà créée
        Set xlwbk = xlapp.Workbooks.Open(cheminFichierDestination) ' on crée une nouvelle instance
    End If


4/ - Pour optimiser le temps d'exécution du code

  • Désactiver au début du code le calcul automatique pour éviter des recalculs si par exemple votre classeur possède des formules.
  • Désactiver aussi le rafraîchissement automatique de l'écran.
  • Désactiver provisoirement aussi les événements.

Code VBA : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
    xlapp.Calculation = xlCalculationManual ' désactive les calculs automatiques sur le classeur
    xlapp.ScreenUpdating = False ' désactive le rafraîchissement automatique de l'écran
    xlapp.EnableEvents = False ' désactive les événements

Réactiver ces options à la fin du code :

Code VBA : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
    xlapp.ScreenUpdating = True ' active le rafraîchissement automatique de l'écran
    xlapp.EnableEvents = True ' active les événements
    xlapp.Calculation = xlCalculationAutomatic ' active les calculs automatiques des formules

5/ - Libérer les variables objet en fin de procédure

Pour libérer la mémoire et éviter de se retrouver avec des processus "fantômes", il faut impérativement mettre ces variables à nothing

Code VBA : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
 ' libère et ferme les variables objet
    Set rst = Nothing
    Set dbs = Nothing
 
    Set xlwsh = Nothing
 
    xlwbk.Close True ' on ferme avec sauvegarde du classeur
    Set xlwbk = Nothing
 
    xlapp.Quit ' quitte Excel
    Set xlapp = Nothing

La procédure complète

Arguments

  • DataSource : source de données Access (table, requête ou SQL).
  • CheminFichierDestination : chemin du classeur.
  • nomFeuille : nom de la feuille Excel.
  • Ajout : argument optionnel (vrai : ajoute à la fin ; faux : écrase les anciennes données).

Code VBA : 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
Public Sub ExportData(dataSource As String, cheminFichierDestination As String, nomFeuille As String, Optional ajout As Boolean = False)
    ' Procédure d'exportation des données de la source, dans le fichier et la feuille passés en arguments
    On Error GoTo err_ExportData
 
    Dim xlapp As Excel.Application ' variable Objet pour faire référence à l'application Excel
    Dim xlwbk As Excel.Workbook ' variable Objet pour faire référence au classeur
    Dim xlwsh As Excel.Worksheet ' variable Objet pour faire référence à la feuille
 
    Dim dbs As DAO.Database ' variable pour faire référence à la base courante
    Dim rst As DAO.Recordset ' variable pour faire référence au recordset lié à la table Access
 
    Dim idLigne As Long, idColonne As Long, idligneMax As Long, idColonneMax As Long
 
    If MsgBox("Souhaitez-vous exporter les données dans le fichier Excel ?", vbYesNo) = vbNo Then
        Exit Sub
    End If
 
    If Not IsFile(cheminFichierDestination) Then ' si le fichier n'existe pas on sort
        MsgBox "Fichier introuvable !", vbExclamation ' on signale que le fichier est introuvable
        Exit Sub ' on sort de la procédure
    End If
 
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(dataSource)
 
    On Error Resume Next ' instruction pour ignorer l'erreur qui pourrait se déclencher sur la ligne suivante
    Set xlapp = GetObject(, "Excel.Application") ' on récupère l'instance Excel ouverte : si pas d'instance la variable est à nothing
    On Error GoTo err_ExportData ' reprend la gestion d'erreur normale en annulant l'effet de l'instruction On error resume next
 
    If xlapp Is Nothing Then ' si pas d'instance d'Excel de créée
        Set xlapp = CreateObject("Excel.Application") ' on crée une nouvelle instance
    End If
 
    On Error Resume Next ' instruction pour ignorer l'erreur qui pourrait se déclencher sur la ligne suivante
    Set xlwbk = xlapp.Workbooks.Item(ExtractFileName(cheminFichierDestination)) ' on récupère l'instance du classeur ouvert : si pas d'instance la variable est à nothing
    On Error GoTo err_ExportData ' reprend la gestion d'erreur normale en annulant l'effet de l'instruction On error resume next
 
    If xlwbk Is Nothing Then ' si pas d'instance du classeur de créée
        Set xlwbk = xlapp.Workbooks.Open(cheminFichierDestination) ' on crée une nouvelle instance
    End If
 
    Set xlwsh = xlwbk.Sheets(nomFeuille) ' on référence la 1re feuille du classeur
 
    ' Commandes pour optimiser la procédure d'export
    xlapp.Calculation = xlCalculationManual ' désactive les calculs automatiques sur le classeur
    xlapp.ScreenUpdating = False ' désactive le rafraîchissement automatique de l'écran
    xlapp.EnableEvents = False ' désactive les événements
 
    ' mise à jour des en-têtes dans la feuille sur la 1re ligne
    For idColonne = 1 To rst.Fields.Count
        xlwsh.Cells(1, idColonne).Value = rst.Fields(idColonne - 1).Name ' copie du nom du champ
    Next idColonne
 
    idColonneMax = rst.Fields.Count
    idligneMax = xlwsh.Cells(xlwsh.Columns(1).Cells.Count, 1).End(xlUp).Row ' on considère la dernière ligne comme indice maxi
 
    If Not ajout Then ' si pas mode ajout on considère la 1re ligne comme indice maxi
        xlwsh.Range(xlwsh.Cells(2, 1), xlwsh.Cells(idligneMax, idColonneMax)).ClearContents ' efface les anciennes données
        idligneMax = 1 ' on considère la 1re ligne comme indice maxi
    End If
 
    idLigne = idligneMax + 1 ' indice de la 1re ligne vierge sur la feuille du classeur disponible pour l'ajout
 
    xlwsh.Range("A" & idLigne).CopyFromRecordset rst ' copie du contenu du recordset à partir de l'indice de la ligne
 
    MsgBox "Export réalisé avec succès !", vbExclamation
 
err_ExportData:
 
    If Err.Number <> 0 Then
        MsgBox (Err.Description)
    End If
 
    On Error Resume Next
 
    xlapp.ScreenUpdating = True ' active le rafraîchissement automatique de l'écran
    xlapp.EnableEvents = True ' active les événements
    xlapp.Calculation = xlCalculationAutomatic ' active les calculs automatiques des formules
 
    If Not (rst Is Nothing) Then
        rst.Close
    End If
 
    ' libère et ferme les variables objet
    Set rst = Nothing
    Set dbs = Nothing
 
    Set xlwsh = Nothing
 
    xlwbk.Close True ' on ferme avec sauvegarde du classeur
    Set xlwbk = Nothing
 
    xlapp.Quit ' quitte Excel
    Set xlapp = Nothing
 
End Sub
 
Function IsFile(ByVal fileName As String) As Boolean
     IsFile = (Dir(fileName) > "")
End Function
 
Public Function ExtractFileName(ByVal FilePath As String) As String
        ExtractFileName = Mid(FilePath, InStrRev(FilePath, "\") + 1)
End Function

On exporte simplement en passant à la procédure les bons arguments :

Code : Sélectionner tout - Visualiser dans une fenêtre à part
ExportData "select * from T_Article where Export=False order by DesignationArticle;", "c:\Documents\Articles.xlsx", "Feuil1", True

Envoyer le billet « Piloter Excel depuis Access : les bonnes pratiques » dans le blog Viadeo Envoyer le billet « Piloter Excel depuis Access : les bonnes pratiques » dans le blog Twitter Envoyer le billet « Piloter Excel depuis Access : les bonnes pratiques » dans le blog Google Envoyer le billet « Piloter Excel depuis Access : les bonnes pratiques » dans le blog Facebook Envoyer le billet « Piloter Excel depuis Access : les bonnes pratiques » dans le blog Digg Envoyer le billet « Piloter Excel depuis Access : les bonnes pratiques » dans le blog Delicious Envoyer le billet « Piloter Excel depuis Access : les bonnes pratiques » dans le blog MySpace Envoyer le billet « Piloter Excel depuis Access : les bonnes pratiques » dans le blog Yahoo

Mis à jour 07/03/2020 à 08h21 par ClaudeLELOUP

Tags: access, excel, piloter, vba
Catégories
Access , VBA , Excel

Commentaires

  1. Avatar de curt
    • |
    • permalink
    Bonjour Denis,

    c'est simple, précis et efficace. Merci pour ce partage qui en sortira plus d'un de la panade.

    Curt
  2. Avatar de User
    • |
    • permalink
    Citation Envoyé par curt
    Bonjour Denis,

    c'est simple, précis et efficace. Merci pour ce partage qui en sortira plus d'un de la panade.

    Curt
    Merci beaucoup curt, c'est en effet le but recherché
  3. Avatar de mfoxy
    • |
    • permalink
    Bonjour,

    Joli petit billet Denis, qui m'ouvre de nouvelle perspective au niveau automation access.

    Habituellement, je travail à l inverse et questionne access via vba excel.

    Je prends donc bonne note de ta méthodologie, au cas où.

    Bat,
  4. Avatar de User
    • |
    • permalink
    Citation Envoyé par mfoxy
    Bonjour,

    Joli petit billet Denis, qui m'ouvre de nouvelle perspective au niveau automation access.

    Habituellement, je travail à l inverse et questionne access via vba excel.

    Je prends donc bonne note de ta méthodologie, au cas où.

    Bat,
    Merci bien

    Oui tout à fait, j'ai simplifié volontairement l'exemple, mais on peut adapter les choses :

    • transfert dans les 2 sens entre une base Access et un classeur
    • entre 2 classeurs
    • ou même encore d'autres manipulations



    Cordialement,
  5. Avatar de Baptou88
    • |
    • permalink
    Merci pour ce tuto , très intéressant !

    Mais comment on instancie les variables objets si on veut travaillé en LateBinding?
    Notamment si on ne veut pas activé la reference (Microsoft Excel 16.0 Object Library)

    merci du retour
  6. Avatar de User
    • |
    • permalink
    Citation Envoyé par Baptou88
    Merci pour ce tuto , très intéressant !

    Mais comment on instancie les variables objets si on veut travaillé en LateBinding?
    Notamment si on ne veut pas activé la reference (Microsoft Excel 16.0 Object Library)

    merci du retour
    Merci à vous

    En fait, il n'y a pas grande différence dans le code, il faut juste déclarer au début les variables en tant qu'objets :

    Code VBA : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
        Dim xlapp As Object ' variable Objet pour faire référence à l'application Excel
        Dim xlwbk As Object ' variable Objet pour faire référence au classeur
        Dim xlwsh As Object ' variable Objet pour faire référence à la feuille
        ...

    En plus, dans l'exemple, j'utilise des constantes (xlCalculationManual, xlCalculationAutomatic, xlUp,..) qui ne sont pas accessibles en late binding, il faut les remplacer par leur valeur :

    Code VBA : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    Const xlCalculationAutomatic = -4105
    Const xlCalculationManual =	-4135
    Const xlUp = -4162
    ...

    Cdlt,
  7. Avatar de Baptou88
    • |
    • permalink
    Citation Envoyé par User
    Merci à vous

    En plus, dans l'exemple, j'utilise des constantes (xlCalculationManual, xlCalculationAutomatic, xlUp,..) qui ne sont pas accessibles en late binding, il faut les remplacer par leur valeur :

    Code VBA : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    Const xlCalculationAutomatic = -4105
    Const xlCalculationManual =	-4135
    Const xlUp = -4162
    ...

    Merci Beaucoup ! c'est ces infos qu'il me manquait en particulier !