Autor: Peter Haserodt  --- Aus Excel VBA - Gruppe: Tutorials

ADO und Excel (3) - Ausgabe des Recordsets

Autor: Peter Haserodt - Erstellt: --      - Letzte Revision: --Gruppenthema: 9 Folgen 1 2 3 4 5 6 7 8 9 Sie sind in Folge:3

ADO und Excel (3) - Ausgabe des Recordsets

(Dieser Artikel setzt die Kenntnis der vorangegangen Artikel zu Ado und Excel voraus)

In der Regel bekommen wir natürlich mehrere Datensätze zurück.
Diese können auch ganz viele sein.
Ich möchte Ihnen hier drei Methoden vorstellen, die Datensätze auszugeben.
Bitte stellen Sie aber ersteinmal wieder eine Testmappe her.
Diese soll ado3.xls heißen.
In dieser Mappe sollen zwei Arbeitsblätter sein, eines mit dem Namen Quelle und eines mit dem Namen Ziel.
Das Arbeitsblatt Quelle füllen wir mit ein paar Werten: Quelle

 ABC
1   
2 MonatWert
3 Januar1
4 Februar2
5 März3
6 Januar4
7 Februar5
8 März6
9 Januar7
10 Februar8
11 März9
12 Januar10
13 Februar11
14 März12
15   

Dann benötigen wir mal wieder ein allgemeines Modul.
Bevor ich Ihnen nun den Code zeige, noch ein kleiner Hinweis:

Starten Sie ihn nicht sofort
, da nichts passiert!

Option Explicit
 
Public Sub AdoAusgabeVariationen()
 ' Peter Haserodt 2007
 Dim oAdoConnection As Object, oAdoRecordset As Object
 Dim sAdoConnectString As String, sPfad As String
 Dim sQuery As String
 Dim oZielStartRange As Range
 On Error GoTo Fehler
 sPfad = ThisWorkbook.FullName
 Set oZielStartRange = ThisWorkbook.Worksheets("Ziel").Range("b2")
 Set oAdoConnection = CreateObject("ADODB.CONNECTION")
 sAdoConnectString = _
 "DRIVER={Microsoft Excel Driver (*.xls)};ReadOnly=1;DBQ=" & sPfad
 oAdoConnection.Open sAdoConnectString
 Set oAdoRecordset = CreateObject("ADODB.RECORDSET")
 sQuery = "Select [Monat],[Wert] from [Quelle$] where Monat='März'"
 With oAdoRecordset
  .Source = sQuery
  .ActiveConnection = oAdoConnection
  .Open
  ''' #################################################################
  ''' #########  Verschiedene Optionen, immer nur eine auskommentieren
  'Call AusgabePerCopyFromRecordset(oAdoRecordset, oZielStartRange)
  'MsgBox AusgabePerGetRows(oAdoRecordset, oZielStartRange)
  'Call AusgabePerLoop(oAdoRecordset, oZielStartRange)
  ''' #################################################################
 End With
 Aufraeumen:
 On Error Resume Next ' Sehr Faul
 oAdoRecordset.Close
 oAdoConnection.Close
 Set oAdoRecordset = Nothing
 Set oAdoConnection = Nothing
 Exit Sub
 Fehler:
 MsgBox "Fehler: " & Err.Description
 Resume Aufraeumen
End Sub
 
Private Sub AusgabePerCopyFromRecordset(DasRecordSet As Object, _
 StartAusgabe As Range)
 ' Peter Haserodt 2007
 StartAusgabe.CurrentRegion.Clear
 StartAusgabe.CopyFromRecordset DasRecordSet
End Sub
 
Private Function AusgabePerGetRows(DasRecordSet As Object, _
 StartAusgabe As Range) As Long
 ' Peter Haserodt 2007
 Dim vX As Variant, iRowCount As Long, iColCount As Long
 On Error GoTo Fehler
 StartAusgabe.CurrentRegion.Clear
 vX = WorksheetFunction.Transpose(DasRecordSet.getrows)
 iRowCount = UBound(vX, 1)
 On Error Resume Next
 iColCount = UBound(vX, 2)
 On Error GoTo Fehler
 If iColCount = 0 Then
  With StartAusgabe.Parent
   .Range(.Cells(StartAusgabe.Row, StartAusgabe.Column), _
   .Cells(StartAusgabe.Row, _
   StartAusgabe.Column + iRowCount - 1)).Value = vX
  End With
  AusgabePerGetRows = 1
 Else
  With StartAusgabe.Parent
   .Range(.Cells(StartAusgabe.Row, StartAusgabe.Column), _
   .Cells(StartAusgabe.Row + iRowCount - 1, _
   StartAusgabe.Column + iColCount - 1)).Value = vX
  End With
  AusgabePerGetRows = iRowCount + 1
 End If
 Exit Function
 Fehler:
 AusgabePerGetRows = 0
End Function
 
Private Sub AusgabePerLoop(DasRecordSet As Object, _
 StartAusgabe As Range)
 Dim k As Long, z As Long
 'Eine schnarchlangsame Ausgabemethode
 'für Sie zum rumspielen
 StartAusgabe.CurrentRegion.Clear
 z = -1
 With DasRecordSet
  Do While Not .EOF
   z = z + 1
   For k = 0 To .fields.Count - 1
    StartAusgabe.Offset(z, k) = .fields(k)
   Next k
   .movenext
  Loop
 End With
End Sub

Der Code - kurze Vorstellung

Außer unserer Hauptprozedur AdoAusgabeVariationen gibt es in diesem Modul noch drei weitere Prozeduren.
Ich habe Ihnen hier 3 Varianten für Ausgaben eingebaut, die Sie sorgfältig studieren sollten.
Ich gehe auf jede noch näher ein.
Zum Testen kommentieren Sie jeweils eine der Call Zeilen wieder ein (vergessen Sie aber nicht, die anderen dann wieder auszukommentieren)
Bei Excel 97 zumindest, kann es bei der ersten Variante zu einem Fehler kommen.
Probieren Sie nun ersteinmal ein wenig herum.
(Die Abfrage selbst ist wieder sehr simpel, nämlich einfach die Daten zurückzugeben, mit dem Monat März)

Zwischenbemerkung

Die Art, wie wir sie hier haben, ist CaseSensitiv, d.h. es wird nur nach März wie es geschrieben ist, gesucht.
Eine kleine Änderung des Querystrings ändert dies:
sQuery = "Select [Monat],[Wert] from [Quelle$] where lcase(Monat) = 'märz'"




Interner Tipp:
Eine Excel Schulung von einem Profi ist Gold wert!
Peter Haserodt bietet Ihnen das und noch viel mehr...
Mehr dazu unter: Schulung

Die Ausgaben im Einzelüblick

1. AusgabePerCopyFromRecordset
Hier benutzen wir eine Methode von Excel, bzw. vom Rangeobjekt.
Bitte beachten Sie, dass dies zumindest bei Excel 97 Probleme bringen kann.
Die Methode CopyFromRecordset benötigt primär eine Angabe, nämlich unser erzeugtes Recordset Objekt.
Interessant sind aber die weiteren optionalen Parameter, die Sie sich auch unbedingt anschauen sollten.
(Max Rows und Max Columns)
Diese Art der Rückgabe hat einige Vorteile:
Ich brauche nur die Startzelle anzugeben. Außerdem löst sie keinen Fehler aus, wenn die Abfrage keinen Record zurückgibt. (Sie können dies testen, in dem Sie die Abrage mal von März auf Dezember ändern)
Nachteil: Bei großen Datenmengen ist sie in meinen Tests erheblich langsamer als die GetRows.

2. AusgabePerGetRows

Hier benutzen wir eine Methode von ADO. In eine Variantvariable werden die Werte zurückgeschrieben.
Witziger Weise sind diese aber gedreht, d.h. Spalten und Zeilen sind vertauscht.
Aber in Excel können wir dies leicht lösen, in dem wir das Feld transponieren.
Im Gegensatz zur obigen Prozedur müssen wir mehr Arbeit leisten, nämlich den Ausgabebereich uns errechnen.
Außerdem löst die GetRows Methode einen Fehler aus, wenn kein Datensatz vorliegt.
(Dies habe ich hier mal simpel über eine OnError abgefangen, eine Function daraus gemacht und gebe gleich noch die Anzahl der Datensätze zurück)

Weiterhin gibt es da noch Problemchen, wenn nur ein Datensatz zurückkommt.
Dies habe ich über die kleine Trick OnError Resume Next Geschichte gelöst und entscheide dann, wie die Ausgabe erfolgen soll.

Bei meinen Tests war diese Methode bei umfangreichen Datenmengen schneller. Aber dies sollten Sie selbst ausgiebig testen.

2. AusgabePerLoop

Hier will ich nicht näher darauf eingehen, da diese sich von selbst erschließt.
Ich habe da schnarchlangsam geschrieben!
So wie sie da steht auf jeden Fall. Wenn man aber die Werte zuerst in einen Variant zurückschreibt, ist auch diese Methode sicherlich nicht ganz so langsam.



Weitere Artikel der Gruppe: Tutorials Aus Excel VBA
Nach oben