Kolejnym naszym krokiem w VBA będzie usprawnienie sobie pracy w procesie pozyskiwania danych zewnętrznych.

 

Każda z metod pobierania danych ma swoje zalety, ale też wady. Dla początkujących, niewątpliwie najbardziej efektywną metodą będzie MS Query. Tylko, jak się pozbyć jego wad (wysoka niestabilność, zły kreator)?

 

Jeżeli mamy problem z efektywnym użyciem jakieś metody, spróbujmy ją ulepszyć!

W pierwszym kroku uruchamiamy Rejestratora makr, następnie uruchamiamy MS Query (odcinek kursu: „MS Query”), dalej postepujemy dokładnie tak, jak robiliśmy to przy klasycznym pobieraniu danych. Gdy dane znajdą się w arkuszu, pamiętajmy o wyłączeniu kreatora makr. Efektem jest nagrany proces tworzenia kwerendy, czyli, gdy w trakcie nagrywania wskażemy skrót klawiszowy (np. [Ctrl+a]), wywołujący makro, jednym klawiszem i bardzo szybko możemy wstawić dane (zdefiniowane kwerendą) do arkusza. Sprawdzamy nagraną kwerendę [Alt+F11].

 

Rys. 1. Nagrana kwerenda

 

{modyfikacja nagranego programu} Powstał dość prosty program, nie wszystko na Rys.1. jest widoczne. Dlaczego? Bo kwerenda, powstając kieruje się PARAMETRAMI tekstowymi, tu: definicją sterownika (linia 3.) oraz definicją zapytania SQL (linia 6.). A to są dobre wiadomości.

Pełny tekst definicji sterownika: "ODBC;DSN=Pliki programu dBase;DefaultDir=E:\AFIN;DriverId=533;MaxBufferSize=2048;PageTimeout=5;"

Pełny tekst definicji zapytania SQL: "SELECT FAKTURA.NR_FAKT, FAKTURA.DATA, FAKTURA.NAZWA, FAKTURA.TERMIN, FAKTURA.POZYCJI, FAKTURA.WART_NET, FAKTURA.VAT" & Chr(13) & "" & Chr(10) & "FROM `C:\GazetaPrawna\KursExcela\Dane\Handel`\FAKTURA.DBF FAKTURA"

Czy można to modyfikować? Oczywiście.

O tyle, o ile definicja sterownika może być dla wszystkich zapytań do plików DBF taka sama (i nie szkodzi nawet, że jest tam wpisana jakaś ścieżka dostępu), tak zdanie SQL musi być określone bardzo precyzyjnie, ale niekoniecznie tak, jak nagrał nam to Rejestrator.

Zdanie SQL, tak skrócone, działa identycznie: "SELECT * FROM `C:\GazetaPrawna\KursExcela\Dane\Handel`\FAKTURA.DBF FAKTURA"

Po co skracać? Żeby łatwiej można było je sparametryzować.

Po co parametryzować? Żeby nie mieć problemu definiowania kwerendy za każdym razem. Naszym celem jest doprowadzenie do takiego stanu, że wpisując np. pierwszą literę nazwy kontrahenta, a jeszcze lepiej jakiś ogólny wzorzec nazwy (filtr), po naciśnięciu skrótu klawiszowego, w arkuszu ma nam się pojawić zestaw faktur, tylko kontrahentów na tą literę (spełniających warunek). Cel dość ambitny, spróbujemy go jednak wykonać.

 

W pierwszej kolejności spróbujmy dołożyć do naszego zapytania parametr wyboru – klauzulę SQL „WHERE”: „…ndel`\FAKTURA.DBF FAKTURA WHERE nazwa LIKE ‘A%’ ”

Następnie „nakażmy” tej procedurze (tekst zdania SQL jest jej elementem), żeby tekst zdania SQL składał się z części stałej i części przekazanej przez wartość komórki w miejscu, w którym procedura zostanie uruchomiona. Nie wdając się w szczegóły, poprawiamy końcówkę powyższego tekstu: „…ndel`\FAKTURA.DBF FAKTURA WHERE nazwa LIKE '" & ActiveCell.Value & "'")

Uwaga: Należy zwracać uwagę na spacje, cudzysłowy i apostrofy. Cudzysłów (”) zamyka tekst zdania, (‘) apostrof jest kwalifikatorem wartości tekstowej w zdaniu SQL. Na końcu przed nawiasem, występują one w następującej kolejności (tu rozdzielone średnikami) : ”;’;”

 

Pojawiają się jednak problemy:

  1. Wpisywana kwerenda przesuwa wartości arkusza w prawo, tj. wstawia kolumny w miejscu uruchomienia – zaradźmy temu, określając z jednej strony docelowe miejsce kwerendy na „B3” (w linii 4. programu) oraz kasując uprzednio wpisane dane.
  2. Poważniejszym problemem jest jednak fakt wstawiania do arkusza kwerendy, a nie wartości. Z tym radzimy sobie, uzupełniając nasz program o zestaw komend, kasujących kwerendy z bieżącego arkusza oraz kasujących wstawiane wraz z kwerendą nazwy do arkusza
  3. Pozostaje jeszcze uzupełnić nasz program o deklaracje zmiennych obiektowych (kwerendy i nazwy są obiektami arkusza)

 

