Excel VBA:n käyttäminen SQL Server -tietokannan kyselyyn

Vaikka voimme luoda Excelissä ulkoisia tietokokonaisuuksia, jotka hakevat tietoja SQL Server -tietokannasta, on tilanteita, joissa haluat tarkistaa arvon tai hakea arvoa taustalla olevasta SQL Server -tietokannasta suoraan Excel-kaavasta.

Katsomme esimerkin, jonka avulla voimme hakea asiakkaan kokonaistulojen arvon Excel-kaavasta.

VBA:n avulla voit kysyä SQL Server -tietokannasta

Seuraavaa VBA-funktiota voidaan käyttää Excelin VBA-projektissa. Sinun on kuitenkin ensin lisättävä ADODB-kirjasto projektiin.

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

Tässä esimerkissä käytetään ADO-yhteyttä SQL Server -instanssiin (DBSRV\SQL2014):

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

• Palveluntarjoaja-parametri ilmaisee, että muodostetaan OLDEB-yhteys, ja tietolähdeparametri osoittaa SQL Server -instanssiin.
• Initial Catalog -parametri yksilöi tietokannan, jota kysytään (AdventureWorks2014)
• Integrated Security -parametri osoittaa, että SQL Serverin todennukseen käytetään Windows Authemticationia.

RecordSet-oliota (rs) käytetään tietuejoukon luomiseen SELECT-lauseesta:

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

SELECT-lause muodostetaan literaalisesta merkkijonosta ja muuttujan intID arvosta, joka välitetään funktioon, kun sitä kutsutaan.

Alussa oleva If-lause tarkistaa, onko intID:n arvo 0. Kokonaislukumuuttujien oletusarvo on nolla, jos niitä ei ole alustettu (toisin sanoen jos funktiota kutsuttaessa ei anneta arvoa). Jos funktiolle ei anneta arvoa, tuloarvona palautetaan arvo 0.

 If intID = 0 Then LookupAWCustomerRevenue = 0

Toisen If-lauseen avulla testataan, onko SELECT-lauseen palauttama arvo ei-numeerinen. Jos funktioon syötetty customerID on kelvollinen, mutta asiakas ei ole tehnyt tilauksia, SUM(TotalDue)-lauseke palauttaa NULL-arvon. Jos näin tapahtuu, funktio palauttaa sen sijaan arvon 0.

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

NULL-arvoskenaario näkyy seuraavassa kuvakaappauksessa.

Asetin katkaisupisteen VBA-koodin riville, joka sisälsi If-lauseen, ja avasin Paikannusikkuna-ikkunan, jotta näen kaikki muuttujien arvot kyseisessä suoritushetkessä.

Testasin funktiota suorittamalla sen VBA-editorin Immediate-ikkunasta:

 ?LookupAWCustomerRevenue(1)

Koodin suoritus pysähtyy automaattisesti merkitylle riville asetetun katkaisupisteen avulla, jolloin voimme tarkastella ympäristöä kyseisessä suoritushetkessä.

Yllä olevan kuvakaappauksen Locals Window -ikkunassa näkyvät Recordset-objektin muuttuja rs ja nimenomaan rs:stä löytyvän ensimmäisen kentän, ”CustRev”, arvo. Näemme, että sen arvoksi on asetettu Null. Tämä johtuu siitä, että asiakas, jonka CustomerID-arvo on 1, ei ole tehnyt yhtään tilausta, eikä hänellä näin ollen ole tuloksena liikevaihtoarvoa.

Seuraavasta skrenn-kuvasta nähdään, että kysely palauttaa NULL-arvon, kun se ajetaan suoraan SQL Server -instanssissa:

Edellytyksenä on, että funktioon syötetään kelvollinen CustomerID-tunnus ja SELECT-lause palauttaa arvon, joka ei ole NULL-arvo, funktio palauttaa kyseisen asiakkaan myynnin kokonaistulon valuutta-arvona.

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

VBA-funktion kutsuminen Excel-kaavasta

VBA-funktion kutsuminen Excel-kaavasta on yksinkertaista. Kun rakennat Excel-kaavan kirjoittamalla sen soluun, intellisense näyttää vastaavat VBA-funktiot sekä sisäänrakennetut funktiot. Seuraava kuvakaappaus osoittaa tämän:

Voit nähdä, että edellä on lueteltu kaksi VBA-funktiota, jotka alkavat kirjaimella Lo: LookupAWCustomerRevenue ja LookupPersonName.

Seuraavassa esimerkissä LookupAWCustomerRevemue-funktiota käytetään solussa laskemaan kokonaistulot työarkin sarakkeessa A erotetulle CustomerID-arvolle:

Yhteenveto

Tässä artikkelissa olemme nähneet hiukan VBA-koodausta, joitain VBA-virheidenkäsittely- ja virheenkorjaustekniikoita sekä nähneet, miten voimme kutsua VBA-funktioita Excel-kaavasta. Voit vapaasti lähettää meille sähköpostia osoitteeseen [email protected], jos sinulla on kysyttävää tähän artikkeliin liittyen. Tästä artikkelista on myös Access-versio.