Wykorzystanie SQL dla pobierania danych z aktywnego skoroszytu 3/3

W ostatnim wpisie dot. wykorzystania SQL i ADODB chciałbym przedstawić dwa zagadnienia- pracę z nazwanymi zakresami oraz złączenia tabel danych w SQL (prosta technika JOIN).

1. Obszary nazwane (Named Ranges) rozpoznawane są przez ADODB jako samodzielne tabele. To zaś daje nam możliwość bardziej elastycznego podejścia do obszarów, które możemy wykorzystać w zapytaniu SQL. Nasz przykładowy obszar nazwany o nazwie TabelaProdukty mógłby wyglądać następująco:

Aby wykorzystać obszar nazwany w zapytaniu SQL umieszczamy jego nazwę w kwadratowych nawiasach. Poniższe zapytanie pobierze więc tabelę w całości:

view sourceprint?

1	'Wariant 3: pobranie informacji z obszaru nazwanego

2	SQL = "SELECT * FROM [TabelaProdukty]"

2. Ostatnią z niezwykle przydatnych umiejętności jest wykorzystanie złączeń tabel. Wyobraźmy sobie sytuację, w której -dla naszych przykładowych danych- chcemy uzyskać informację o ilości sprzedaży w podziale na kategorie produktów:

view sourceprint?

1	'Wariant 4: złączenie tabel- arkuszowej i z obszaru nazwanego

2	SQL = "SELECT TP.Kategoria, SUM(A.Ilość) AS [Ilość Razem] " & _

3	"FROM [Arkusz Dane$] AS A " & _

4	"LEFT JOIN [TabelaProdukty] AS TP " & _

5	"ON A.Produkt = TP.Produkt " & _

6	"GROUP BY TP.Kategoria"

Po wywołaniu powyższej kwerendy SQL uzyskamy następującą tabelę wynikową:

Wykorzystanie SQL dla pobierania danych z aktywnego skoroszytu 2/3

Pierwsza rzecz, która wymaga uzupełnienia względem poprzedniego wpisu (o tym samym tytule lecz z numerkiem 1) to technika dodawania nagłówków do tabeli wynikowej. Aby to uzyskać potrzebna będzie pętla oraz odwołanie do kolekcji Fields, a więc kolekcji kolumn tabeli danych. Brakujący kod wraz z komentarzem wygląda następująco:

view sourceprint?

01

'...

02	Sheets(2).Range("A2").CopyFromRecordset RS

03

04	'dodajemy nagłówki

05	Dim i As Integer

06	'pętla dla wszystkich kolumn

07	For i = 0 To RS.Fields.Count - 1

08	'kolumny danych RS indeksowane są od zera, _

09	a kolumny Excela od 1

10	Sheets(2).Cells(1, i + 1).Value = RS.Fields(i).Name

11

Next i

12

13

RS.Close

14

'...

Po uzupełnieniu poprzedniego kodu nasza tabela wynikowa wyglądać będzie następująco (dla wariantu 2):

Bez wątpienia uzyskane nagłówki w pełni nam nie odpowiadają. Aby je poprawić musimy zmodyfikować nasze zapytanie SQL przez dodanie aliasów:

view sourceprint?

1	'Wariant 2: pobranie informacji o wielkości sprzedaży dla całej tabeli

2	'uzupełnione o nazwy kolumn

3	SQL = "SELECT Produkt, SUM(Ilość) AS [Ilość Razem], " & _

4	"Sum(Ilość * Cena) AS [Przychód Razem] " & _

5	"FROM [Arkusz Dane$]" & _

6	"GROUP BY Produkt"

Wykorzystanie SQL dla pobierania danych z aktywnego skoroszytu 1/3

Nie każdy programista VBA wie, że w całkiem prosty sposób można wykorzystać język SQL do pobrania danych z określonego arkusza aktywnego skoroszytu. Bardziej zaawansowani programiści znają ryzyka związane z tą techniką- tzw. memory leaks, problem związany brakiem pamięci, ale także z brakiem dostępu do części danych w sytuacji gdy są one aktualizowane równocześnie z procesem pobierania. Pomimo wspomnianych zagrożeń uważam, że wspomniana technika jest na tyle interesująca, iż postanowiłem poświęcić jej co najmniej dwa wpisy. Zaprezentuję więc prostą technikę pobierającą SQL z wykorzystaniem ADODB (Microsoft ActiveX Data Objects).

1. Chyba najprostszy sposób pracy z ADODB oparty jest o technikę wczesnego wiązania dzięki czemu uzyskujemy dostęp do podpowiedzi IntelliSense dla obiektów, właściwości i metod związanych z ADODB. W tym celu w naszym projekcie, w edytorze VBA przechodzimy do Menu >> Tools >> References... i na liście odnajdujemy bibliotekę ADODB 2.8 (idealna dla Office od 2007 do 2013).

2. Bez wątpienia potrzebujemy zestawu danych testowych, które powinny znajdować się w określonym arkuszu. Najczęściej dane te znajdują się począwszy od komórki A1. Co jednak ważne- nie jest to wymagane. Silnik pobierania będzie wyszukiwał pierwszej zajętej komórki i tę właśnie uzna za początek naszej tabeli danych.

3. A teraz sam kod... wszelkie dodatkowe informacje umieszczone zostały poniżej w postaci komentarzy. A co się stanie w wyniku wywołania poniższej przykładowej procedury? Zależnie od wybranego zapytania SQL albo uzyskamy wybrany wycinek danych albo tabelę z podsumowaniem sprzedaży.

view sourceprint?

01	Sub SQL_ADODB_AktywnySkoroszyt()

02

03	Dim strConnString As String

04	strConnString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _

05	"Data Source=" & ActiveWorkbook.FullName & ";" & _

06	"Extended Properties=Excel 12.0 Xml;"

07

08	Dim SQL As String

09	'Wariant 1: pobranie danych z października

10	SQL = "SELECT * FROM [Arkusz Dane$] WHERE Data <#2015-11-01#"

11

12	'Wariant 2: pobranie informacji o wielkości sprzedaży dla całej tabeli

13	SQL = "SELECT Produkt, SUM(Ilość), Sum(Ilość * Cena) " & _

14	"FROM [Arkusz Dane$]" & _

15	"GROUP BY Produkt"

16

17	'należy wykomentować powyżej wariant 1 lub 2

18

19	Dim RS As ADODB.Recordset

20	Set RS = New ADODB.Recordset

21

22	RS.Open SQL, strConnString

23

24	Sheets(3).Range("A2").CopyFromRecordset RS

25

26

RS.Close

27

28

End Sub