GRATUIT

Vos offres d'emploi informatique

Développeurs, chefs de projets, ingénieurs, informaticiens
Postez gratuitement vos offres d'emploi ici visibles par 4 000 000 de visiteurs uniques par mois

emploi.developpez.com

Exemple d'interaction Access-Excel

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 17 commentaires Donner une note à l'article (5) 

Article lu   fois.

L'auteur

Profil ProSite personnel

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

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.

Image non disponible

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).

C:\MesDocuments\PrintScreen\k323.jpgL'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

Image non disponible

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

Image non disponible

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

Image non disponible

Et finalement ce message :

Image non disponible

À 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 

Image non disponible
  • 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

 
Sélectionnez
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 String On 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) :

Image non disponible

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.

Image non disponible
 
Sélectionnez
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 :

Image non disponible

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 Image non disponible pour vider la table de son contenu actuel ;

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

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

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 :

 
Sélectionnez
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.
Public Sub MettreAJour()
  Dim xlApp As Excel.Application
  Dim xlSheet As Excel.Worksheet
  Dim xlBook As Excel.Workbook
  Dim db As DAO.Database, rs As DAO.Recordset
  Dim i As Integer, j As Integer
  Dim depart As Double
  ' Mémoriser l'instant de démarrage pour mesurer la durée du traitement
  depart = Now()
  ' Accéder à la feuille BD de Habille.xls
  Set xlApp = CreateObject("Excel.Application")
  Set xlBook = xlApp.Workbooks.Open(CurrentProject.Path & "\Habille.xls")
  xlApp.Sheets("BD").Select
  xlApp.ActiveSheet.Range("A2:H30000").Value = ""  'pour réinitialiser la plage
  Set db = CurrentDb
  ' Créer un jeu d'enregistrements avec la table tAExporter
  Set rs = db.OpenRecordset("rTransformer")
  ' Copier chaque enregistrement cellule par cellule
  i = 2
  Do Until rs.EOF
    For j = 0 To rs.Fields.Count - 1
      xlApp.ActiveSheet.Range(Chr(65 + j) & i) = rs(j) 'important que l'ordre des colonnes soit le même
    Next j
    i = i + 1
    rs.MoveNext
  Loop
  ' Code de fermeture
  xlApp.DisplayAlerts = False 'pour éviter la demande compatibilité
  xlBook.Close (True)
  xlApp.DisplayAlerts = True
  xlApp.Quit
  Set xlSheet = Nothing
  Set xlBook = Nothing
  Set xlApp = Nothing
  rs.Close
  Set rs = Nothing
  Set db = Nothing
  'Message de bonne arrivée
  MsgBox "Il y avait " & i - 2 & " enregistrements." & vbLf _
                     & "Durée d'exécution : " & Now() - depart
End Sub

III-D-1. Explication du code

Image non disponible

On définit les variables qui seront utilisées

Image non disponible

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 ».

Image non disponible

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).

Image non disponible

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.

Image non disponible

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.

Image non disponible

Image non disponibleIci, il faut un peu s'accrocher !

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

Image non disponible

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.

Image non disponible

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) :

Image non disponible

Et mutatis mutandis pour les autres valeurs de j.

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

Image non disponible

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

Image non disponible

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

Image non disponible

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

Image non disponible

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 :

http://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

    http://claudeleloup.developpez.com/ForumAccess/k309.jpg

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

    http://claudeleloup.developpez.com/ForumAccess/k310.jpg

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

    http://claudeleloup.developpez.com/ForumAccess/k311.jpg
  • Les données de Original.xls ont été bricolées, les résultats obtenus dans Habille.xls sont donc farfelus.

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   


CInt( ) dans sa version anglo-saxonne.

  

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.