Importation de données depuis une base de données SQL Server, Excel VBA

Importation de données depuis une base de données SQL Server, Excel VBA

Voici une explication détaillée du processus d’importation de données depuis une base de données SQL Server dans Excel via VBA (Visual Basic for Applications). Cette explication vous guidera à travers la création du code VBA nécessaire, la configuration de la connexion et l’importation des données dans une feuille Excel.
Prérequis :
1. SQL Server : Vous devez avoir accès à une base de données SQL Server et connaître le nom du serveur, le nom de la base de données, ainsi que vos identifiants (nom d’utilisateur et mot de passe).
2. Excel : Vous devez utiliser Excel avec l’éditeur VBA (généralement disponible dans l’onglet Développeur d’Excel).
3. Microsoft ActiveX Data Objects (ADO) : ADO est une technologie Microsoft qui permet de se connecter et d’interroger des bases de données. Vous devez vous assurer que la bibliothèque Microsoft ActiveX Data Objects est activée dans Excel VBA.
Étapes pour activer ADO dans Excel :
1. Ouvrez l’éditeur Visual Basic for Applications (VBA) en appuyant sur Alt + F11.
2. Allez dans Outils → Références.
3. Dans la boîte de dialogue, faites défiler et cochez Microsoft ActiveX Data Objects x.x Library (x.x correspond à la version disponible, par exemple, 6.1).
Code VBA pour importer des données depuis SQL Server dans Excel :
Voici un exemple détaillé de code VBA pour importer des données depuis SQL Server dans Excel. Cet exemple se connecte à la base de données, exécute une requête SQL et renvoie les résultats dans une feuille Excel.
Code complet :

Sub ImportDataFromSQLServer()
    ' Déclarez les objets ADO
    Dim conn As Object
    Dim rs As Object
    Dim connString As String
    Dim query As String
    Dim ws As Worksheet
    Dim rowNum As Long
    Dim colNum As Integer
    ' Définir la feuille de calcul où les données seront importées
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Modifiez en fonction du nom de votre feuille
    ' Effacer les données existantes
    ws.Cells.Clear
    ' Définir la requête SQL à exécuter
    query = "SELECT * FROM your_table_name" ' Modifiez avec votre requête SQL
    ' Définir la chaîne de connexion pour SQL Server
    connString = "Provider=SQLOLEDB;Data Source=your_server_name;" & _
                 "Initial Catalog=your_database_name;" & _
                 "User ID=your_username;" & _
                 "Password=your_password;" ' Modifiez avec vos informations de connexion
    ' Créer l'objet de connexion
    Set conn = CreateObject("ADODB.Connection")
    conn.Open connString
    ' Créer l'objet Recordset
    Set rs = CreateObject("ADODB.Recordset")
    ' Ouvrir le recordset avec la requête SQL
    rs.Open query, conn
    ' Si des données sont retournées, importez-les dans Excel
    If Not rs.EOF Then
        ' Définir les en-têtes (noms des champs) dans la première ligne de la feuille de calcul
        For colNum = 0 To rs.Fields.Count - 1
            ws.Cells(1, colNum + 1).Value = rs.Fields(colNum).Name
        Next colNum
        ' Parcourez le recordset et importez les données ligne par ligne
        rowNum = 2 ' Commencez à partir de la ligne 2 pour éviter d'écraser les en-têtes
        Do Until rs.EOF
            For colNum = 0 To rs.Fields.Count - 1
                ws.Cells(rowNum, colNum + 1).Value = rs.Fields(colNum).Value
            Next colNum
            rs.MoveNext
            rowNum = rowNum + 1
        Loop
    Else
        MsgBox "Aucune donnée retournée par la requête.", vbExclamation
    End If
    ' Fermer les objets
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
    MsgBox "Importation des données terminée !", vbInformation
End Sub

Explication détaillée du code :
1. Déclaration des objets :

• conn : C’est l’objet de connexion qui sera utilisé pour se connecter à SQL Server.
• rs : C’est l’objet Recordset qui contiendra les données retournées par SQL Server.
• connString : Cette chaîne contient les informations de connexion, telles que le nom du serveur, le nom de la base de données et les identifiants.
• ws : Cette variable représente la feuille Excel où les données seront importées.
• rowNum et colNum : Ces variables suivent les numéros de ligne et de colonne lors de l’écriture des données dans la feuille Excel.

2. Définir la feuille de calcul :

• La feuille ws est définie comme la feuille spécifique de votre classeur où les données seront importées (par exemple, « Sheet1 »).

3. Effacer les données existantes :

• La méthode ws.Cells.Clear est utilisée pour effacer toute donnée existante dans la feuille avant d’importer de nouvelles données.

4. Définir la requête SQL :

• La variable query contient la requête SQL que vous souhaitez exécuter sur SQL Server pour récupérer les données (par exemple, SELECT * FROM your_table_name).

5. Chaîne de connexion :

• La chaîne de connexion connString contient les détails de la connexion, tels que le nom du serveur SQL (Data Source), le nom de la base de données (Initial Catalog), et les informations d’identification de l’utilisateur (User ID et Password).

6. Créer l’objet de connexion :

• Set conn = CreateObject("ADODB.Connection") : Cela crée l’objet de connexion pour interagir avec la base de données.

7. Ouvrir la connexion :

• conn.Open connString : Cette méthode ouvre la connexion en utilisant la chaîne de connexion fournie précédemment.

8. Créer l’objet Recordset :

• Set rs = CreateObject("ADODB.Recordset") : Cela crée l’objet Recordset qui contiendra les résultats de la requête SQL.

9. Exécuter la requête SQL :

• rs.Open query, conn : Cela exécute la requête SQL et charge les résultats dans le recordset rs.

10. Écrire les données dans Excel :

• Le code écrit d’abord les noms des champs (en-têtes) du recordset dans la première ligne de la feuille Excel.
• Ensuite, il parcourt chaque ligne du recordset et écrit les données dans les cellules de la feuille, en commençant par la ligne 2 pour ne pas écraser les en-têtes.

11. Fermer la connexion et le recordset :

• Après que les données ont été importées, les méthodes rs.Close et conn.Close sont utilisées pour fermer le recordset et la connexion à la base de données.

12. Nettoyage des objets :

• Set rs = Nothing et Set conn = Nothing libèrent la mémoire des objets une fois qu’ils ne sont plus nécessaires.

13. Boîte de message :

• Une boîte de message apparaît pour indiquer que l’importation est terminée ou pour signaler qu’aucune donnée n’a été retournée par la requête.

Modifications à apporter :

• Chaîne de connexion : Remplacez your_server_name, your_database_name, your_username, et your_password par vos informations réelles de connexion à SQL Server.
• Requête SQL : Modifiez la requête SELECT * FROM your_table_name pour l’adapter à vos besoins.
• Nom de la feuille : Modifiez ThisWorkbook.Sheets("Sheet1") si vous souhaitez importer les données dans une feuille différente.

Conclusion :
Ce code VBA permet de se connecter à une base de données SQL Server, d’exécuter une requête SQL et d’importer les résultats dans une feuille Excel. Ce processus est automatisé et peut être facilement adapté à différents cas d’utilisation, comme l’importation de données spécifiques ou l’automatisation de rapports dans Excel.