Brug af Excel VBA til at forespørge en SQL Server-database

Selv om vi kan oprette eksterne datasæt i Excel, der trækker oplysninger fra en SQL Server-database, er der tidspunkter, hvor du ønsker at kontrollere en værdi eller slå en værdi op fra en underliggende SQL Server-database direkte fra en Excel-formel.

Vi vil se på et eksempel, der gør det muligt for os at slå op på en kundes samlede omsætningsværdi fra en Excel-formel.

VBA For at forespørge på en SQL Server-database

Følgende VBA-funktion kan bruges fra et Excel VBA-projekt. Du skal dog først tilføje ADODB-biblioteket til projektet.

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

I dette eksempel bruger vi en ADO-forbindelse til at oprette forbindelse til en SQL Server-instans (DBSRV\SQL2014):

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

• Parameteren Provider angiver, at der vil blive oprettet en OLDEB-forbindelse, og parameteren Data Source peger på SQL Server-instansen.
• Parameteren Initial Catalog identificerer den database, der skal forespørges (AdventureWorks2014)
• Parameteren Integrated Security (Integreret sikkerhed) angiver, at Windows Authemtication (Windows Authemtication) vil blive brugt til at autentificere med SQL Server.

Et RecordSet-objekt (rs) bruges til at oprette et recordsæt fra en SELECT-anvisning:

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

SELECT-anvisningen konstrueres ud fra en bogstavelig streng og værdien for variablen intID, der overføres til funktionen, når den kaldes.

I If-erklæringen i starten kontrolleres det, om værdien intID er 0. Heltalsvariabler har som standard værdien 0, hvis de ikke er initialiseret (med andre ord, hvis der ikke er angivet nogen værdi, når funktionen kaldes). Hvis der ikke gives nogen værdi til funktionen, returneres værdien 0 som indkomstværdi.

 If intID = 0 Then LookupAWCustomerRevenue = 0

Den anden If-anvisning tester, om SELECT-anvisningen returnerer en ikke-numerisk værdi. Hvis et kundeID, der er overført til funktionen, er gyldigt, men de har ikke afgivet nogen ordrer, returnerer SUM(TotalDue)-udtrykket en NULL-værdi. Hvis dette sker, returnerer funktionen i stedet værdien 0.

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

Scenariet med NULL-værdien kan ses på følgende skærmbillede.

Jeg placerede et breakpoint på den linje af VBA-kode, der indeholder If-erklæringen, og åbnede vinduet Locals, så jeg kunne se alle variabelværdierne på det pågældende tidspunkt af udførelsen.

Jeg testede funktionen ved at udføre den fra Immediate Window i VBA-editoren:

 ?LookupAWCustomerRevenue(1)

Med et indstillet breakpoint stopper kodeudførelsen automatisk ved den markerede linje og giver os mulighed for at se miljøet på det pågældende udførelsestidspunkt.

Lokalvinduet i ovenstående skærmbillede viser Recordset-objektets variabel rs, og specifikt værdien for det første felt fra rs, “CustRev”. Vi kan se, at den er indstillet til Null. Det skyldes, at en kunde med CustomerID-værdien 1 ikke har afgivet nogen ordrer og derfor ikke har nogen resulterende omsætningsværdi.

Det følgende skærmbillede viser, at forespørgslen returnerer NULL, når den køres direkte på SQL Server-instansen:

Antaget, at et gyldigt CustomerID er overført til funktionen, og at en ikke-NULL-værdi returneres af SELECT-anvisningen, returnerer funktionen den samlede salgsindtægt for den pågældende kunde som en valutaværdi.

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

Kald VBA-funktionen fra en Excel-formel

Det er enkelt at kalde en VBA-funktion fra en Excel-formel. Når du konstruerer en Excel-formel ved at skrive den ind i en celle, vil intellisense vise matchende VBA-funktioner samt indbyggede funktioner. Følgende skærmbillede viser dette:

Du kan se, at der er to VBA-funktioner opført ovenfor, der starter med Lo: LookupAWCustomerRevenue og LookupPersonName.

Det følgende eksempel viser, at funktionen LookupAWCustomerRevemue bruges i en celle til at beregne den samlede indtægt for værdien CustomerID, der er udskilt i kolonne A i regnearket:

Summary

I denne artikel har vi set lidt VBA-kodning, nogle VBA-fejlbehandlings- og fejlfindingsteknikker, og vi har set, hvordan vi kan kalde VBA-funktioner fra en Excel-formel. Du er velkommen til at sende os en e-mail på [email protected], hvis du har spørgsmål i forbindelse med denne artikel. Der findes også en Access-version af denne artikel.

Du kan opleve, at disse kurser fra tid til anden kommer på et tilbud om sen tilgængelighed, der giver besparelser på 30 %.