Verwenden von Excel VBA zum Abfragen einer SQL Server-Datenbank

Obwohl wir in Excel externe Datensätze erstellen können, die Informationen aus einer SQL Server-Datenbank abrufen, gibt es Fälle, in denen Sie einen Wert überprüfen oder einen Wert aus einer zugrunde liegenden SQL Server-Datenbank direkt aus einer Excel-Formel nachschlagen möchten.

Wir werden uns ein Beispiel ansehen, mit dem wir den Gesamtumsatz eines Kunden aus einer Excel-Formel abfragen können.

Die VBA-Funktion zum Abfragen einer SQL Server-Datenbank

Die folgende VBA-Funktion kann in einem Excel-VBA-Projekt verwendet werden. Sie müssen jedoch zuerst die ADODB-Bibliothek zum Projekt hinzufügen.

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 diesem Beispiel verwenden wir eine ADO-Verbindung, um eine Verbindung zu einer SQL Server-Instanz (DBSRV\SQL2014) herzustellen:

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

• Der Provider-Parameter gibt an, dass eine OLDEB-Verbindung hergestellt wird, und der Datenquellen-Parameter zeigt auf die SQL Server-Instanz.
• Der Parameter „Initial Catalog“ identifiziert die abzufragende Datenbank (AdventureWorks2014)
• Der Parameter „Integrated Security“ gibt an, dass die Windows-Authentifizierung zur Authentifizierung bei SQL Server verwendet wird.

Ein RecordSet-Objekt (rs) wird verwendet, um einen Datensatz aus einer SELECT-Anweisung zu erstellen:

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

Die SELECT-Anweisung wird aus einem String-Literal und dem Wert für die Variable intID konstruiert, die beim Aufruf an die Funktion übergeben wird.

Die If-Anweisung am Anfang prüft, ob intID den Wert 0 hat. Ganzzahlige Variablen haben standardmäßig den Wert Null, wenn sie nicht initialisiert sind (d.h. wenn beim Aufruf der Funktion kein Wert übergeben wird). Wenn kein Wert an die Funktion übergeben wird, wird der Wert 0 als Umsatzwert zurückgegeben.

 If intID = 0 Then LookupAWCustomerRevenue = 0

Die zweite If-Anweisung prüft, ob die SELECT-Anweisung einen nicht numerischen Wert zurückgibt. Wenn eine an die Funktion übergebene Kunden-ID gültig ist, aber keine Bestellungen aufgegeben wurden, gibt der Ausdruck SUM(TotalDue) einen NULL-Wert zurück. In diesem Fall gibt die Funktion stattdessen den Wert 0 zurück.

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

Das NULL-Wert-Szenario ist im folgenden Screenshot zu sehen.

Ich habe einen Haltepunkt in der Zeile des VBA-Codes gesetzt, die die If-Anweisung enthält, und das Fenster „Locals“ geöffnet, damit ich alle Variablenwerte an diesem Punkt der Ausführung sehen kann.

Ich testete die Funktion, indem ich sie vom Sofort-Fenster im VBA-Editor aus ausführte:

 ?LookupAWCustomerRevenue(1)

Mit einem gesetzten Haltepunkt hält die Codeausführung automatisch an der markierten Zeile an und ermöglicht es uns, die Umgebung an diesem Punkt der Ausführung zu sehen.

Das Fenster „Locals“ im obigen Screenshot zeigt die Recordset-Objektvariable rs und insbesondere den Wert für das erste Feld von rs, „CustRev“. Wie wir sehen können, ist dieser Wert auf Null gesetzt. Dies liegt daran, dass ein Kunde mit dem CustomerID-Wert 1 keine Bestellungen aufgegeben hat und daher keinen resultierenden Umsatzwert hat.

Der folgende Screenshot zeigt, dass die Abfrage NULL zurückgibt, wenn sie direkt auf der SQL Server-Instanz ausgeführt wird:

Angenommen, dass eine gültige CustomerID an die Funktion übergeben wird und ein Nicht-NULL-Wert von der SELECT-Anweisung zurückgegeben wird, gibt die Funktion den Gesamtumsatz für diesen Kunden als Währungswert zurück.

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

Aufruf der VBA-Funktion aus einer Excel-Formel

Der Aufruf einer VBA-Funktion aus einer Excel-Formel ist einfach. Wenn Sie eine Excel-Formel erstellen, indem Sie sie in eine Zelle eingeben, zeigt Intellisense sowohl passende VBA-Funktionen als auch eingebaute Funktionen an. Der folgende Screenshot zeigt dies:

Sie können sehen, dass oben zwei VBA-Funktionen aufgeführt sind, die mit Lo beginnen: LookupAWCustomerRevenue und LookupPersonName.

Das folgende Beispiel zeigt, wie die Funktion LookupAWCustomerRevemue in einer Zelle verwendet wird, um den Gesamtumsatz für den Wert CustomerID in Spalte A des Arbeitsblatts zu berechnen:

Zusammenfassung

In diesem Artikel haben wir ein wenig VBA-Codierung, einige VBA-Fehlerbehandlung und Debugging-Techniken gesehen, und wir haben gesehen, wie wir VBA-Funktionen aus einer Excel-Formel aufrufen können. Wenn Sie Fragen zu diesem Artikel haben, schicken Sie uns bitte eine E-Mail an [email protected]. Es gibt auch eine Access-Version dieses Artikels.

Es kann sein, dass diese Kurse von Zeit zu Zeit mit einem Preisnachlass von 30% angeboten werden.