Utiliser Excel VBA pour interroger une base de données SQL Server

Bien que nous puissions créer des ensembles de données externes dans Excel qui tirent des informations d’une base de données SQL Server, il y a des moments où vous voulez vérifier une valeur ou consulter une valeur d’une base de données SQL Server sous-jacente directement à partir d’une formule Excel.

Nous allons examiner un exemple qui nous permet de consulter la valeur du revenu total d’un client à partir d’une formule Excel.

Le VBA pour interroger une base de données SQL Server

La fonction VBA suivante peut être utilisée à partir d’un projet Excel VBA. Vous devrez cependant ajouter la bibliothèque ADODB au projet au préalable.

Public Function LookupAWCustomerRevenue(intID As Long) As Currency Dim conn As ADODB.Connection Dim rs As ADODB.Recordset Dim strConnString As String If intID = 0 Then LookupAWCustomerRevenue = 0 Else strConnString = "Provider=SQLOLEDB;Data Source=W10NBMJD\SQL2014;" _ & "Initial Catalog=AdventureWorks2014;Integrated Security=SSPI;" Set conn = New ADODB.Connection conn.Open strConnString Set rs = conn.Execute("SELECT SUM(TotalDue) AS CustRev FROM Sales.SalesOrderHeader WHERE CustomerID = " & intID) If Not IsNumeric(rs.Fields("CustRev").Value) Then LookupAWCustomerRevenue = 0 Else LookupAWCustomerRevenue = rs.Fields("CustRev").Value rs.Close End If End If End Function

Dans cet exemple, nous utilisons une connexion ADO pour nous connecter à une instance de SQL Server (DBSRV\SQL2014):

 strConnString = "Provider=SQLOLEDB;Data Source=DBSRV\SQL2014;" _ & "Initial Catalog=AdventureWorks2014;Integrated Security=SSPI;"

• Le paramètre Provider indique qu’une connexion ADO sera établie et le paramètre Data Source pointe vers l’instance de SQL Server.
• Le paramètre Initial Catalog identifie la base de données à interroger (AdventureWorks2014)
• Le paramètre Integrated Security indique que l’Authemtication Windows sera utilisée pour s’authentifier auprès de SQL Server.

Un objet RecordSet (rs) est utilisé pour créer un jeu d’enregistrements à partir d’une instruction SELECT :

 Set rs = conn.Execute("SELECT SUM(TotalDue) AS CustRev FROM Sales.SalesOrderHeader WHERE CustomerID = " & intID)

L’instruction SELECT est construite à partir d’une chaîne littérale et de la valeur de la variable intID qui est passée dans la fonction lorsqu’elle est appelée.

L’instruction If au début vérifie la présence d’une valeur intID de 0. Les variables entières ont par défaut une valeur de zéro si elles ne sont pas initialisées (en d’autres termes, si aucune valeur n’est fournie lorsque la fonction est appelée). Si aucune valeur n’est passée dans la fonction, une valeur de 0 est renvoyée comme valeur de revenu.

 If intID = 0 Then LookupAWCustomerRevenue = 0

La deuxième instruction If teste si une valeur non numérique est renvoyée par l’instruction SELECT. Si un ID client passé à la fonction est valide, mais qu’il n’a passé aucune commande, l’expression SUM(TotalDue) renverra une valeur NULL. Si cela se produit, alors la fonction retournera une valeur de 0 à la place.

 If Not IsNumeric(rs.Fields("CustRev").Value) Then LookupAWCustomerRevenue = 0

Le scénario de la valeur NULL peut être vu dans la capture d’écran suivante.

J’ai placé un point d’arrêt sur la ligne de code VBA contenant l’instruction If et j’ai ouvert la fenêtre Locals afin de voir toutes les valeurs des variables à ce point d’exécution.

J’ai testé la fonction en l’exécutant à partir de la fenêtre immédiate de l’éditeur VBA :

 ?LookupAWCustomerRevenue(1)

Avec un point d’arrêt placé, l’exécution du code s’arrête automatiquement à la ligne marquée et nous permet de voir l’environnement à ce point d’exécution.

La fenêtre Locals dans la capture d’écran ci-dessus montre la variable d’objet Recordset rs, et spécifiquement la valeur du premier champ de rs, « CustRev ». Nous pouvons voir qu’il est défini à Null. Cela est dû au fait qu’un client avec la valeur CustomerID 1 n’a pas passé de commande et, par conséquent, n’a pas de valeur de revenu résultante.

La capture d’écran suivante montre que la requête renvoie NULL lorsqu’elle est exécutée directement sur l’instance SQL Server:

En supposant qu’un CustomerID valide est passé dans la fonction et qu’une valeur non NULL est renvoyée par l’instruction SELECT, la fonction renverra le revenu total des ventes pour ce client sous forme de valeur monétaire.

 LookupAWCustomerRevenue = rs.Fields("CustRev").Value

Appeler la fonction VBA depuis une formule Excel

Appeler une fonction VBA depuis une formule Excel est simple. Lorsque vous construisez une formule Excel en la tapant dans une cellule, intellisense affichera les fonctions VBA correspondantes ainsi que les fonctions intégrées. La capture d’écran suivante montre cela:

Vous pouvez voir qu’il y a deux fonctions VBA listées ci-dessus qui commencent par Lo : LookupAWCustomerRevenue et LookupPersonName.

L’exemple suivant montre la fonction LookupAWCustomerRevemue utilisée dans une cellule pour calculer le revenu total pour la valeur CustomerID sepcifiée dans la colonne A de la feuille de calcul :

Summary

Dans cet article, nous avons vu un peu de codage VBA, quelques techniques de gestion des erreurs et de débogage VBA, et nous avons vu comment nous pouvons appeler des fonctions VBA à partir d’une formule Excel. N’hésitez pas à nous envoyer un courriel à [email protected] si vous avez des questions concernant cet article. Il existe également une version Access de cet article.

Vous pourrez constater que ces cours font l’objet d’une offre de disponibilité tardive de temps à autre, offrant des économies de 30 %.