MS Excel - Se connecter à une base de données SQL Server, exécuter une requête et récupérer les résultats - Pas à pas

En avril 2024, j'ai écrit un petit blog post à propos d'un script VBA qui se connecte à une base MS SQL, exécute une requête SELECT pour récupérer des données et les place dans une feuille Excel.

Réécrivons cet article sous la forme d'un tutoriel complet. Nous allons installer et lancer une base de données SQL Server avec Docker, télécharger MS SQL Server Management Studio, nous connecter à notre SQL Server, créer une nouvelle base avec des données bidon et, enfin, récupérer la liste de nos clients dans Excel.

Excel est un front-end confortable, mais un mauvais endroit pour stocker de gros volumes de données. Quand le volume grandit, Pandas - Fusionner deux fichiers ou plus et en créer un seul explique pourquoi, et quoi faire à la place.

Voici le résultat : une requête en direct contre SQL Server, dont les lignes atterrissent directement dans une feuille Excel.

Pourquoi ça fonctionne​

• Une seule classe VBA (clsData) encapsule ADO : chaque sous-routine lui demande simplement un recordset au lieu de bricoler une chaîne de connexion à chaque fois.
• CopyToSheet copie les données une fois — rapide, mais aucun lien vivant avec la base.
• AddQueryTable garde au contraire la connexion active : l'utilisateur final peut faire Données → Actualiser dans Excel à tout moment pour obtenir des données fraîches.

Télécharger SQL Server et créer une base de données bidon​

Vous pouvez sauter cette étape si vous disposez déjà d'une instance SQL Server à laquelle vous connecter.

Lisez l'article complet Jouer avec Microsoft SQL Server 2022 avec Docker pour plus d'informations sur la façon de faire tourner votre propre instance SQL Server.

En bref :

• Ouvrez une console (DOS, PowerShell ou Linux),
• Exécutez docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=2Secure*Password2" -p 1433:1433 --name sqlserverdb -h mysqlserver -d mcr.microsoft.com/mssql/server:2022-latest pour télécharger SQL Server et en lancer une instance dans un container Docker,
• Téléchargez SQL Server Management Studio si vous ne l'avez pas encore. C'est gratuit.
• Une fois installé, démarrez SQL Server Management Studio.

Utilisez les valeurs ci-dessous pour l'authentification :

Vous voilà dans SSMS. Nous allons créer une base bidon. À l'aide d'une IA, j'ai demandé un script, le voici :

-- Create a new database
CREATE DATABASE SampleDB;?
GO?

-- Switch to the new database
USE SampleDB;?
GO

-- Create a sample table
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY IDENTITY(1,1),?
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100),
    City VARCHAR(50)
);
GO

-- Insert sample data
INSERT INTO Customers (FirstName, LastName, Email, City) VALUES
('John', 'Doe', 'john.doe@example.com', 'New York'),
('Jane', 'Smith', 'jane.smith@example.com', 'London'),
('David', 'Lee', 'david.lee@example.com', 'Paris'),
('Emily', 'Brown', 'emily.brown@example.com', 'Tokyo'),
('Michael', 'Davis', 'michael.davis@example.com', 'Sydney'),
('Sarah', 'Wilson', 'sarah.wilson@example.com', 'Berlin'),
('Robert', 'Garcia', 'robert.garcia@example.com', 'Madrid'),
('Jennifer', 'Rodriguez', 'jennifer.rodriguez@example.com', 'Rome'),
('William', 'Martinez', 'william.martinez@example.com', 'Toronto'),
('Linda', 'Anderson', 'linda.anderson@example.com', 'Chicago');
GO

-- Verify the data
SELECT * FROM Customers;?
GO

Il suffit de cliquer sur le bouton Execute ou d'appuyer sur F5 pour l'exécuter, c'est-à-dire pour créer notre base SampleDB.

