Exemple d'interaction Access-Excel

Claude Leloup

Cet article montre comment on peut traiter avec Access les données d'une feuille Excel et exporter le résultat dans une autre feuille Excel.

Ceci pour répondre à cette question posée sur le forum Commentez

- Titre : Exemple d'interaction Access-Excel

- Auteur : Claude Leloup

- Parution : 17 mai 2014

- Licence : Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright ® 2014 Claude Leloup. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Sommaire

- I. Le contexte

- II. Utiliser une feuille Excel comme une table

- III. Réexporter les données vers Excel

- III-A. Les transformations à opérer

- III-B. Trois fonctions pour faciliter l'écriture

- III-B-1. Explication du code

- III-C. Une requête pour construire le résultat

- III-D. Réexporter le résultat de la requête vers un classeur Excel

- III-D-1. Explication du code

- IV. Un formulaire pour déclencher le processus

- V. Télécharger la base de données et les données du test

- VI. Remarques au sujet des données de test

I - Le contexte

Des données brutes sont livrées sous la forme d'une feuille Excel par un système extérieur à l'entreprise.

On voudrait intégrer ces données dans une application Excel (calcul de ratios, statistiques diverses…) existante.

Le problème se résume donc à « habiller » les données brutes pour que leur forme soit compatible avec ce qu'attend le système Excel en place.

L'idéal serait d'obtenir directement du logiciel externe une feuille Excel à notre mesure, on éviterait ainsi la deuxième étape (généralement chronophage et sujette à erreur de manipulation).

Mais :

- on n'a pas accès au logiciel ;

- on voudrait garder telle quelle la 3e étape (préserver les habitudes des utilisateurs finals).

L'idée : automatiser la 2e étape en utilisant Access.

C'est le propos de cet article.

II - Utiliser une feuille Excel comme une table

Nous allons lier la feuille Excel originale (celle produite par le logiciel externe).

Dans la barre des menus : Fichier > Données externes > Lier les tables

Une fenêtre s'ouvre qui vous permet de rechercher le fichier ad hoc…

… et vous cliquez sur « Attacher », il vient une série de questions posées par l'assistant

Et finalement ce message :

À partir de maintenant, la table « Original » va se comporter, à peu de chose près, comme une table Access traditionnelle.

III - Réexporter les données vers Excel

III-A - Les transformations à opérer

- Dans Original, la colonne N°_ est de type « Texte » dans la feuille cible, c'est du « Numérique ».
On ne peut pas modifier la définition d'une table attachée, dès lors, nous devrons transformer.
Access prévoit une fonction pour une telle transformation : CEnt ( )(1).

- Original contient une colonne avec une ville, un espace, un type d'activité, un espace et l'indication Midi ou Soir. À l'output, on désire cette information en trois colonnes.
Nous écrivons trois fonctions pour ce genre de transformation.

III-B - Trois fonctions pour faciliter l'écriture

Public Function Ville(Pdv As String) As String
Dim Txt() As String
Txt = Split(Pdv, " ")
Ville = Txt(0)
End Function

Public Function Activite(Pdv As String) As String
Dim Txt() As String
Txt = Split(Pdv, " ")
Activite = Txt(1)
End Function
Public Function Service(Pdv As String) As String
Dim Txt() As String
Txt = Split(Pdv, " ")
Service = Txt(1)
End Function

III-B-1 - Explication du code

