Az Excel VBA használata SQL Server-adatbázis lekérdezéséhez

Bár Excelben létrehozhatunk olyan külső adatkészleteket, amelyek az SQL Server-adatbázisból nyernek információt, előfordul, hogy közvetlenül egy Excel-képletből szeretnénk ellenőrizni egy értéket vagy keresni egy értéket a mögöttes SQL Server-adatbázisból.

Megnézünk egy példát, amely lehetővé teszi számunkra, hogy egy Excel képletből lekérdezzük egy ügyfél teljes bevételének értékét.

A VBA egy SQL Server adatbázis lekérdezéséhez

A következő VBA függvényt egy Excel VBA projektből használhatjuk. Ehhez azonban először hozzá kell adni az ADODB könyvtárat a projekthez.

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

Ebben a példában ADO kapcsolatot használunk egy SQL Server példányhoz (DBSRV\SQL2014) való csatlakozáshoz:

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

• A Provider paraméter azt jelzi, hogy OLDEB kapcsolat jön létre, az Adatforrás paraméter pedig az SQL Server példányra mutat.
• A Kezdeti katalógus paraméter azonosítja a lekérdezendő adatbázist (AdventureWorks2014)
• Az Integrált biztonság paraméter azt jelzi, hogy az SQL Serverrel való hitelesítéshez Windows Authemtication lesz használva.

A RecordSet objektum (rs) egy rekordhalmaz létrehozására szolgál egy SELECT utasításból:

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

A SELECT utasítás egy szó szerinti karakterláncból és az intID változó értékéből épül fel, amelyet a függvény hívásakor átadunk a függvénynek.

Az elején lévő If utasítás ellenőrzi, hogy az intID értéke 0-e. Az egészértékű változók alapértelmezett értéke nulla, ha nincsenek inicializálva (más szóval ha a függvény hívásakor nem adunk meg értéket). Ha a függvénynek nem adunk át értéket, akkor a függvény 0 értéket ad vissza bevételi értékként.

 If intID = 0 Then LookupAWCustomerRevenue = 0

A második If utasítás azt vizsgálja, hogy a SELECT utasítás nem numerikus értéket ad-e vissza. Ha a függvénynek átadott ügyfélID érvényes, de nem adott le rendelést, a SUM(TotalDue) kifejezés NULL értéket ad vissza. Ha ez megtörténik, akkor a függvény 0 értéket fog visszaadni.

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

A NULL érték forgatókönyv a következő képernyőképen látható.

Töréspontot helyeztem el a VBA-kódnak az If utasítást tartalmazó során, és megnyitottam a Helyek ablakot, hogy láthassam az összes változó értékét a végrehajtás adott pontján.

Teszteltem a függvényt a VBA-szerkesztő Immediate Window-jából történő végrehajtásával:

 ?LookupAWCustomerRevenue(1)

A töréspont beállításával a kód végrehajtása automatikusan megáll a megjelölt sorban, és lehetővé teszi számunkra, hogy megnézzük a környezetet a végrehajtás adott pontján.

A fenti képernyőképen látható Locals Window a Recordset objektum rs változóját mutatja, és különösen az rs első mezőjének, a “CustRev”-nek az értékét. Láthatjuk, hogy null értékre van állítva. Ez azért van így, mert az 1-es CustomerID-értékkel rendelkező ügyfél nem adott le rendelést, és ezért nincs ebből eredő árbevételi értéke.

A következő képernyőképen látható, hogy a lekérdezés NULL értéket ad vissza, ha közvetlenül az SQL Server példányon futtatjuk:

Feltéve, hogy érvényes CustomerID-t adunk át a függvénybe, és a SELECT utasítás nem NULL értéket ad vissza, a függvény az adott ügyfél teljes árbevételét adja vissza valutaértékként.

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

VBA-funkció meghívása Excel-képletből

A VBA-funkció meghívása Excel-képletből egyszerű. Amikor egy Excel-képletet egy cellába beírva felépít egy Excel-képletet, az intellisense megjeleníti a megfelelő VBA-függvényeket, valamint a beépített függvényeket. A következő képernyőkép ezt mutatja:

Láthatja, hogy a fenti listában két olyan VBA függvény is szerepel, amely Lo betűvel kezdődik: LookupAWCustomerRevenue és LookupPersonName.

A következő példában a LookupAWCustomerRevemue függvényt egy cellában használjuk a teljes bevétel kiszámítására a munkalap A oszlopában elkülönített CustomerID értékhez:

Summary

A cikkben láttunk egy kis VBA kódolást, néhány VBA hibakezelési és hibakeresési technikát, és láttuk, hogyan hívhatunk VBA függvényeket egy Excel képletből. Ha bármilyen kérdése van ezzel a cikkel kapcsolatban, nyugodtan írjon nekünk a [email protected] címre. Ennek a cikknek van egy Access változata is.

Ezek a tanfolyamok időről időre későn elérhető ajánlatot jelentenek, ami 30%-os megtakarítást jelent.

Találkozhat ezekkel a tanfolyamokkal.