Excel ist nett, aber auch unübersichtlich. Vor allem wenn man mit Datenmengen und VBA arbeiten will. Um Daten anzureichern, in grösseren Mengen zu verarbeiten etc. sehe ich immer wieder Script die Zellenadressen zusammensetzen und dadurch unlesbar sin. Wer weiss beim lesen schon, was die A4 ist?

Nungut, ich komme von der Datenbankseite. Dort arbeite ich mit SQL um die Datan abzufragen, zusammenzusetzen etc.. Zum Glück für mich, ist das alles auch in Excel VBA möglich.

ADODB hiesst das Losungswort.
Ich erkläre hier nicht alle Details. Das WWW gibt gut auskunft. Nein, ich zeige wie ich es anwende

Als erstes habe ich mir ein Property geschrieben, das eine ADODB-Connection auf das aktuelle Workbook zurückgibt. Da ich mich später nicht darum kümmern will, ob ich die Connection schon geöffnet habe, ist es nach dem Tutorial Funktion mit Cache als Property umgesetzt.

'/**
' * Handle dhe adodb-connection to the current workbook
' * @param  Boolean	FLag, ob die Connectionneu aufgebaut werden soll
' * @return ADODB.Conection
' */
Private Property Get connection(ByVal iReconnect As boolean = False) As Object
    Static pConn As Object
    If pConn Is Nothing Or iReconnect Then
        Set pConn = CreateObject("ADODB.Connection")
        pConn.Provider = "Microsoft.Jet.OLEDB.4.0"
        pConn.ConnectionString = "Data Source='" & ThisWorkbook.FullName & "'; " & "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"
    End If
    If Not (pConn.State And adStateOpen) = adStateOpen Then
         pConn.Open
    End If
    Set connection = pConn
End Property

Der Nächste Schritt ist das Öffnen eines ADODB.Recordsets aus dem aktuellen Worksheet. Dazu brauchen wir natürlich die oben erwähnte Connection

'/**
' * Open a adodb recordset from the current workbook
' * @param  String      SQL-String
' * @param  Boolean	FLag, ob die Connectionneu aufgebaut werden soll
' * @return ADODB.Recordset
' */
Public Function openRs(ByVal iSql As String, ByVal iReconnect As boolean = False) As Object
    Dim rst As Object: Set rst = CreateObject("ADODB.Recordset")
    Dim cmd As Object: Set cmd = CreateObject("ADODB.Command")
 
    Set cmd.ActiveConnection = connection(iReconnect)
    cmd.CommandType = adCmdText
    cmd.CommandText = iSql
 
    rst.CursorLocation = adUseClient
    rst.CursorType = adOpenDynamic
    rst.LockType = adLockOptimistic
 
 
    'open the connection
    rst.Open cmd
 
    'disconnect the recordset
    Set rst.ActiveConnection = Nothing
 
    'cleanup
    If cmd.State = adStateOpen Then
        Set cmd = Nothing
    End If
    Set openRs = rst
End Function

Doch wie greift man mittels SQL jetzt auf die Daten zu? Es gibt mehrere Varianten um die Daten jetzt als Source in einem SQL anzusprechen

Häufig habe ich von Exports aus anderen Systemen gleich ein Worksheet, das genau einer Tabelle entspricht. Die erste Zeile hat die Titel, die folgenden Zeilen sind die Daten. Ich kann mit dem SQL gleich auf diese Daten zugriefen. Die Titel sind dann auch im SQL die Namen der Spalten.

Als Tabellennamen wird einfach der Sheet-Name gefolgt von einem $ verwendet.

-- Auflistung der Vornamen aus dem Sheet 'adress_sheet'
SELECT DISTINCT
    [firstname]
FROM
    [adress_sheet$]

Ich arbeite gerne mit benannten Ranges. so lassens ich sehr einfach Datenbereiche aus einem Sheet auslesen. Dazu einfach in einem Excelsheet einen Range selektionieren und bennen (Dazugibts diverse anleitungen im Web. zB. How to Define a Named Range in Excel). Am besten wählt man ein Bereich mit einer Titelzeile.

Für kleinere Mapping-Tabellen, Stammdaten etc. verwende ich jeweils nur ein Sheet und arbeite dann mit Benannten Ranges.

Da der Benannte Range für das ganze Workbook gültigkeit hat, muss auch kein Sheet angegeben werdn

Im SQL kann dann einfach gleich dieser Name als Quelle verwendet werden.

-- Zugriff auf den Range 'cities', der mitten in einem Sheet ist
SELECT 
    [plz],
    [city]
FROM
    [cities]

Man kann auch auf einen beliebigen Bereich auf einem Sheet zugreifen. Ich rate jedoch davon ab. Adressen Hardcodiert - Never.

sheetName$adress

SELECT *
FROM [my_mappings$A3:G17]

• Ganzes Sheet: [sheetName$]
• Benannter Range: [rangeName]
• Beliebiger Range: [sheetName$adress]

Je nachdem, ob Feldernamen gefunden wurden oder nicht, kann Direkt auf den Feldnamen oder auf die Feldnummer zugegriffen werden.

• Feld Name: [feldName]
• Feld-Nr: F#