Maintenant, faites un clic droit sur le nœud Databases (dans l'arborescence en haut à gauche) et vous verrez : notre DB est là.

La partie Excel​

Rendez-vous sur https://github.com/cavo789/vba_excel_sql pour récupérer mon code VBA. Ce code est une classe VBA pour Excel qui rend vraiment simple l'accès aux enregistrements stockés dans SQL Server et leur affichage dans une feuille Excel, en gardant ou non la connexion active (vous pouvez donc faire un Refresh à tout moment).

Démarrez Excel et créez un nouveau classeur.

Appuyez sur ALT+F11 pour ouvrir l'interface VBE.

Faites un clic droit sur Microsoft Excel Objects dans le volet de gauche Project - VBAProject et insérez une nouvelle classe.

Allez sur https://github.com/cavo789/vba_excel_sql/blob/master/src/SQL2Excel.xlsm/clsData.cls et cliquez sur le bouton Copy raw file

Collez le contenu dans l'éditeur VBE et supprimez les premières lignes comme montré ci-dessous. Ensuite, dans le volet Properties en bas à gauche, cliquez sur le champ (Name) et tapez clsData comme nouveau nom.

Créez maintenant un nouveau module.

Allez sur https://github.com/cavo789/vba_excel_sql/blob/master/src/SQL2Excel.xlsm/test.bas et cliquez sur le bouton Copy raw file.

Collez le contenu dans l'éditeur VBE et supprimez la première ligne comme montré ci-dessous. Ensuite, dans le volet Properties en bas à gauche, cliquez sur le champ (Name) et tapez test comme nouveau nom.

Toujours dans le module test, faites attention aux toutes premières lignes :

Vous devrez adapter ces valeurs aux vôtres. Si vous avez créé l'instance SQL Server comme expliqué plus haut, utilisez ces valeurs :

Private Const cServerName = "localhost,1433"   ' <-- You'll need to mention here your server name?
Private Const cDBName = "SampleDB"       ' <-- You'll need to mention here your database name?
Private Const cSQLStatement = "SELECT * FROM dbo.Customers" ' <-- You'll need to mention here a valid SQL statement (SELECT ...)?

Toujours dans le VBA, cliquez sur le menu Tools puis sélectionnez References

Dans la liste, faites défiler jusqu'à trouver Microsoft ActiveX Data Objects 2.8 Library et, une fois trouvé, sélectionnez-le.

Jouons avec CopyToSheet​

Toujours dans l'interface VBE, sélectionnez le module test et descendez jusqu'à la sous-routine CopyToSheet.

Mettez à jour la sous-routine et ajoutez deux lignes :

Sub CopyToSheet()

Dim rng As Range?

    cData.ServerName = cServerName?
    cData.DatabaseName = cDBName?
    ' highlight-next-line
    cData.UserName = "SA"?
    ' highlight-next-line
    cData.Password = "2Secure*Password2"?

    Set rng = cData.SQL_CopyToSheet(cSQLStatement, ActiveSheet.Range("A1"))?

End Sub

Nous sommes prêts. Placez votre curseur dans la fonction CopyToSheet, n'importe où, et appuyez sur F5 pour l'exécuter.

Rien ne s'est passé ? Vous en êtes sûr ?

Passez de l'interface VBE à votre feuille Excel et tadaaa — le même résultat que celui déjà montré en haut de cet article.

La liste des fonctionnalités (approfondissement optionnel — code complet de chaque sous-routine)​

Sous-routine CopyToSheet​

Description​

Récupère un recordset depuis la base et l'écrit dans une feuille. Cette fonction fait une copie, pas un lien => il n'y a aucun lien avec la base, aucun moyen de faire un refresh.

Avantages​

Rapide

Inconvénient​

Ne conserve aucun lien avec la DB, les enregistrements sont copiés dans Excel

Exemple de code :

cData.ServerName = cServerName
    cData.DatabaseName = cDBName
    ' highlight-next-line
    cData.UserName = "SA"?
    ' highlight-next-line
    cData.Password = "2Secure*Password2"?

    ' When cData.UserName and cData.Password are not supplied
    ' the connection will be made as "trusted" i.e. with the connected
    ' user's credentials.

    Set rng = cData.SQL_CopyToSheet("SELECT * FROM dbo.Customers", ActiveSheet.Range("A1"))?

Sous-routine AddQueryTable​

Description​

Crée une query table dans une feuille : crée la connexion, la query table, lui donne un nom et récupère les données.

Avantages​

Garde la connexion active. L'utilisateur final pourra faire un Données -> Actualiser pour obtenir une mise à jour de la feuille. Si l'utilisateur n'a pas accès à la base, les enregistrements resteront bien visibles mais sans aucune possibilité de les rafraîchir

Inconvénient​

Si le paramètre bPersist vaut True, la chaîne de connexion sera en clair dans le fichier (=> évitez ceci si vous utilisez un login / mot de passe).

Paramètres​

• sSQL : instruction à utiliser (une instruction SQL valide comme SELECT ... FROM ... ou EXEC usp_xxxx)
• sQueryName : nom interne qui sera donné à la querytable
• rngTarget : destination du recordset retourné (p.ex. Sheet1!$A$1)
• bPersist : si true, la chaîne de connexion sera stockée et l'utilisateur pourra alors rafraîchir la requête

Exemple de code

cData.ServerName = cServerName
    cData.DatabaseName = cDBName
    ' highlight-next-line
    cData.UserName = "SA"?
    ' highlight-next-line
    cData.Password = "2Secure*Password2"?

    ' When cData.UserName and cData.Password are not supplied?
    ' the connection will be made as "trusted" i.e. with the connected
    ' user's credentials.

    sSQL = "SELECT * FROM dbo.Customers"

    Call cData.AddQueryTable(sSQL, "qryTest", ActiveCell, True)?

Sous-routine RunSQLAndExportNewWorkbook​

Description​

Cette fonction appelle la fonction AddQueryTable de cette classe mais crée d'abord un nouveau classeur, récupère les données et met en forme la feuille (ajoute un titre, affiche la date/heure de « Last extracted date » dans le rapport, ajoute les filtres automatiques, la mise en page et plus encore.

Le classeur obtenu sera prêt à être envoyé à quelqu'un.

Paramètres​

• sSQL : instruction à utiliser (une instruction SQL valide comme SELECT ... FROM ... ou EXEC usp_xxxx)
• sReportTitle : titre de la feuille
• bPersist : si true, la chaîne de connexion sera stockée et l'utilisateur pourra alors rafraîchir la requête

Exemple de code

cData.ServerName = cServerName
    cData.DatabaseName = cDBName
    ' highlight-next-line
    cData.UserName = "SA"?
    ' highlight-next-line
    cData.Password = "2Secure*Password2"?

    sSQL = "SELECT * FROM dbo.Customers"

    Call cData.RunSQLAndExportNewWorkbook(sSQL, "My Title", False)?

Conclusion​

Une classe VBA, un container SQL Server, et Excel passe du tableur statique à une véritable surface de requête — copiez les données une fois avec CopyToSheet, ou gardez-les rafraîchissables avec AddQueryTable. Voyez la première version, plus courte, de cet article si vous voulez juste le snippet minimal sans toute la mise en place Docker + SSMS.