Exécuter des requêtes SQL, Excel VBA

Exécuter des requêtes SQL, Excel VBA

Voici une explication détaillée accompagnée d’un code VBA pour exécuter des requêtes SQL dans Excel. Le processus consiste à utiliser VBA pour se connecter à une base de données, envoyer des requêtes SQL et récupérer les données dans Excel.
Ce dont nous avons besoin :
1. ADO (ActiveX Data Objects) : Une bibliothèque COM (Component Object Model) pour interagir avec les bases de données.
2. Chaîne de connexion à la base de données : Cela contient les détails de la connexion à la base de données, comme le serveur, le nom de la base de données et les informations d’authentification.
Processus étape par étape :
1. Ajouter la référence à la bibliothèque ADO : Avant d’écrire le code, vous devez ajouter la référence à la bibliothèque ADO pour pouvoir interagir avec la base de données.

• Ouvrez Excel et appuyez sur Alt + F11 pour ouvrir l’éditeur VBA.
• Dans l’éditeur VBA, allez dans Outils > Références.
• Dans la boîte de dialogue, faites défiler et cochez « Microsoft ActiveX Data Objects x.x Library » (par exemple, 6.1).
• Cliquez sur OK.

2. Configurer la chaîne de connexion : La chaîne de connexion varie en fonction du type de base de données à laquelle vous vous connectez. Voici des exemples pour quelques bases de données courantes :

• • SQL Server :

"Provider=SQLOLEDB;Data Source=NomServeur;Initial Catalog=NomBaseDeDonnées;User ID=VotreNomUtilisateur;Password=VotreMotDePasse;"

• • MySQL :

"Driver={MySQL ODBC 8.0 Driver};Server=NomServeur;Database=NomBaseDeDonnées;User=VotreNomUtilisateur;Password=VotreMotDePasse;"

• • Access :

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\chemin\vers\votre\base.accdb;"

3. Code VBA pour exécuter des requêtes SQL :
Voici un exemple détaillé de code pour exécuter des requêtes SQL dans Excel avec VBA :

Sub RunSQLQuery()
    ' Déclaration des objets de connexion et de recordset
    Dim conn As Object
    Dim rs As Object
    Dim connString As String
    Dim query As String
    Dim sheet As Worksheet
    Dim rowNum As Long
    ' Création de l'objet de connexion
    Set conn = CreateObject("ADODB.Connection")
    ' Définissez ici votre chaîne de connexion
    ' Remplacez par vos détails de connexion réels
    connString = "Provider=SQLOLEDB;Data Source=VotreServeur;Initial Catalog=VotreBaseDeDonnées;User ID=VotreNomUtilisateur;Password=VotreMotDePasse;"
    ' Ouvrir la connexion
    conn.Open connString
    ' Définir la requête SQL à exécuter
    query = "SELECT * FROM VotreNomTable;"  ' Exemple : remplacez par votre requête SQL
    ' Créer un recordset pour stocker les données
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open query, conn
    ' Définir la feuille de calcul où vous voulez sortir les données
    Set sheet = ThisWorkbook.Sheets("Feuille1")  ' Remplacez par le nom de votre feuille
    ' Commencer à afficher les données à partir de la ligne 2 (laisser la ligne 1 pour les en-têtes)
    rowNum = 2
    ' Écrire les en-têtes des colonnes depuis les noms des champs du recordset
    For i = 0 To rs.Fields.Count - 1
        sheet.Cells(1, i + 1).Value = rs.Fields(i).Name
    Next i
    ' Boucler à travers le recordset et écrire chaque ligne dans Excel
    Do While Not rs.EOF
        For i = 0 To rs.Fields.Count - 1
            sheet.Cells(rowNum, i + 1).Value = rs.Fields(i).Value
        Next i
        rs.MoveNext
        rowNum = rowNum + 1
    Loop
    ' Fermer le recordset et la connexion
    rs.Close
    conn.Close
    ' Nettoyage des objets
    Set rs = Nothing
    Set conn = Nothing
    ' Avertir l'utilisateur que la requête a été exécutée avec succès
    MsgBox "Requête exécutée avec succès et données importées dans Excel.", vbInformation
End Sub

Explication du code :
1. Objet de connexion (conn) :
L’objet de connexion est utilisé pour ouvrir une connexion avec la base de données à l’aide d’une chaîne de connexion.
2. Objet Recordset (rs) :
Le recordset est utilisé pour stocker les données renvoyées par votre requête SQL. Vous pouvez le considérer comme un conteneur pour les résultats de la requête SQL.
3. Chaîne de connexion :
La chaîne de connexion contient les détails nécessaires pour se connecter à la base de données, comme l’adresse du serveur, le nom de la base de données et les informations d’authentification.
4. Requête SQL :
Vous pouvez modifier la variable query pour y insérer n’importe quelle requête SQL valide. Dans cet exemple, nous sélectionnons toutes les lignes (SELECT *) d’une table appelée VotreNomTable.
5. Boucle pour écrire les données :
Le code boucle ensuite à travers le Recordset et écrit chaque champ (colonne) et chaque ligne de données dans Excel, en commençant par la ligne 2 (la ligne 1 étant utilisée pour les en-têtes).
6. En-têtes de colonnes :
Les noms des champs de la requête SQL sont écrits dans la première ligne d’Excel comme en-têtes de tableau. La propriété Fields.Count permet d’obtenir le nombre de champs (colonnes) dans le recordset, et les noms des champs sont écrits dans la première ligne.
7. Fermeture et nettoyage :
Le Recordset et la Connexion sont fermés avec rs.Close et conn.Close respectivement, pour s’assurer que les ressources sont libérées après utilisation.
8. Boîte de message :
Après l’exécution de la requête et l’importation des données dans Excel, une boîte de message est affichée pour informer l’utilisateur du succès de l’opération.
Points à noter :

• Gestion des erreurs :
  Ce code ne comprend pas de gestion des erreurs. Vous pouvez ajouter des instructions On Error pour gérer les éventuels problèmes, comme des échecs de connexion ou des erreurs de requêtes.
• Type de base de données :
  Assurez-vous que la chaîne de connexion correspond au type de base de données que vous utilisez (SQL Server, MySQL, Access, etc.).
• Sécurité :
  Faites attention aux données sensibles comme les identifiants de connexion à la base de données. Il est préférable de ne pas coder en dur les identifiants dans votre code et d’utiliser des moyens sécurisés pour les stocker si nécessaire.
• Exécution du code :
  Pour exécuter le code, appuyez sur Alt + F8, sélectionnez la macro RunSQLQuery et cliquez sur Exécuter. Les données seront récupérées de la base de données et affichées dans la feuille de calcul spécifiée.

Conclusion :
Cette approche vous permet d’intégrer directement des requêtes SQL dans Excel via VBA, ce qui en fait un outil puissant pour extraire et analyser des données provenant de bases de données externes. Vous pouvez personnaliser la requête, la chaîne de connexion et la mise en forme des résultats en fonction de vos besoins spécifiques.