Utilizarea Excel VBA pentru a interoga o bază de date SQL Server

Deși putem crea seturi de date externe în Excel care să extragă informații dintr-o bază de date SQL Server, există momente în care doriți să verificați o valoare sau să căutați o valoare dintr-o bază de date SQL Server subiacentă direct dintr-o formulă Excel.

Vom analiza un exemplu care ne permite să căutăm valoarea veniturilor totale ale unui client dintr-o formulă Excel.

VBA Pentru a interoga o bază de date SQL Server

Următoarea funcție VBA poate fi utilizată din cadrul unui proiect Excel VBA. Cu toate acestea, va trebui să adăugați mai întâi biblioteca ADODB în proiect.

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

În acest exemplu folosim o conexiune ADO pentru a ne conecta la o instanță SQL Server (DBSRV\SQL2014):

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

• Parametrul Provider indică faptul că va fi stabilită o conexiune OLDEB, iar parametrul Data Source indică instanța SQL Server.
• Parametrul Initial Catalog identifică baza de date care va fi interogată (AdventureWorks2014)
• Parametrul Integrated Security indică faptul că se va utiliza Authemtication Windows pentru autentificarea cu SQL Server.

Un obiect RecordSet (rs) este utilizat pentru a crea un set de înregistrări dintr-o instrucțiune SELECT:

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

Instrucțiunea SELECT este construită dintr-un șir literal și valoarea pentru variabila intID care este transmisă în funcție atunci când aceasta este apelată.

Declarația If de la început verifică dacă există o valoare intID de 0. Variabilele de tip întreg au implicit valoarea zero dacă nu sunt inițializate (cu alte cuvinte, dacă nu este furnizată nicio valoare atunci când funcția este apelată). Dacă nicio valoare nu este transmisă funcției, o valoare de 0 este returnată ca valoare a venitului.

 If intID = 0 Then LookupAWCustomerRevenue = 0

Cea de-a doua instrucțiune If testează dacă este returnată o valoare nenumerică de către instrucțiunea SELECT. Dacă un ID client transmis în funcție este valid, dar acesta nu a plasat nicio comandă, expresia SUM(TotalDue) va returna o valoare NULL. Dacă se întâmplă acest lucru, atunci funcția va returna în schimb valoarea 0.

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

Scenariul valorii NULL poate fi văzut în următoarea captură de ecran.

Am plasat un punct de întrerupere pe linia de cod VBA care conține instrucțiunea If și am deschis fereastra Locals Window astfel încât să pot vedea toate valorile variabilelor în acel punct de execuție.

Am testat funcția executând-o din Immediate Window în editorul VBA:

 ?LookupAWCustomerRevenue(1)

Cu un punct de întrerupere setat, execuția codului se oprește automat la linia marcată și ne permite să vizualizăm mediul în acel punct de execuție.

Fereastra Locals din captura de ecran de mai sus arată variabila obiectului Recordset rs, și în special valoarea pentru primul câmp din rs, „CustRev”. Putem vedea că aceasta este setată la Null. Acest lucru se datorează faptului că un client cu valoarea CustomerID 1 nu a plasat nicio comandă și, prin urmare, nu are nicio valoare de venit rezultată.

Screnn shot-ul următor arată că interogarea returnează NULL atunci când este rulată direct pe instanța SQL Server:

Să presupunem că un CustomerID valid este trecut în funcție și că o valoare non NULL este returnată de instrucțiunea SELECT, funcția va returna venitul total din vânzări pentru acel client ca o valoare în valută.

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

Chemarea funcției VBA dintr-o formulă Excel

Chemarea unei funcții VBA dintr-o formulă Excel este simplă. Atunci când construiți o formulă Excel tastând-o într-o celulă, intellisense va afișa funcțiile VBA corespunzătoare, precum și funcțiile încorporate. Următoarea captură de ecran arată acest lucru:

Puteți vedea că există două funcții VBA enumerate mai sus care încep cu Lo: LookupAWCustomerRevenue și LookupPersonName.

Exemplul următor arată funcția LookupAWAWCustomerRevemue fiind utilizată într-o celulă pentru a calcula venitul total pentru valoarea CustomerID sepcificată în coloana A a foii de calcul:

Summary

În acest articol am văzut un pic de codare VBA, câteva tehnici de tratare a erorilor VBA și de depanare și am văzut cum putem apela funcții VBA dintr-o formulă Excel. Nu ezitați să ne trimiteți un e-mail la [email protected] dacă aveți întrebări legate de acest articol. Există, de asemenea, o versiune Access a acestui articol.

S-ar putea să descoperiți că aceste cursuri apar din când în când într-o ofertă de disponibilitate târzie, oferind economii de 30%.

.