Powstaje gotowy program, w którym na czerwono zaznaczono fragmenty kodu, konieczne do poprawnego działania, a wpisane ręcznie.

 

Sub Makro1()

    Dim kwerenda, name As Object              ‘ deklaracja zmiennych

 

    ActiveSheet.Range("A3:Z500").Delete             ‘ usuwanie wpisanych danych

    For Each name In ActiveSheet.Names               ‘ pętla, usuwająca

        name.Delete                                                     ‘ nazwy z arkusza

    Next

   

    With ActiveSheet.QueryTables.Add(Connection:= _

"ODBC;DSN=Pliki programu dBase;DefaultDir=E:\AFIN;DriverId=533;Ma

xBufferSize=2048;PageTimeout=5;" _

           , Destination:=Range("B3"))

           .CommandText = Array( _

           "SELECT * FROM `C:\GazetaPrawna\KursExcela\Dane\Handel`\FAKTURA.DBF

 FAKTURA WHERE nazwa LIKE '" & ActiveCell.Value & "'")

        .name = "Kwerenda z Pliki programu dBase"

        .FieldNames = True

        .RowNumbers = False

‘usunięto fragment programu, nie mający wpływu na jego działanie

        .Refresh BackgroundQuery:=False

    End With

   

    For Each kwerenda In ActiveSheet.QueryTables         ‘ pętla, usuwająca kwerenda.Delete                                                  ‘ kwerendy z arkusza

    Next

End Sub

 

Efekt

Nasz arkusz stał się przeglądarką faktur klientów. Wystarczy w kolejne komórki (np. w pierwszym wierszu, wpisać najczęściej używane filtry (tu: wzorce nazw kontrahentów) i ustawiając się na każdej z nich i naciskać skrót klawiszowy [Ctrl+a]. Po każdym naciśnięciu (uruchomieniu procedury) wartość tej komórki zostaje wpisana do procedury jako parametr wyszukiwania rekordów i w ten sposób otrzymujemy pełną listę faktur. Uważny czytelnik zapyta, czym to się różni od Autofiltra? W naszym przypadku dane nie są w arkuszu – dane są w zewnętrznej bazie danych. Może to być baza DBF, może być inny skoroszyt Excela, plik tekstowy, baza Access, Oracle, SQL Server lub każda inna, o ile posiadamy do niej sterownik ODBC (należy pytać firmowego informatyka). W arkuszu nie tworzy się kwerenda na stałe, więc wszelkie niedogodności MS Query sprytnie omijamy, w arkusz wpisywane są wyłącznie wartości, więc arkusz, o ile odwołuje się do danych na lokalizacji sieciowej, może być używany przez wiele osób na różnych komputerach.

 

Rys.2. Widok arkusza z wyfiltrowanymi danymi z zewnętrznej bazy danych

 

Różne filtry, w arkuszu w wierszu 1-szym, oznaczają kolejno: „%” – wybierz wszystkie rekordy, „A%” – kontrahenci na „A”, „%X” – kontrahenci z nazwą, zakończoną na „X”, kontrahenci z „P”, a następnie z „D” w nazwie, „___I%” – kontrahenci z 4-tą literą „I” w nazwie (jest poprzedzona trzema znakami „_” (podkreślenie), kontrahenci o nazwach 6-cio literowych (6 kolejnych znaków „_”). Oczywiście gdy sparametryzujemy w ten sposób datę, wzorzec numeru faktury, a nawet całe zapytanie SQL również będzie to działać. Działa szybko, bez żadnych powiązań międzyarkuszowych, a przede wszystkim BEZPIECZNE – ten program się nie zepsuje.

 

Opisana powyżej metoda jest doskonałym przykładem wspomagania standardowych funkcjonalności arkusza przez programowanie w VBA. Nie napisaliśmy całego programu, my go tylko zmodyfikowaliśmy pod nasze potrzeby. Tak samo można stworzyć program, odczytujący dane z Internetu – w pierwszym kroku nagrywanej kwerendy, należy po prostu wybrać „Nowa kwerenda sieci Web” zamiast „Utwórz nową kwerendę bazy danych”.

 

Test umiejętności:

Podpowiedź:

Zamiast "SELECT * FROM

`C:\GazetaPrawna\KursExcela\Dane\Handel`\FAKTURA.DBF FAKTURA

 WHERE nazwa LIKE '" & ActiveCell.Value & "'"

należy po prostu wpisać ActiveCell.Value, a w komórce, której wartość jest parametrem, całość zapytania, np.

SELECT * FROM

`C:\GazetaPrawna\KursExcela\Dane\Handel`\FAKTURA.DBF FAKTURA

 WHERE nazwa LIKE ‘A%’

lub dowolne inne zdanie SQL. Uwaga: apostrofy, używane przez ODBC na określenie źródła danych, tu ścieżki do pliku FAKTURA.DBF, to apostrofy nad tabulatorem na klawiaturze, apostrofy koło Entera mają w SQL inne znaczenie – kwalifikują tekst wyszukiwanej wartości.