Använda Excel VBA för att fråga en SQL Server-databas

Och även om vi kan skapa externa datamängder i Excel som hämtar information från en SQL Server-databas finns det tillfällen då du vill kontrollera ett värde eller söka upp ett värde från en underliggande SQL Server-databas direkt från en Excel-formel.

Vi kommer att titta på ett exempel som gör det möjligt för oss att söka upp en kunds totala intäktsvärde från en Excel-formel.

VBA För att fråga en SQL Server-databas

Följande VBA-funktion kan användas från ett Excel VBA-projekt. Du måste dock först lägga till ADODB-biblioteket i 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 det här exemplet använder vi en ADO-anslutning för att ansluta till en SQL Server-instans (DBSRV\SQL2014):

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

• Parametern Provider anger att en OLDEB-anslutning kommer att upprättas och parametern Data Source pekar på SQL Server Instance.
• Parametern Initial Catalog identifierar den databas som ska frågas (AdventureWorks2014)
• Parametern Integrated Security anger att Windows Authemtication kommer att användas för att autentisera med SQL Server.

Ett RecordSet-objekt (rs) används för att skapa en uppsättning poster från ett SELECT-meddelande:

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

SELECT-meddelandet konstrueras från en bokstavlig sträng och värdet för variabeln intID som skickas till funktionen när den anropas.

I If-anvisningen i början kontrollerar om intID har värdet 0. Heltalsvariabler har som standard värdet noll om de inte är initialiserade (med andra ord om inget värde anges när funktionen anropas). Om inget värde lämnas in till funktionen returneras värdet 0 som intäktsvärde.

 If intID = 0 Then LookupAWCustomerRevenue = 0

Det andra If-meddelandet testar om ett icke-numeriskt värde returneras av SELECT-meddelandet. Om ett kund-ID som skickas in till funktionen är giltigt, men de har inte lagt några beställningar, kommer uttrycket SUM(TotalDue) att returnera ett NULL-värde. Om detta händer kommer funktionen att returnera värdet 0 i stället.

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

Scenariot med NULL-värdet kan ses i följande skärmdump.

Jag placerade en brytpunkt på raden VBA-kod som innehåller If-anvisningen och öppnade fönstret Locals så att jag kunde se alla variablernas värden vid den tidpunkten för utförandet.

Jag testade funktionen genom att köra den från Immediate Window i VBA-editorn:

 ?LookupAWCustomerRevenue(1)

Med en brytpunkt inställd stannar kodutförandet automatiskt vid den markerade linjen och gör det möjligt för oss att se miljön vid den tidpunkten för utförandet.

Lokalfönstret i skärmdumpen ovan visar Recordset-objektets variabel rs, och specifikt värdet för det första fältet från rs, ”CustRev”. Vi kan se att det är inställt på Null. Detta beror på att en kund med CustomerID-värdet 1 inte har lagt några beställningar och därför inte har något resulterande intäktsvärde.

Följande skärmdump visar att frågan returnerar NULL när den körs direkt på SQL Server-instansen:

Att anta att ett giltigt CustomerID skickas in i funktionen och att ett icke NULL-värde returneras av SELECT-angivelsen, kommer funktionen att returnera den totala försäljningsintäkten för den kunden som ett valutavärde.

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

Kalla VBA-funktionen från en Excel-formel

Att kalla en VBA-funktion från en Excel-formel är enkelt. När du konstruerar en Excel-formel genom att skriva in den i en cell kommer intellisense att visa matchande VBA-funktioner samt inbyggda funktioner. Följande skärmdump visar detta:

Du kan se att det finns två VBA-funktioner listade ovan som börjar med Lo: LookupAWCustomerRevenue och LookupPersonName.

Följande exempel visar hur funktionen LookupAWCustomerRevemue används i en cell för att beräkna den totala intäkten för det kund-ID-värde som anges i kolumn A i kalkylbladet:

Sammanfattning

I den här artikeln har vi sett lite VBA-kodning, några tekniker för VBA-felhantering och felsökning, och vi har sett hur vi kan anropa VBA-funktioner från en Excel-formel. Du är välkommen att maila oss på [email protected] om du har några frågor som rör den här artikeln. Det finns även en Access-version av denna artikel.

Du kanske upptäcker att dessa kurser ibland erbjuds i ett sent erbjudande, vilket innebär en besparing på 30 %.