Dim Txt() As StringOn définit un tableau (d'un nombre de colonnes non spécifié).

Txt = Split(Pdv, " ")On affecte à chaque colonne de ce tableau et dans l'ordre chacun des morceaux de texte qui sont séparés par un espace.

Donc si la fonction est appelée avec l'argument « Lyon Restaurant Midi », la première colonne de Txt contiendra Lyon, la deuxième Restaurant et la troisième Midi.

Access numérote les colonnes à partir de zéro, on accède donc au contenu de la 1re colonne avec la syntaxe Txt(0).

D'une manière générale, pour se documenter sur un mot-clé de code (par exemple Split) ou sur les propriétés d'un formulaire, d'un état, de leurs contrôles :
- afficher l'objet en mode création ;
- cliquer sur la propriété, elle se met alors en surbrillance ;
- enfoncer la touche <F1>.
L'aide Access s'ouvre à la bonne page.
On peut aussi :
- ouvrir l'aide <F1>, choisir l'onglet « Aide intuitive » et suivre les instructions ;
- ouvrir la fenêtre d'exécution (<Ctrl> + G), saisir un mot-clé, y placer le curseur de la souris et presser <F1>.

Ces trois fonctions sont logées dans un module que nous avons appelé mFonctions.

Pour tester, ouvrez la fenêtre d'exécution (<ALT> G) :

III-C - Une requête pour construire le résultat

Pour la suite du processus de transfert, le nom des colonnes de la requête n'a pas d'importance, par contre, l'ordre de ses colonnes doit être rigoureusement le même que l'ordre des colonnes de la feuille Excel cible.

SELECT CInt([N°_]) AS Num, Original.[Article/Produit], Original.QTE, Original.[Prix Vte], Original.Coût, Ville([Point de Ventes]) AS Ville, Activite([Point de Ventes]) AS Activité, Service([Point de Ventes]) AS Service
FROM Original
WHERE (((Original.[Article/Produit]) Is Not Null));

qui ramène ceci :

III-D - Réexporter le résultat de la requête vers un classeur Excel

La première idée serait de lier la feuille Excel cible et la considérer comme une table Access.

Pas si simple !

Dans notre cas, ce qu'il faudrait faire, c'est remplacer dans la feuille Excel les données du mois passé par celles de ce mois-ci.

Si on avait affaire à une table banale, nous ferions deux requêtes :

- une requête de type pour vider la table de son contenu actuel ;

- une seconde de type pour la remplir avec les données nouvelles.

Dans une table liée depuis Excel, on ne peut pas supprimer des enregistrements.

Il faut donc s'y prendre autrement : nous allons piloter Excel depuis Access :

- ouvrir la feuille ;

- effacer ce qu'elle contient comme données ;

- injecter les données de la requête dans la feuille, cellule par cellule.

Il faut effacer le contenu des cellules et non pas supprimer les lignes, car une suppression de lignes entraînerait une modification par Excel des formules contenues dans son classeur !

Voici le code :

1. Public Sub MettreAJour()

2. Dim xlApp As Excel.Application

3. Dim xlSheet As Excel.Worksheet

4. Dim xlBook As Excel.Workbook

5. Dim db As DAO.Database, rs As DAO.Recordset

6. Dim i As Integer, j As Integer

7. Dim depart As Double

8. ' Mémoriser l'instant de démarrage pour mesurer la durée du traitement

9. depart = Now()

10. ' Accéder à la feuille BD de Habille.xls

11. Set xlApp = CreateObject("Excel.Application")

12. Set xlBook = xlApp.Workbooks.Open(CurrentProject.Path & "\Habille.xls")

13. xlApp.Sheets("BD").Select

14. xlApp.ActiveSheet.Range("A2:H30000").Value = ""  'pour réinitialiser la plage

15. Set db = CurrentDb

16. ' Créer un jeu d'enregistrements avec la table tAExporter

17. Set rs = db.OpenRecordset("rTransformer")

18. ' Copier chaque enregistrement cellule par cellule

19. i = 2

20. Do Until rs.EOF

21. For j = 0 To rs.Fields.Count - 1

22. xlApp.ActiveSheet.Range(Chr(65 + j) & i) = rs(j) 'important que l'ordre des colonnes soit le même

23. Next j

24. i = i + 1

25. rs.MoveNext

26. Loop

27. ' Code de fermeture

28. xlApp.DisplayAlerts = False 'pour éviter la demande compatibilité

29. xlBook.Close (True)

30. xlApp.DisplayAlerts = True

31. xlApp.Quit

32. Set xlSheet = Nothing

33. Set xlBook = Nothing

34. Set xlApp = Nothing

35. rs.Close

36. Set rs = Nothing

37. Set db = Nothing

38. 'Message de bonne arrivée

39. MsgBox "Il y avait " & i - 2 & " enregistrements." & vbLf _

40. & "Durée d'exécution : " & Now() - depart

41. End Sub

III-D-1 - Explication du code

On définit les variables qui seront utilisées

C'est la syntaxe pour ouvrir une session Excel, ouvrir un classeur ( CurrentProject.Path donne le chemin de la base de données Access, c'est en effet dans ce répertoire que nous avions choisi de loger le fichier Excel à compléter).
C'est dans la feuille « BD » de ce classeur qu'on loge les données de base « habillées ».

