Użycie Excel VBA do zapytań do bazy danych SQL Server

Mimo, że możemy tworzyć Zewnętrzne Zestawy Danych w Excelu, które pobierają informacje z bazy danych SQL Server, zdarzają się sytuacje, w których chcemy sprawdzić wartość lub wyszukać wartość z baz danych SQL Server bezpośrednio z formuły Excela.

Przyjrzymy się przykładowi, który umożliwia nam sprawdzenie wartości całkowitego przychodu klienta z formuły Excela.

Funkcja VBA do zapytania bazy danych SQL Server

Poniższa funkcja VBA może być używana z poziomu projektu Excel VBA. Należy jednak najpierw dodać bibliotekę ADODB do projektu.

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

W tym przykładzie używamy połączenia ADO do połączenia z instancją SQL Server (DBSRVSQL2014):

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

• Parametr Provider wskazuje, że zostanie nawiązane połączenie OLDEB, a parametr Data Source wskazuje na instancję SQL Server.
• Parametr Initial Catalog identyfikuje bazę danych, która ma być zapytana (AdventureWorks2014)
• Parametr Integrated Security wskazuje, że do uwierzytelnienia z serwerem SQL Server zostanie użyty Windows Authemtication.

Obiekt RecordSet (rs) jest używany do tworzenia zbioru rekordów z instrukcji SELECT:

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

Konstrukcja SELECT jest tworzona z dosłownego łańcucha i wartości zmiennej intID, która jest przekazywana do funkcji podczas jej wywoływania.

Konstrukcja If na początku sprawdza, czy zmienna intID ma wartość 0. Zmienne całkowite mają domyślnie wartość zero, jeśli nie są zainicjalizowane (innymi słowy, jeśli nie podano wartości przy wywołaniu funkcji). Jeśli żadna wartość nie zostanie przekazana do funkcji, wartość 0 jest zwracana jako wartość przychodu.

 If intID = 0 Then LookupAWCustomerRevenue = 0

Druga instrukcja If sprawdza, czy wartość nienumeryczna jest zwracana przez instrukcję SELECT. Jeżeli identyfikator klienta przekazany do funkcji jest poprawny, ale nie złożył on żadnego zamówienia, wyrażenie SUM(TotalDue) zwróci wartość NULL. Jeśli tak się stanie, funkcja zwróci wartość 0 zamiast tego.

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

Scenariusz wartości NULL można zobaczyć na poniższym zrzucie ekranu.

Umieściłem punkt przerwania na linii kodu VBA zawierającej instrukcję If i otworzyłem okno Locals, dzięki czemu mogłem zobaczyć wszystkie wartości zmiennych w tym punkcie wykonania.

Testowałem funkcję wykonując ją z okna Immediate w edytorze VBA:

 ?LookupAWCustomerRevenue(1)

Z ustawionym punktem przerwania wykonanie kodu automatycznie zatrzymuje się na zaznaczonej linii i umożliwia nam podgląd środowiska w tym punkcie wykonania.

Okno Locals na powyższym zrzucie ekranu pokazuje zmienną obiektu Recordset rs, a konkretnie wartość dla pierwszego pola z rs, „CustRev”. Widzimy, że jest ono ustawione na Null. Dzieje się tak dlatego, że klient o wartości CustomerID 1 nie złożył żadnego zamówienia i dlatego nie ma wynikowej wartości przychodu.

Następny zrzut ekranu pokazuje, że zapytanie zwraca NULL, gdy jest uruchamiane bezpośrednio na instancji SQL Server:

Zakładając, że prawidłowy identyfikator klienta jest przekazywany do funkcji i wartość non NULL jest zwracana przez instrukcję SELECT, funkcja zwróci całkowity przychód ze sprzedaży dla tego klienta jako wartość walutową.

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

Wywołanie funkcji VBA z formuły Excela

Wywołanie funkcji VBA z formuły Excela jest proste. Kiedy konstruujesz formułę Excela, wpisując ją do komórki, intellisense wyświetli pasujące funkcje VBA, jak również funkcje wbudowane. Pokazuje to poniższy zrzut ekranu:

Widać, że istnieją dwie funkcje VBA wymienione powyżej, które zaczynają się od Lo: LookupAWCustomerRevenue oraz LookupPersonName.

Następujący przykład pokazuje funkcję LookupAWCustomerRevemue używaną w komórce do obliczania całkowitego przychodu dla wartości CustomerID wyodrębnionej w kolumnie A arkusza:

Podsumowanie

W tym artykule widzieliśmy trochę kodowania VBA, niektóre techniki obsługi błędów VBA i debugowania, i widzieliśmy, jak możemy wywoływać funkcje VBA z formuły Excela. Jeśli masz jakiekolwiek pytania związane z tym artykułem, napisz do nas na adres [email protected]. Istnieje również wersja Access tego artykułu.

Możesz zauważyć, że te kursy pojawiają się w ofercie Late Availability od czasu do czasu, oferując oszczędności rzędu 30%.