Excel VBA gebruiken om een SQL Server Database te ondervragen

Hoewel wij Externe Gegevens Sets in Excel kunnen maken die informatie uit een SQL Server Database halen, zijn er momenten dat u een waarde wilt controleren of een waarde uit een onderliggende SQL Server database wilt opzoeken, direct vanuit een Excel formule.

We zullen een voorbeeld bekijken waarmee we de totale omzet van een klant kunnen opzoeken vanuit een Excel formule.

De VBA Om een SQL Server Database te bevragen

De volgende VBA Functie kan worden gebruikt vanuit een Excel VBA Project. U moet echter eerst de ADODB bibliotheek aan het project toevoegen.

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

In dit voorbeeld gebruiken we een ADO verbinding om verbinding te maken met een SQL Server instantie (DBSRV\SQL2014):

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

• De Provider parameter geeft aan dat er een OLDEB verbinding tot stand wordt gebracht en de Data Source parameter wijst naar de SQL Server Instance.
• De Initial Catalog parameter wijst de database aan die moet worden geraadpleegd (AdventureWorks2014)
• De Integrated Security parameter geeft aan dat Windows Authemtication zal worden gebruikt om te authenticeren met SQL Server.

Een RecordSet object (rs) wordt gebruikt om een record set te maken van een SELECT statement:

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

Het SELECT statement wordt geconstrueerd uit een letterlijke string en de waarde voor de variabele intID die wordt doorgegeven aan de functie wanneer deze wordt aangeroepen.

De If-instructie aan het begin controleert of intID de waarde 0 heeft. Gehele variabelen hebben standaard de waarde nul als ze niet geïnitialiseerd zijn (met andere woorden, als er geen waarde wordt opgegeven wanneer de functie wordt aangeroepen). Indien geen waarde aan de functie wordt doorgegeven, wordt de waarde 0 als waarde voor de inkomsten geretourneerd.

 If intID = 0 Then LookupAWCustomerRevenue = 0

De tweede If-instructie controleert of de SELECT-instructie een niet-numerieke waarde heeft geretourneerd. Indien een klantID die aan de functie wordt doorgegeven geldig is, maar zij hebben geen orders geplaatst, zal de SUM(TotalDue) uitdrukking een NULL waarde teruggeven. In dat geval retourneert de functie een waarde van 0.

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

Het scenario met de NULL-waarde is te zien op de volgende schermafbeelding.

Ik plaatste een breekpunt op de regel VBA-code die de If-instructie bevat en opende het venster Locals, zodat ik alle variabele waarden op dat moment van uitvoering kon zien.

Ik testte de functie door deze uit te voeren vanuit het venster Onmiddellijk in de VBA-editor:

 ?LookupAWCustomerRevenue(1)

Met een breekpunt ingesteld stopt de uitvoering van de code automatisch op de gemarkeerde regel en stelt ons in staat de omgeving op dat punt van uitvoering te bekijken.

Het venster Locals in de bovenstaande schermafbeelding toont de Recordset-objectvariabele rs, en in het bijzonder de waarde voor het eerste veld van rs, “CustRev”. We kunnen zien dat het is ingesteld op Null. Dit komt omdat een klant met CustomerID-waarde 1 geen bestellingen heeft geplaatst en daarom geen resulterende omzetwaarde heeft.

De volgende schermafbeelding laat zien dat de query NULL retourneert wanneer deze rechtstreeks op de SQL Server-instance wordt uitgevoerd:

Aannemende dat een geldige CustomerID aan de functie wordt doorgegeven en dat een niet NULL-waarde wordt geretourneerd door de SELECT-instructie, retourneert de functie de totale verkoopopbrengst voor die klant als een valutawaarde.

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

De VBA-functie vanuit een Excel-formule oproepen

Een VBA-functie vanuit een Excel-formule oproepen is eenvoudig. Wanneer u een Excel-formule construeert door deze in een cel te typen, geeft de intellisense de bijbehorende VBA-functies weer, alsmede de ingebouwde functies. De volgende schermafbeelding laat dit zien:

U kunt zien dat er twee VBA functies zijn die beginnen met Lo: LookupAWCustomerRevenue en LookupPersonName.

Het volgende voorbeeld laat zien hoe de LookupAWCustomerRevemue functie wordt gebruikt in een cel om de totale opbrengst te berekenen voor de CustomerID waarde die is gescheiden in kolom A van het werkblad:

Summary

In dit artikel hebben we een beetje VBA codering gezien, wat VBA Foutafhandeling en Debugging technieken, en we hebben gezien hoe we VBA functies kunnen aanroepen vanuit een Excel formule. Voel je vrij om ons te e-mailen op [email protected] als je vragen hebt met betrekking tot dit artikel. Er is ook een Access versie van dit artikel.

U kunt merken dat deze cursussen van tijd tot tijd laat beschikbaar zijn, waardoor u 30% kunt besparen.