Exemple d'interaction Access-Excel

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

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 1^re 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) :

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

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 1^re ligne contient les titres de colonnes) jusqu'à la cellule le plus à droite de la 30 000^e 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 1^er 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.