QueryTables i zewnętrzne Recordsety

Ciekawą właściwością obiektu QueryTables (powstaje gdy pobieramy dane zewnętrzne) jest możliwość wpompowania recordsetu ADO. Dzięki temu w łatwy i przyjemny sposób jesteśmy wstanie przygotować sobie uniwersalną maszynę generującą jakiś raport a następnie umieszczającą w przygotowanym szablonie. poniżej przedstawiam przykładowy kod (kod ze skoroszytu, nie można go umieścić bezpośrednio w module) realizujący tego typu operację. Omówienia wymaga tylko linia

Me.QueryTables(1)

wskazuje ona na pierwszą definicję obiektu QueryTables w skoroszycie w którym wykonywany jest kod. na upartego kod możemy umieścić gdzie indziej i wywołać po zmianie linii

Arkusz1.QueryTables(1)

Deklaracja

Private WithEvents

sprawia że obiekty w taki sposób zadeklarowane udostępniają nam obsługę eventów - przydatne, na końu przedstawiłem prosty przykład wykorzystania

I jeszcze jedna ważna uwaga: po wpompowaniu danych do takiego obiektu tracimy definicje w nim zawarte czyli nie będziemy mogli go odświeżyć za pomocą polecenia odśwież (obecny pod prawoklikiem)

Option Explicit

Private WithEvents aConn As ADODB.Connection
Private WithEvents aRs As ADODB.Recordset

Sub wpopmpuj()

Dim sConn As String
Dim sql As String

On Error GoTo ERR_Handler:

' definicja połączenia
sConn = "Provider=SQLOLEDB.1;" & _
"Data Source=PRZEMEK-PC\SQLEXPRESS;" & _
"Integrated Security=SSPI;" & _
"Persist Security Info=True;" & _
"Initial Catalog=Aplikacja;" & _
"Use Procedure for Prepare=1;" & _
"Auto Translate=True;" & _
"Packet Size=4096;" & _
"Workstation ID=PRZEMEK-PC;" & _
"Use Encryption for Data=False;" & _
"Tag with column collation when possible=False"

' zapytanie SQL
sql = "select * from dbo.Employees"

' stworzenie obiektu
Set aConn = New ADODB.Connection

With aConn
.ConnectionString = sConn
.Open ' otwarcie połączenia
' pobranie danych do Recordset-u
Set aRs = .Execute(sql)
End With

' wypełnienie obiektu w Excelu
With Me.QueryTables(1)
Set .Recordset = aRs ' wpompowanie
.Refresh ' odswierzenie
End With

END_Handler:

' zamknięcie połązenia
aConn.Close
Set aConn = Nothing

Exit Sub

ERR_Handler:

' tu można przechwycić błędy

Resume END_Handler:

End Sub

' Akcja po wykonaniu execute
Private Sub aConn_ExecuteComplete(ByVal RecordsAffected As Long, _
ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, _
ByVal pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, _
ByVal pConnection As ADODB.Connection)

If Not IsNothing(pError) Then
Debug.Print pError.Description, pError.Source
End If

End Sub

' Akcja przed wykonaniem Execute
Private Sub aConn_WillExecute(Source As String, CursorType As ADODB.CursorTypeEnum, _
LockType As ADODB.LockTypeEnum, Options As Long, adStatus As ADODB.EventStatusEnum, _
ByVal pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, _
ByVal pConnection As ADODB.Connection)

Debug.Print Source
End Sub

' funkcja sprawdzająca istnienie obiektu
Private Function IsNothing(x)
IsNothing = TypeName(x) = "Nothing"
End Function