Wykorzystanie SQL-a w Excelu bez jakiejkolwiek bazy danych

Ostatnio wpadłem na dosyć oryginalny pomysł (pewnie nie ja pierwszy) wykorzystania silnika JET do wykonywania operacji na danych wprost z Excel-a. Pomysł opiera się na tym że można wskazać dowolny plik Excel jako źródło danych dla kwerendy SQL. Główkując chwilę stworzyłem procedurę tworzącą obiekt QueryTable w wybranej lokalizacji która zwraca wynik zapytania SQL. Jak wiadomo QueryTable to taki fajny mechanizm do prezentacji danych zewnętrznych w postaci tabelki. Ma wiele gadżetów ale nie o tym dziś mowa. Kod procedury i przykładowe wykorzystanie poniżej.

Plik do pobrania

Sub Raport(Target As Range, SQL As String, Optional Name)

Dim sConn As String
Dim sPath As String
Dim sName As String
Dim qt As QueryTable
Dim wks As Excel.Worksheet

If IsMissing(Name) Then
' sprawdź czy taki obiekt nie istnieje
sName = "Lista"
Else
sName = CStr(Name)
End If

On Error Resume Next
If ThisWorkbook.Names(sName).Name <> "" Then
If Err.Number = 0 Then
sName = sName & "_1"
End If
Err.Clear
End If

On Error GoTo ERR_Handler:
' ścieżka do pliku roboczego
sPath = ThisWorkbook.Path & "\" & ThisWorkbook.Name

' Connection String
sConn = "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=;User ID=Admin;Data Source=" & sPath & ";" & _
"Mode=Share Deny Write;Extended Properties=""HDR=YES;"";" & _
"Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;"

' skoroszyt roboczy
Set wks = Target.Parent

' sprawdź czy obiekt istnieje
If wks.QueryTables.Count > 0 Then
' generuje błąd jak QT nie ma, działanie celowe
Set qt = wks.QueryTables(sName)
Else
' generuj błąd braku obiektu
Err.Raise 9
End If

With qt   ' tworzymy obiekt QueryTable we wskazanej lokalizacji
.CommandType = xlCmdSql ' informacja o tym że korzystamy z polecenia SQL
.CommandText = SQL ' Komenda SQL, gdzie [Sheet1$] skoroszytem, po wstawieniu np. [lista] pobieramy dane z zakresu nazwanego
.Name = sName ' nazwa obiektu Querytable
.Refresh BackgroundQuery:=False 'pobieramy dane
End With

END_Handler:

Exit Sub

ERR_Handler:

Select Case Err.Number
Case 9
' tworzenie obiektu
Set qt = wks.QueryTables.Add(Connection:=sConn, Destination:=Target)
Resume Next

Case Else
MsgBox Err.Description
Resume END_Handler:
End Select

End Sub

' przykład wykorzystania
Sub test()
' wyswietl elementy ze skoroszytu Sheet1, znak $ konieczny do tego żeby JET wiedział że to cały skoroszyt
Raport Sheet3.Range("A1"), "select * from [Sheet1$]", "Wynik_2"
'policz oraz sumuj elementy z obszaru nazwanego lista
Raport Sheet3.Range("D1"), "select count(*) as ILE, sum(R) as [S] from [lista]", "Wynik_3"
End Sub

Arkusze z Klasą

Język programowania dostarcza nam szereg funkcjonalności. Jedną z nich są klasy użytkownika. W odróżnieniu od modułów które mogą zawierać luźno powiązany ze sobą kod, klasa stanowi hermetyczną całość ściśle ze sobą powiązaną. Wymaga to bardziej abstrakcyjnego myślenia podczas programowania, niemniej jednak nagrodą są funkcjonalności niedostępne w podejściu modułowym.
Pracę z klasami rozpoczniemy od zrozumienia jak to w ogóle działa, gdyż bez tego nie ma co się brać za pisania :)

Klasa jest swego rodzaju kontenerem na inne elementy odseparowane w ramach tego pojemnika od innych części programu, dzięki czemu taką klasę można później swobodnie wykorzystać w innym projekcie. Tu muszę zaznaczyć że bezwzględnie należy stosować zasadę separacji i nie odwoływania się do jakiegoś elementu nadrzędnego. Powodem tego mogą być nieoczekiwane efekty w momencie gdyby przyszło nam do głowy stworzyć kolejną instancję klasy w obiekcie. Dojście do tego dlaczego mamy błąd było by niewątpliwie kłopotliwe.

Klasa jako taka nie może być wywołana jak zwykła funkcja lub procedura, musi być zadeklarowana do jakiegoś obiektu. Dopiero taki obiekt udostępnia nam dostęp do metod i właściwości publicznych w ramach danej klasy, są to odpowiednio odpowiedniki procedur i zmiennych. Osobnym elementem wymagającym szczególnego omówienia są Eventy, gdyż te nie mają odpowiednika w świecie modułów.

Event to zdarzenie które możemy stworzyć i wykorzystać do własnych celów. Działanie eventu jest bardzo proste. Mamy np. jakąś procedurę w klasie, procedura się wykonuje i w pewnym momencie następuje wyzwolenie event-a czyli wywołanie instrukcji RaiseEvent. W tym momencie następuje coś nieoczekiwanego z punktu widzenia podejścia standardowego, a mianowicie program przeskakuje do podprogramu obsługi event-a znajdującego się w kodzie głównym, czyli w miejscu gdzie nastąpiło wywołanie procedury z klasy.

