L’objectif est de vous montrer les possibilités de requêter facilement un tableau Excel avec un peu de code VBA. A travers 5 exemples, nous allons donc voir comment interroger un tableau avec des macros très simples.

Choix de l’exemple

Nous allons utiliser une table toute simple facile à reproduire. Toutefois, vous pouvez télécharger les fichiers:

Voici les 5 exemples que nous allons réaliser:

Créer une procédure pour créer liste déroulante des prénoms ;
Créer une fonction qui va compter le nombre d’enregistrements pour un prénom donné ;
Créer une fonction qui va récupérer le tableau complet pour un prénom donné ;
Créer une fonction qui calculera la somme d’après une date ;
Vérifier si les jointures sont possibles.

Table et champ nommé

Nommez votre tableau TabBD

Sélectionner une cellule du tableau
Outils de tableau > Création > Propriétés > Nom du tableau: TabBD

Il faut définir un champ nommé de la zone à requêter.

Attention: le champ ne peut pas être dynamique.

Nous essaierons de le faire avec un tableau Excel pour vérifier.

Sélectionner B5:D27
Formules > Noms définis > Définir un nom > « BD »

Deux choses à savoir:

J’ai choisi de sélectionner jusqu’à la ligne 27 alors que le tableau s’arrête en ligne 26. Cela afin de ne pas avoir de champ dynamique (=TabBD[#Tout]). J’aurais pu modifier la valeur D27 en D26 sur la fenêtre qui apparaissait mais cela n’a pas vraiment d’importance sur la table à requêter. En revanche, il fallait bien prendre des lignes en plus et non des colonnes en plus car à chaque colonne correspond un entête qui sert de nom de champ de la table lors de la requête.
Si votre tableau doit évoluer et surtout être plus grand, vous pouvez dès le départ choisir une sélection plus importante. Cela n’aura pas d’impact sur la performance. Par exemple, vous pouvez très bien choisir un champ nommé B5:D10000 pour être plus serein, et même plus si vous pensez que cela est nécessaire.

N’oubliez pas de sauvegarder votre fichier au format .XLSM

La préparation

Onglet Développeur

Tout d’abord, s’assurer que l’onglet développeur est bien activé.

Fichier > Options
Personnaliser le ruban
L’onglet développeur doit être coché.

Ensuite, il faut ouvrir l’éditeur VBE (Visual Basic Editor) en utilisant le raccourci ALT+F11

La première étape consiste à créer un nouveau module.

Clic-droit sur « Microsoft Excel Objects » > Insertion > Module

Renommer le nom du module: ReqExcel
Si la fenêtre propriété n’apparaît pas, faire F4 ou Affichage > Fenêtre Propriétés

Cliquer ensuite deux fois sur le module pour faire apparaître la feuille de code.

Les Références

Nous allons utiliser ce qu’on appelle la technologie DAO (Data Access Object) pour faire des requêtes Excel. A partir de là , il est indispensable d’ajouter la référence DAO. La référence pourra varier en fonction des versions d’Excel.

Aller dans Outils > Références
Choisir « Microsoft DAO 3.6 Object Library

Si vous avez le message d’erreur suivant, il vous faudra alors choisir une autre référence.

Aller dans Outils > Références
Choisir « Microsoft Office 16.0 Access Database Engine Object Library

Le numéro 16.0 correspond à votre version.

A partir de maintenant, vous pouvez donc programmer du DAO dans VBA !

5 exemples pour mieux comprendre

Création de la liste déroulante

Dans la zone de code, vous pouvez à présent taper le code suivant:

Sub AjoutListeDeroulante()

'Déclarer les variables
Dim TableauExcel As DAO.Database
Dim RS As DAO.Recordset
Dim ListePrenoms As String

'============================================================
'Etape 1 : création du code SQL
'============================================================
Req = "SELECT Distinct BD.[Prénom] FROM BD "

'============================================================
'Etape 2 : Execution du code et récupération dans un RecordSet
'============================================================

'Cette instruction fait considérée Excel comme équivalent base de données
'Ouvrir la base de données
Set TableauExcel = OpenDatabase(ThisWorkbook.FullName, False, False, "Excel 8.0")

'Exécuter la requête
Set RS = TableauExcel.OpenRecordset(Req)

'============================================================
'Etape 3 : Création de la liste déroulante
'============================================================
If RS.RecordCount > 0 Then
   'Pour chaque enr, ajouter le prénom
   ListePrenoms = ""
   RS.MoveFirst

   'Pour chaque ligne de résultat
   For i = 1 To RS.RecordCount
      'On vérifie que le prénom est renseigné car n'oublions pas que la zone BD contient des valeurs vides (en ligne 27)
      If RS!Prénom <> "" Then
         ListePrenoms = ListePrenoms & "," & RS!Prénom
      End If
      'On passe à l'enregistrement suivant      
      RS.MoveNext
   Next i

   'Création Validation de donnée en N6 (suppression puis création)
   With Range("N6").Validation
      .Delete
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=ListePrenoms
   End With
End If

'On mets à vide
Range("N6") = ""

'============================================================
'Etape 4 : Fermer et détruire les objets RecrodSet et BaseDeDonnées
'============================================================
'Fermer les requêtes
RS.Close
Set RS = Nothing

'Fermeture de la base de données
TableauExcel.Close
Set TableauExcel = Nothing

End Sub

Vous pouvez vérifier en changeant la valeur de la cellule N6 d’après la liste déroulante créée.

L’objectif est d’initialiser cette liste déroulante au lancement du fichier.

Double-cliquer sur « ThisWorkbook »

Choisir Workbook et Open dans les listes déroulantes
Ajouter le code comme ci-dessous:

Dès que vous ouvrirez le fichier, la liste déroulante sera automatiquement créée.

Compter le nombre d’enregistrements

Il s’agit ici de créer une fonction qui retourne le nombre d’enregistrements en fonction du prénom.

Veuillez recopier le code suivant:

Function CompterEnr(ByVal sPrenom As String) As Integer

   'Déclaration des variable
   Dim TableauExcel As DAO.Database
   Dim RS As DAO.Recordset

   '============================================================
   'Etape 1 : création du code SQL
   '============================================================
   Req = "SELECT COUNT(*) AS NbEnr FROM BD WHERE BD.[Prénom] = '" & sPrenom & "'"

   '============================================================
   'Etape 2 : Execution du code et récupération dans un RecordSet
   '============================================================
   Set TableauExcel = OpenDatabase(ThisWorkbook.FullName, False, False, "Excel 8.0")
   Set RS = TableauExcel.OpenRecordset(Req)

   '============================================================
   'Etape 3 : Récupération du résultat
   '============================================================
   CompterEnr = RS!NbEnr

   '============================================================
   'Etape 4 : Fermer et détruire les objets RecrodSet et BaseDeDonnées
   '============================================================
   'Fermer les requêtes
   RS.Close
   Set RS = Nothing

   'Fermeture de la base de données
   TableauExcel.Close
   Set TableauExcel = Nothing

End Function

Il est possible de réduire l’ensemble pour finalement n’avoir que ce code:

Function CompterEnr(ByVal sPrenom As String) As Integer
   Req = "SELECT COUNT(*) AS NbEnr FROM BD WHERE BD.[Prénom] = '" & sPrenom & "'"
   Set TableauExcel = OpenDatabase(ThisWorkbook.FullName, False, False, "Excel 8.0")
   Set RS = TableauExcel.OpenRecordset(Req)
   CompterEnr = RS!NbEnr
End Function

Dans la cellule N7, taper la formule suivante:

=CompterEnr(N6)

Vous pourrez ainsi voir que le calcul fonctionne bien. Il suffit ensuite de modifier le prénom de la cellule N6 pour voir le résultat évolué.

Récupérer toutes les informations pour un prénom

La particularité ici est de recopier l’ensemble des données dans un tableau Excel.

Veuillez taper le code suivant:

Sub TableauPrenoms()
   Req = "SELECT * FROM BD WHERE BD.[Prénom] = '" & Range("N6") & "' ORDER BY BD.[Date]"
   Set TableauExcel = OpenDatabase(ThisWorkbook.FullName, False, False, "Excel 8.0")
   Set RS = TableauExcel.OpenRecordset(Req)

   'Nous allons d'abord effacer les données
   Range("Q:S").ClearContents

   If RS.RecordCount <> 0 Then
      'On affiche les titres des colonnes
      For Champ = 1 To RS.Fields.Count
         'la colonne 16 correspond à la colonne P
         'Le numéro du champ est en base 0, il faut donc soustraire 1
         Cells(6, 16 + Champ) = RS.Fields(Champ - 1).Name
      Next Champ

      'On affiche le résultat
      Range("Q7").CopyFromRecordset RS
   End If
End Sub

Pour que ce soit plus simple pour l’utilisateur, nous allons ajouter un bouton de mise à jour.

Vous pouvez télécharger l’image que j’utilise: Télécharger image.

Insérer > Compléments > Illustrations > Images
Choisir ensuite l’image souhaitée

Placer l’image en P1
Clic-droit sur l’image puis Affecter une macro
Choisir « TableauPrenoms »

Vous pouvez vous amuser à changer de prénom en N6 et voir ce qui se passe lorsque vous cliquez sur le nouveau bouton.

Somme d’après une date

L’objectif ici est de voir comment manipuler une date dans une requête Excel.

Vous pouvez recopier le code suivant.

Function SommeDate(ByVal sDate As Date) As Integer
   Req = "SELECT SUM(BD.[Valeur]) AS Nb FROM BD WHERE BD.[Date] = #" & sDate & "#"
   Set TableauExcel = OpenDatabase(ThisWorkbook.FullName, False, False, "Excel 8.0")
   Set RS = TableauExcel.OpenRecordset(Req)

   'Contrôle si la somme est nulle
   If IsNull(RS!Nb) Then
      SommeDate = 0
   Else
      SommeDate = RS!Nb
   End If
End Function

Remarquez que nous avons mis en la date entre deux #.

Dans la cellule N12, taper la formule suivante:

=SommeDate(N11)

Essayez de modifier la valeur de la cellule N11 et regardez le résultat. Afin de contrôler l’exactitude du résultat, vous pouvez taper la formule suivante dans la cellule N13 (cette formule fait exactement la même chose, sans passer par VBA)

=SOMME.SI(TabBD[Date];$N$11;TabBD[Valeur])

Et les jointures, c’est possible ? …

Une jointure est un lien entre deux tables permettant de récupérer les données des deux tables. Dans notre exemple, la jointure se fait depuis le prénom et nous permettra de récupérer le code.

Nous allons donc pour cela rajouter un nouveau tableau contenant les codes des prénoms.

Nous allons utiliser un nouveau champ nommé.

Sélectionner B5:D27
Formules > Noms définis > Définir un nom > « ListeP »

Ajoutez ensuite le code suivant:

Sub TestJointures()

   'Création requête
   Req = "SELECT ListeP.[Code], BD.[Date], BD.[Valeur]"
   Req = Req & " FROM BD INNER JOIN ListeP ON BD.[Prénom] = ListeP.[Nom] "
   Req = Req & " WHERE BD.[Prénom] = '" & Range("N6") & "'"
   Req = Req & " ORDER BY BD.[Date]"

   'Exécution Requête
   Set TableauExcel = OpenDatabase(ThisWorkbook.FullName, False, False, "Excel 8.0")
   Set RS = TableauExcel.OpenRecordset(Req)

   'Nous allons d'abord effacer les données
   Range("Q16:S23").ClearContents

   If RS.RecordCount <> 0 Then
      'On affiche les titres des colonnes
      For Champ = 1 To RS.Fields.Count
         'la colonne 16 correspond à la colonne P
         'Le numéro du champ est en base 0, il faut donc soustraire 1
         Cells(16, 16 + Champ) = RS.Fields(Champ - 1).Name
      Next Champ
      'On affiche le résultat
      Range("Q17").CopyFromRecordset RS
   End If
End Sub

Vous pouvez voir dans la requête que nous avons utilisé la jointure INNER JOIN.

Ajoutez un bouton comme nous avions fait plus tôt pour lancer la macro ; vous pouvez même créer une nouvelle macro qui lancera les deux macros créées par le bouton Actualiser.

Sub Extractions()
   TableauPrenoms
   TestJointures
 End Sub

Clic-droit sur l’image > Affecter une macro > Extractions

Changez le prénom, cliquez sur l’image et observez !!!!

Et si nous utilisions le tableau…

Prenons le code pour la somme des prénoms et dates.

Modifiez la requête en remplaçant BD Par TabBD

Req = "SELECT SUM(TabBD.[Valeur]) AS Nb FROM TabBD WHERE TabBD.[Date] = #" & sDate & "#"

Recalculer la page en utilisant le raccourci F9 et observez le résultat.

La requête ne trouve pas de résultat et la cellule donne comme résultat: #VALEUR!

En effet, comme nous le mentionnions plus tôt, il n’est pas possible d’utiliser de tableaux dynamiques, que ce soit un champ nommé élastique (avec la fonction DECALER) ou en utilisant un tableau Excel. De ce fait, il est nécessaire de définir une plage assez grande qui sera la source de nos requêtes, à savoir notre table de données.

Conclusion

Nous venons de voir quelques exemples permettant de réaliser des requêtes directement sur des tableaux Excel.

Bien sûr, avec l’arrivée de PowerQuery, de nombreux cas d’utilisations ont été simplifiés. Toutefois, il peut s’avérer encore nécessaire dans certains cas d’utiliser ces possibilités qu’offre EXCEL au niveau VBA.

Utilisez donc ce que vous venez d’apprendre si PowerQuery ne vous permet pas d’obtenir le résultat souhaité. Et dans certains cas, une combinaison des deux peut sembler tout à fait judicieuse.

N’hésitez pas à me faire vos retours sur vos cas d’utilisation. Et surtout n’oubliez pas: Faire toujours le plus simple !

Les meilleures recettes Excel et Power BI

Les meilleures recettes Excel et Power BI

Requêter un tableau Excel

Choix de l’exemple

Table et champ nommé

La préparation

Onglet Développeur

Les Références

5 exemples pour mieux comprendre

Création de la liste déroulante

Compter le nombre d’enregistrements

Récupérer toutes les informations pour un prénom

Somme d’après une date

Et les jointures, c’est possible ? …

Et si nous utilisions le tableau…

Conclusion

J’aime ça :

Articles similaires

Choix de l’exemple

Table et champ nommé

La préparation

Onglet Développeur

Les Références

5 exemples pour mieux comprendre

Création de la liste déroulante

Compter le nombre d’enregistrements

Récupérer toutes les informations pour un prénom

Somme d’après une date

Et les jointures, c’est possible ? …

Et si nous utilisions le tableau…

Conclusion

Partager :

J’aime ça :

Articles similaires

En savoir plus sur Cooking-Excel