Dynamiczne połączenie z bazą danych

Dynamiczne połączenie z bazą danych za pomocą VBA

Posted on Paweł WróblewskiPosted in Raport

Tworząc dashboard czy też cykliczny raport, zazwyczaj zasila się go w dane korzystając z bezpośredniego połączenia z bazą danych. Połączenie takie można dodatkowo wzbogacić o dynamicznie wybierany zakres kryteriów korzystając z makra napisanego w języku VBA. Wspomniane makro odwołuje się do danych wybranych w komórce i na ich podstawie zmienia klauzulę WHERE w zapytaniu T-SQL. Prześledźmy poniższe kroki potrzebne do skonfigurowania takiego makra.

 

1. Konfiguracja połączenia w Excelu

Aby wykonać wszystkie kroki przedstawione w przykładzie należy zainstalować instancję SQL Server np. 2012 wraz z bazą danych AdventureWorks2012. Jeżeli jednak posiadamy dostęp do innej bazy danych, również możemy odtworzyć ćwiczenie pamiętając o modyfikacji zapytania SQL. Na początku otwórzmy nowy skoroszyt programu Excel i zapiszmy go jako skoroszyt z obsługą makr. Następnie kliknijmy na kartę Dane --> Pobieranie danych zewnętrznych --> Z innych źródeł --- > Z programu SQL Server. W ten sposób  przejdziemy do Kreatora połączeń danych, w którym skonfigurujemy nasze połączenie. Połączenie to będziemy konfigurować w różny sposób w zależności od tego jak zainstalowaliśmy naszą instancję. W moim przypadku w polu Nazwa serwera wpisuję nazwę swojego komputera łamaną na nazwę instancji SQL Servera. Pole poświadczeń logowania zostawiam domyślnie ustawione na Uwierzytelnienie systemu Windows.

 

Połączenie z SQL Serverem

 

Klikamy Dalej i przechodzimy do kolejnego okna Kreatora połączeń, w którym możemy wybrać dowolny widok czy tabelę.

 

Wybór tabeli w kreatorze połączeń

 

Klikamy ponownie Dalej i przechodzimy do ostatniego okna, którego użyjemy do zmiany nazwy połączenia na łatwiejszą np. na połączenie.

 

Zmiana nazwy połączenia

 

Klikamy Zakończ i ustawiamy aby nasze połączenie danych zwracało dane jako tabela w arkuszu w komórce A5.

 

Importowanie danych

 

W okienku Importowanie danych przed kliknięciem OK zmienimy jeszcze właściwości połączenia. W tym celu klikamy  na przycisk Właściwości... i w karcie Definicja zmieniamy Typ polecenia na SQL a w oknie tekst polecenia wpisujemy poniższe zapytanie SQL. Zapytanie to zwróci nam ilość sztuk zapasu danej kategorii i podkategorii asortymentu. 

 

select
pc.Name
,ps.Name
,SUM(PI.Quantity)
from Production.Product as PP
inner join Production.ProductSubcategory as PS on PS.ProductSubcategoryID=PP.ProductSubcategoryID
inner join Production.ProductCategory as PC on PS.ProductCategoryID=PC.ProductCategoryID
inner join Production.ProductInventory as PI on PI.ProductID=PP.ProductID
where pc.Name like 'Bikes'
group by pc.Name,ps.Name

 

Właściwości połączenia

 

 

Po kliknięciu OK  i akceptacji pojawiającego się powiadomienia, w komórce A5 pojawi się nam tabelka z określonym zapasem danego typu roweru.

 

2. Marko w VBA

Nim przejdziemy do  napisania marka stwórzmy listę rozwijaną w komórce A1, w której będą znajdowały się nasze kryteria wyboru do klauzuli WHERE. Stworzona lista powinna zawierać następujące kategorie sprzętów: Bikes, Components, Clothing i Accessories.  Używając kombinacji klawiszy Alt+F11, otwieramy okno Visual Basic i wklejamy poniższy kod. Makro to będzie śledziło czy w komórce A1 dokonała się zmiana, jeżeli tak to pobierze dane używając naszego połączenia i odświeży dane w tabeli.

 

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" Then

ActiveWorkbook.Connections("połączenie").OLEDBConnection.CommandText = _
"select pc.Name,ps.Name,SUM(PI.Quantity)from Production.Product as PP inner join Production.ProductSubcategory as PS on PS.ProductSubcategoryID=PP.ProductSubcategoryID inner join Production.ProductCategory as PC on PS.ProductCategoryID=PC.ProductCategoryID inner join Production.ProductInventory as PI on PI.ProductID=PP.ProductID where pc.Name = '" & Range("A1").Value & "' group by pc.Name,ps.Name"

ActiveWorkbook.Connections("połączenie").Refresh

End If

End Sub

 

Pamiętajmy żeby całe zapytanie SQL znajdowało się w jednej linijce. O tym jak przedstawić kod SQL w poszczególnych linijkach opowiem w kolejnych postach. Zapisujemy nasze makro i zamykamy edytor Visual Basic. Od tej pory każda zmiana w komórce będzie skutkowała pobraniem nowych danych i odświeżeniem tabeli.

 

Dynamiczne połączenie z bazą danych

 

 

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *