Verbund (join) von Tabellen

 

Excel ist leider nicht mit einer Funktion für den Verbund (join) zweier Tabellen ausgestattet. Die folgende Prozedur joinTable vollzieht einen solchen Verbund bedient sich hierzu der Standardsprache SQL.

 

Die Benutzung von SQL innerhalb von Excel-VBA ist unproblematisch. Man muss jedoch vorher eine Referenz zur Bibliothek ADO 2.5 herstellen. Hierzu wählt man die Option Verweise auf der Registerkarte Extras der VBA-Entwicklungsumgebung (VBE) und aktiviert dort die Bibliothek durch Markieren des Kästchens Microsoft ActiveX Data Objects 2.5 Library.

 

Die folgende Prozedur joinTables setzt voraus, dass die zu verknüpfenden Tabellen sich jeweils auf einem eigenen Arbeitsblatt befinden, das sonst keine Daten mehr enthält. In der ersten Zeile der Tabelle stehen jeweils die Spaltenüberschriften.

 

Der Verbund mit joinTables ist insofern beschränkt, als nur ein Verknüpfungsmerkmal benutzt werden kann und dieses Merkmal in den beiden Tabellen denselben Namen haben muss. Selbstverständlich müssen die beiden Tabellen auch den Anforderungen für Datenbanktabellen genügen. Dies ist z.B. bei den beiden folgenden Tabellen der Fall. Wir wollen annehmen, dass sich die erste Tabelle auf dem Arbeitsblatt sales befindet, die zweite auf dem Arbeitsblatt sp:

 

 

Offensichtlich haben die beiden Tabellen ein gemeinsames Merkmal, nämlich product. Dies ist das Verknüpfungsmerkmal (join column).

 

 

Der Code der Prozedur joinTables

 

Die Prozedur befindet sich in einem Modul DBJoin. Sie hat vier Parameter. Die ersten beiden sind die Namen der Arbeitsblätter, auf denen sich die Tabellen befinden. Der dritte Parameter ist der Name des Verknüpfungsmerkmals, der vierte der Name des Arbeitsblatts, auf dem das Ergebnis ausgegeben werden soll. Dieses Arbeitsblatt muss zum Zeitpunkt des Aufrufs noch nicht vorhanden sein. Falls nötig, wird es neu angelegt.

 

'joins the tables on worksheets t1Name and t2Name using

' the common attribute attrName.

 

Public Sub joinTables(ByVal t1Name As String, _

                                ByVal t2Name As String, _

                                ByVal attrName As String, _

                                wsName As String)

 

    Dim rs  As ADODB.Recordset                           'receives query result

    Dim conStr As String                                         'connection string

    Dim queryStr As String                                       'the sql query

    Dim outws As Worksheet                                  'worksheet for output

    Dim t1Range As Range, t2Range As Range        'ranges of the 2 tables

    Dim i As Integer

 

    On Error GoTo errorhandler1

   

    'create worksheet for output, if necessary, clear cells

    If Not Hlp.wrkshExists(wsName) Then Worksheets.Add.Name = wsName

    Set outws = Worksheets(wsName)

    outws.Cells.Clear

           

    'write header to output worksheet

    Set t1Range = Range(t1Name & "!" & "A1").CurrentRegion

    Set t2Range = Range(t2Name & "!" & "A1").CurrentRegion

    For i = 1 To t1Range.Columns.Count

        outws.Cells(1, i) = t1Range.Cells(1, i)

    Next i

    For i = 1 To t2Range.Columns.Count

        outws.Cells(1, i + t1Range.Columns.Count) = t2Range.Cells(1, i)

    Next i

   

    'establish connection, execute query and write to outws

    conStr = "Provider=Microsoft.ACE.OLEDB.12.0;" & _

                    "Data Source=" & ThisWorkbook.FullName & ";" & _

                    "Extended Properties=Excel 12.0;"

    queryStr = _

    "select t1.*, t2.* " & _

    "from [" & t1Name & "$] as t1, [" & t2Name & "$] as t2 " & _

    "where t1." & attrName & " = t2." & attrName & "; "

    Set rs = New ADODB.Recordset

    rs.Open queryStr, conStr

    outws.Range("A2").CopyFromRecordset rs

   

    'clean up

    rs.Close

    Set rs = Nothing

    Exit Sub

   

errorhandler1:

    MsgBox "Error: operation failed"

End Sub

 

 

 

Beispielaufruf der Prozedur joinTables

 

Die Benutzung der Prozedur ist recht einfach. Mit dem folgenden Aufruf kann man z.B. die beiden oben abgebildeten Tabellen sales und sp bezüglich des Merkmals product verknüpfen und das Ergebnis in der Tabelle Test ablegen lassen:

 

Public Sub joinTest()

     DBjoin.joinTables "sales", "sp", "product", "Test"

End Sub

 

 

Das folgende Bild zeigt einen Ausschnitt aus der Ergebnistabelle auf dem Arbeitsblatt "Test"

 

 

 

 

 

Aufruf mit Formular

 

Alternativ kann man auch ein kleines Formular anlegen und von dort aus die Prozedur joinTables aufrufen (Bild unten)

 

 

 

Der Code der Ereignisprodezur ist denkbar einfach (hier ohne Validierung):

 

Private Sub StartJoinBtn_Click()

    DBjoin.joinTables Me.FirstWsTBx.Text, Me.SecondWsTBx.Text, _

                              Me.JoinAttrTBx.Text, Me.ResultWsTBx.Text

    Worksheets(Me.ResultWsTBx.Text).Activate

End Sub