Utilizzando Excel VBA per interrogare un database SQL Server

Anche se possiamo creare set di dati esterni in Excel che estraggono informazioni da un database SQL Server, ci sono momenti in cui si vuole controllare un valore o ricercare un valore da un database SQL Server sottostante direttamente da una formula Excel.

Guarderemo un esempio che ci permette di cercare il valore delle entrate totali di un cliente da una formula Excel.

Il VBA per interrogare un database SQL Server

La seguente funzione VBA può essere usata da un progetto Excel VBA. Tuttavia, dovrai prima aggiungere la libreria ADODB al progetto.

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 questo esempio usiamo una connessione ADO per connetterci a un’istanza di SQL Server (DBSRV\SQL2014):

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

• Il parametro Provider indica che verrà stabilita una connessione OLDEB e il parametro Data Source punta all’istanza di SQL Server.
• Il parametro Initial Catalog identifica il database da interrogare (AdventureWorks2014)
• Il parametro Integrated Security indica che verrà utilizzata la Windows Authemtication per autenticarsi con SQL Server.

Un oggetto RecordSet (rs) è usato per creare un record set da un’istruzione SELECT:

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

L’istruzione SELECT è costruita da una stringa letterale e dal valore della variabile intID che viene passata nella funzione quando viene chiamata.

L’istruzione If all’inizio controlla se intID ha un valore di 0. Le variabili intere hanno un valore predefinito di zero se non sono inizializzate (in altre parole se non viene fornito alcun valore quando la funzione viene chiamata). Se nessun valore viene passato alla funzione, un valore di 0 viene restituito come valore delle entrate.

 If intID = 0 Then LookupAWCustomerRevenue = 0

La seconda istruzione If verifica se un valore non numerico viene restituito dall’istruzione SELECT. Se un ID cliente passato alla funzione è valido, ma non ha effettuato alcun ordine, l’espressione SUM(TotalDue) restituirà un valore NULL. Se questo accade, la funzione restituirà invece il valore 0.

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

Lo scenario del valore NULL può essere visto nel seguente screenshot.

Ho messo un punto di interruzione sulla linea di codice VBA che contiene l’istruzione If e ho aperto la finestra Locals in modo da poter vedere tutti i valori delle variabili in quel punto dell’esecuzione.

Ho testato la funzione eseguendola dalla finestra immediata nell’editor VBA:

 ?LookupAWCustomerRevenue(1)

Con un breakpoint impostato l’esecuzione del codice si ferma automaticamente sulla linea marcata e ci permette di visualizzare l’ambiente in quel punto di esecuzione.

La finestra Locals nello screenshot sopra mostra la variabile dell’oggetto Recordset rs, e in particolare il valore del primo campo di rs, “CustRev”. Possiamo vedere che è impostato su Null. Questo perché un cliente con CustomerID 1 non ha effettuato alcun ordine e, pertanto, non ha alcun valore di ricavo risultante.

Il seguente screenshot mostra che la query restituisce NULL se eseguita direttamente sull’istanza di SQL Server:

Assumendo che un CustomerID valido sia passato nella funzione e che un valore non NULL sia restituito dall’istruzione SELECT, la funzione restituirà il ricavo totale delle vendite per quel cliente come valore di valuta.

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

Chiamare la funzione VBA da una formula Excel

Chiamare una funzione VBA da una formula Excel è semplice. Quando si costruisce una formula di Excel digitandola in una cella, l’intellisense mostrerà le funzioni VBA corrispondenti e le funzioni incorporate. Il seguente screenshot mostra questo:

Si può vedere che ci sono due funzioni VBA elencate sopra che iniziano con Lo: LookupAWCustomerRevenue e LookupPersonName.

L’esempio seguente mostra la funzione LookupAWCustomerRevemue usata in una cella per calcolare il totale delle entrate per il valore CustomerID sepcificato nella colonna A del foglio di lavoro:

Summary

In questo articolo abbiamo visto un po’ di codifica VBA, alcune tecniche VBA di gestione degli errori e debug, e abbiamo visto come possiamo chiamare funzioni VBA da una formula Excel. Sentitevi liberi di mandarci un’email a [email protected] se avete delle domande relative a questo articolo. C’è anche una versione Access di questo articolo.

Potresti scoprire che questi corsi vengono offerti di tanto in tanto in offerta Late Availability, offrendo risparmi del 30%.