On blanchit les cellules depuis la cellule A2 (la 1re ligne contient les titres de colonnes) jusqu'à la cellule le plus à droite de la 30 000e ligne (on s'offre une marge de sécurité, sachant qu'il y a habituellement de 20 à 25 000 lignes de données).

On crée un jeu des enregistrements avec ceux ramenés par la requête rTransformer : cela va nous permettre de traiter ces enregistrements un après l'autre et d'accéder à chacun des champs de rTransformer.

On va se servir de la variable i pour exprimer le n° de la ligne que l'on traite dans la feuille Excel.
On lui donne 2 comme valeur de départ (la ligne 1 est occupée par les entêtes de colonne dans la feuille Excel).
On déclenche la lecture séquentielle de chaque enregistrement de rTransformer.
Sur chaque enregistrement on fait un traitement que nous verrons plus bas.
Quand ce processus est accompli, on incrémente i d'une unité et on passe à l'enregistrement suivant de rTransformer et on boucle jusqu'à avoir traité le dernier enregistrement.
Quand Access tente de lire l'enregistrement « qui suit le dernier », il trouve l'EOF (End-of-file) du jeu et sort de la boucle.

Ici, il faut un peu s'accrocher !

rs.Fields.Countest une variable qui indique le nombre de colonnes dans sTransformer. Dans notre exemple, il y en a 8 :

Donc la traduction française de la ligne 21 peut s'énoncer ainsi :
« Avec des valeurs de j variant de zéro à 7 » faites ce que j'indique aux lignes suivantes (ici la ligne 21) et quand vous rencontrerez "Next", vous passerez à la valeur de j suivante ».
Concrètement le programme va exécuter 8 fois l'instruction de la ligne 22.

Ce qu'on doit faire, c'est :

mettre dans la colonne A de Excel, ce qui se trouve dans la colonne 0 de rTransformer ;
mettre dans la colonne B de Excel, ce qui se trouve dans la colonne 1 de rTransformer ;
…
mettre dans la colonne H de Excel, ce qui se trouve dans la colonne 7 de rTransformer.

On aurait pu l'écrire en 8 lignes de code, mais en rusant un peu une seule suffit et quel que soit le nombre de colonnes (pratique, si on doit un jour ajouter ou supprimer une colonne, le code restera bon !).

Voici l'astuce, imaginez que nous sommes occupés à traiter le 1er enregistrement de rTransformer

Nous aurons pour j = 0 (rappelons que, à cet instant, i vaut alors 2) :

Et mutatis mutandis pour les autres valeurs de j.

Ça fonctionne pour autant que le nombre de colonnes soit de maximum 26 !

On libère la mémoire pour sortir proprement.

On envoie un message à l'utilisateur pour signaler la fin du processus et sa durée (± 4 minutes pour 8 colonnes sur 23 000 lignes).

Ce code est aussi à loger dans le module mFonctions.

IV - Un formulaire pour déclencher le processus

Un clic sur le bouton Traiter, déclenche le processus qui se termine comme ceci :

V - Télécharger la base de données et les données du test

Une archive que vous devez décompresser dans un même répertoire se trouve ici :

https://claudeleloup.developpez.com/tutoriels/access/interaction-access-excel/InterAccessExcel.zip

VI - Remarques au sujet des données de test

- J'utilise Excel2010, si vous disposez d'une version moins récente, vous aurez sans doute un message d'erreur comme celui-ci

Si c'est le cas, cliquez sur OK pour fermer cette fenêtre et dans le menu Outils, cliquez sur Références…

Dans la fenêtre qui s'ouvre, recherchez la bibliothèque qui correspond à votre version d'Excel et vous cochez

- Les données de Original.xls ont été bricolées, les résultats obtenus dans Habille.xls sont donc farfelus.

(1) CInt( ) dans sa version anglo-saxonne.