Może brzmi to dziwnie, ale każdy bardziej zaawansowany programista VBA doskonale zna np. zdarzenia które oprogramowuje w skoroszycie np. Private Sub Worksheet_SelectionChange(ByVal Target As Range) lub Private Sub Worksheet_Calculate(). Widać tutaj pewną prawidłowość nazwa procedury składa się z dwu cześci rozdzielonych znakiem _. Pierwsza część to nazwa obiektu w którym programujemy Event-y, zaś część po znaku _ to nazwa eventu zadeklarowanego w klasie, reszta to parametry Eventu jakie będą przekazane do programu głównego.

Tu przedstawię ciekawostkę: nie można zadeklarować klasy w taki sposób aby tworzyła się samodzielnie instancja klasy wraz z obsługą Eventów. Ponadto Eventy są obsługiwane tylko w innych obiektach będących klasami czyli userformach, arkuszach, formularzach innych klasach. Nie są obsługiwane w modułach. O czym ja piszę :) bo pewnie brzmi to trochę po chińsku.
Chodzi mi o to że klasę można deklarować na dwa sposoby:

dim a as new clTest
a.test

Coś takiego możemy zadeklarować w dowolnym miejscu, działa to tak że klasa clTest działa dopiero po pierwszym użyciu. dzięki czemu nie musimy sobie zaprzątać głowy tym że np. instancja klasy nie jest zadeklarowana. Jeżeli jednak chcielibyśmy korzystać z dobrodziejstw Eventów trzeba postąpić w sposób bardziej wyrafinowany:

private WithEvents a as clTest

public sub wywolaj
  set a = new clTest
  a.test
end sub

private Sub a_test_event ()
  msgbox ("a ku ku")
end sub

Jak widzimy w procedurze Wywołaj jawnie deklarujemy nową instancję klasy, co może być kłopotliwe przy wielokrotnym wykorzystani tej metody. Z odsieczą przyjdzie nam mała funkcja pozwalająca sprawdzić czy obiekt jest zadeklarowany:

Function IsNothing(vObject) As Boolean
    On Error Resume Next
    IsNothing = vObject Is Nothing
    If Err.Number <> 0 Then IsNothing = True
End Function

Dzięki takiej funkcji możemy sprawdzić czy obiekt już został zadeklarowany.

public sub wywolaj
  if IsNothing(a) then
    set a = new clTest
  end if
  a.test
end sub

W takiej konstrukcji obiekt będzie tworzony tylko w momencie gdy jest niezadeklarowany.

Oddzielną kwestią jest to co się dzieje podczas tworzenia nowego obiektu. Klasy jako takie posiadają mechanizm konstruktora i destruktora czyli podprogramu uruchamianego w momencie tworzenia lub niszczenia obiektu klasy. bardzo praktycznym przykładem wykorzystania konstruktora i destruktora jest połączenie się z bazą danych i stworzenie obiektu ADODB.Connection podtrzymującego połączenie przez cały czas życia klasy czyli aż do zniszczenia obiektu lub wciśnięciu guzika Stop w edytorze VBA.

Deklaracja konstruktora i desruktora:

Private Sub Class_Initialize()
 ' kod konstruktora
End Sub

Private Sub Class_Terminate()
 ' kod destruktora
End Sub

Uprawnienia w MSSQL-u do kolekcji parametrów

Dłubiąc dziś w SQL-u doszedłem co jest potrzebne do tego żeby kolekcja
parametrów w obiekcie typu ADODB.Parameters uzupełniła się sama po wpisaniu nazwy procedury. Trzeba mianowicie nadać prawo do wykonania EXECUTE userowi lub roli do obiektów
dbo.sp_ddopen oraz dbo.sp_sproc_columns.

uprawnienia dla użytkownika Guest

GRANT EXECUTE ON [dbo].[sp_ddopen] TO [guest]
GRANT EXECUTE ON [dbo].[sp_sproc_columns] TO [guest]
GO

uprawnienia dla roli Public

GRANT EXECUTE ON [dbo].[sp_ddopen] TO [public]
GRANT EXECUTE ON [dbo].[sp_sproc_columns] TO [public]
GO

Dzięki temu nie trzeba tworzyć kolekcji parametrów gdyż tworzy się sama

po podaniu nazwy procedury, dzięki czemu możemy podstawiać wartości bezpośrednio do istniejących elementów kolekcji

cmd.parameters("@par").value = "coś"

i są wszystkie parametry dostępne dla danej procedury łącznie z @return_value. Jak jest problem z uprawnieniami to tej kolekcji nie ma i trzeba sobie wszystkie parametry dodać ręcznie za pomocą konstrukcji:

cmd.Parameters.Append cmd.CreateParameter("@par", adChar, adParamInput, 50, "coś")

co jest niewygodne, lecz łatwe do osiągnięcia z poziomu kodu. Przydatne może być następujące zapytanie:

SELECT     S.name AS [Schema], PR.name, PA.name AS ParmName, T.name AS ParType, PA.max_length, PA.precision, PA.scale, PA.is_output, PA.has_default_value
FROM         sys.parameters AS PA INNER JOIN
sys.procedures AS PR ON PA.object_id = PR.object_id INNER JOIN
sys.schemas AS S ON PR.schema_id = S.schema_id INNER JOIN
sys.types AS T ON PA.system_type_id = T.system_type_id
WHERE     (PR.name = @RaportName) AND (S.name = @Schema)

Zwraca ono listę parametrów, które posiada procedura o nazwie zdefiniowanej w @RaportName i będącej w schemacie @Schema