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

ADO und Excel (4) - Ein etwas komplexeres Beispiel

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

ADO und Excel (4) - Ein etwas komplexeres Beispiel

Hier möchte ich Ihnen ein kleines Beispiel vorstellen, welches Ihnen die Macht von ADO in Excel zeigt.
Tatsächlich können wir Datenbankfunktionalitäten nutzen, um auch komplexere Auswertungen zu starten.
Als Ausgabeprozedur habe ich hier die AusgabePerCopyFromRecordset benutzt. Sie können natürlich auch andere aus dem vorangegangenen Artikel auswählen.

Zuerst brauche wir wieder einmal eine Testumgebung:
Erstellen Sie eine Arbeitsmappe mit dem Namen ado4.xls.
Diese soll drei Arbeitsblätter haben:
Ziel, V1 und V2.
Die Blätter V1 und V2 füllen wir wie folgt:

V1

 ABC
1   
2 ArtikelnummerBezeichnung
3 1Hammer
4 2Zange
5 3Schraubenzieher


V2

 ABC
1   
2 ArtikelnummerVerkauft
3 114
4 217
5 34
6 115
7 216
8 318
9 15
10 220
11 313
12 112
13 211
14 38
15 116
16 26
17 37
18 17

Ich möchte Ihnen anhand dieses Beispieles zeigen, dass wir auch Gruppenfunktionen mit Verknüpfungen lösen können.
Und nun der Code, der eigentlich nur noch intersessant im Querystring ist, alles andere kennen wir schon:

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 [V1$].[Bezeichnung], Sum([V2$].[Verkauft]) AS a" & _
 " FROM [V1$] INNER JOIN [V2$] ON [V1$].[Artikelnummer] " & _
 " = [V2$].[Artikelnummer]" & _
 " GROUP BY [V1$].[Bezeichnung]" & _
 " ORDER BY [V1$].[Bezeichnung]"
 With oAdoRecordset
  .Source = sQuery
  .ActiveConnection = oAdoConnection
  .Open
  Call AusgabePerCopyFromRecordset(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)
 StartAusgabe.CurrentRegion.Clear
 StartAusgabe.CopyFromRecordset DasRecordSet
End Sub

Auswertung

Eigentlich sollte ich dazu nichts sagen. Manchmal sollte nur die Schönheit der Aktion für sich selbst sprechen.
Aber ich kann meinen Mund nunmal nicht halten und deswegen:
Zuerst: Natürlich könnte man dies auch über eine Pivot Tabelle erreichen.
Aber wie so oft, manchmal will man dies nicht. Außerdem ist es einfach schön.

Tatsächlich habe ich den Query String einfach in Access zusammengebaut.
Danach muss man aber noch ein wenig aufpassen. Nämlich die eckigen Klammern zu setzten und vor allem bei den Tabellennamen die $ nicht vergessen!!!

Es sollte aber ein leichtes sein, sich ein Script zu schreiben, welches das für einen erledigt.
Die Abrage selbst ist wieder leicht zu verstehen. Es wird über eine Gruppenfunktion die Summen gebildet, wobei unsere Artikelnummern als Relation dient. Und aus reinem Spaß an der Freude, habe ich auch noch ein wenig sortiert.

Zugegeben, hier fängt es dann doch schon an, dass man sich ein wenig mit Datenbanktechniken auskennen muss.
Aber dies habe ich von Anfang an gesagt.

Ab hier sollten Sie ersteinmal selbst experementieren und damit rumspielen.
Im weiteren werde ich Ihnen aber noch ein paar mehr Dinge verraten.



Weitere Artikel der Gruppe: Tutorials Aus Excel VBA
Nach oben