Datenimport aus Excel

Die TransferSpreadsheet-Methode weist im Wesentlichen zwei Einschränkungen auf: Sie steht nur innerhalb von Access-Anwendungen zur Verfügung – wenn Sie eine Anwendung als VB-Projekt entwerfen oder dorthin migrieren möchten, müssen Sie die Sache selbst in die Hand nehmen – und es ist begrenzt auf einzulesende Daten, die bereits ein relationales Format aufweisen, die also so vorliegen, wie sie auch in der Datenbank erscheinen sollen. Diese zweite Einschränkung betrifft auch andere Methoden, wie zum Beispiel den SQL-Zugriff auf Excel über den ISAM-Treiber oder das direkte Abfragen einer Excel-Datei über ein DAO- oder ADODB-Recordset.

Beim Verarbeiten der Importdaten spielen die Modulo- und Integerdivision eine entscheidende Rolle. Falls Sie mit diesen Operatoren nicht gut vertraut sind, finden Sie im folgenden Teil eine kleine Erläuterung. Wenn Sie auf diesem Gebiet jedoch firm sind, können Sie auch direkt zur ersten Aufgabenstellung springen.

Kleine Einführung: Ganzzahldivision

Die Modulo- (Mod) und Integer-Division (\) sind Ganzzahloperationen, die Folgendes leisten:

• a \ b ergibt die größte ganze Zahl, die multipliziert mit b kleiner oder gleich a ist.
• a Mod b ergibt den Rest dieser Division.

Einige Beispiele:

5 \ 2 = 2
5 Mod 2 = 1
14 \ 5 = 2
14 Mod 5 = 4

Das ist Division der zweiten Grundschulklasse: zwei passt zweimal in fünf, Rest eins; fünf passt zweimal in 14, Rest vier. Was Sie damit anfangen können, zeigt sich aber viel anschaulicher anhand einer kleinen Schleife:

For i = 0 To 15
 Debug.Print i \ 5; i Mod 5
Next i

Für \ ergibt sich die Zahlenreihe 0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 3, für Mod 0, 1, 2, 3, 4, 0, 1, 2, 3, 4, 0, 1, 2, 3, 4, 0. Mit der Basis 3 statt 5 ergäbe sich für \ die Reihe 0, 0, 0, 1, 1, 1, 2, 2, 2, 3, 3, 3 … und für Mod 0, 1, 2, 0, 1, 2, 0, 1, 2 …

Schauen Sie sich nun die Indexpaare eines Arrays aus Tabelle 1 an. Wenn Sie die Zellen zeilenweise durchnumerieren, lauten die Zeilenindizes (1, 1, 1, 2, 2, 2, 3, 3, 3) und die Spaltenindizes (1, 2, 3, 1, 2, 3, 1, 2, 3). Das sind genau die um eins erhöhten Ergebnisse von Zellennummer \ 3 und Zellennummer Mod 3.

Durch Verschiebung und überlagerung solcher Indexfolgen durch Multiplikation, Addition und mehrfacher Integerdivision mit verschiedenen Basen können Sie aus einem laufenden Zähler beliebige Indexpaare erzeugen, mit denen Sie die Reihenfolge zum Schreiben der Daten aus einem Array in Felder und Datensätze festlegen – Beispiele dazu folgen.

Beispiel 1: Daten aus Kreuztabellen einlesen

Viel öfter als ordentlich angeordnete Daten mit Spalten für Felder und Zeilen für Datensätze liefern Kunden ein datentechnisches Tohuwabohu, das Sie nur per Excel-Automation umsetzen können (siehe Bild 1).

Die Daten sollen die Entwicklung der Quartalsumsätze in verschiedenen Kategorien (Konten, Vertriebsbereiche) von mehreren Filialen darstellen. Die eigentlichen Kategorien (Kategorie##) sind noch einmal Oberkategorien (Kategorie#) untergeordnet, die jeweils die Summe der untergeordneten Kategorien darstellen.

Sie konnten dem Kunden folgende Zusicherungen abringen:

• In Zeile 1 stehen immer die Filialnamen. Für jede Filiale werden immer Zahlen für alle vier Quartale geliefert. Zwischen je zwei Filialnamen sind also sicher drei Leerzellen.
• In Zeile 2 stehen die Quartalsbezeichnungen. Diese sind nicht fix, sondern können ohne Vorankündigung auch 1Q, 1.Quartal, Q1, Quartal eins et cetera lauten. Die Importroutine soll das klaglos verdauen und in der DB in jedem Fall die Zahlen 1, 2, 3 und 4 fürs Quartal ausgeben.

Bekannte Zahlen für abgelaufene Quartale, die je Filiale bereits vollständig verarbeitet wurden, sind durch einen grünen Hintergrund in der Quartalsbezeichnung gekennzeichnet. Für in der Zukunft liegende beziehungsweise noch nicht verarbeitete Quartale werden dennoch Zahlen geliefert; es handelt sich um extrapolierte Schätzungen der Controlling-Abteilung. Solche Werte sind an einem roten Hintergrund erkennbar. In der Datenbank sollen die Werte durch Flags wie Gemessen oder Geschätzt gekennzeichnet werden.

Spalte A enthält die Kategorienamen. Oberkategorien werden als berechnete Werte nicht in die Datenbank übernommen. Oberkategorien sind an Fettschrift erkennbar.

Das Zielformat in der Datenbank sieht also aus wie in Bild 2 – hier dargestellt unter Excel.

Diese Aufgabe ist in keiner Weise mit TransferSpreadsheet oder anderen relationalen Methoden lösbar, zumal einige der abzubildenden Informationen in Form von Zellformatierungen geliefert werden.

Um die Tabelle im Beispiel zu verarbeiten, ist zunächst ein wenig Kenntnis der Objekthierarchie von Excel nötig.

Zuoberst steht das Application-Objekt, das die Anwendung darstellt. Darunter findet sich ein WorkBook-Objekt – das ist die zu verarbeitende Excel-Mappe. In dieser wiederum müssen Sie das Tabellenblatt, das die Daten enthält, identifizieren, was mit einem WorkSheet-Objekt geschieht. Hier sind jetzt noch der genaue Zellbereich beziehungsweise die einzelnen Zellen, welche die Daten liefern, zu identifizieren. Letzteres geschieht durch ein Range-Objekt. Die Hierarchie sieht also wie folgt aus:

Application
    Workbooks("Mappename")
        WorkSheets("Blattname")
            Range(<Zellbereich>)

Importieren mit der Cells-Eigenschaft

Nachfolgend sehen Sie zunächst die benötigten Deklarationen (den kompletten Code finden Sie im Modul ImportPivot der Beispieldatenbank, die importierte Tabelle heißt Pivot und ist in der Excel-Datei Daten.xls zu finden). Wenn Sie hier das Application-Objekt vermissen: Da beim Import keine visuelle Interaktion des Benutzers mit der einzulesenden Excel-Datei erforderlich ist, wird Ihnen hier ein Verfahren vorgestellt, das mit einem Minimum an Code alle Vorgänge komplett im Hintergrund ausführt.

Dim xlWbk As Excel.Workbook
Dim xlSht As Excel.Worksheet
Dim xlRng As Excel.Range
Dim Path As String
Dim LastCol As Long
Dim LastRow As Long
Dim c As Long, r As Long
Dim rcs As DAO.Recordset

Zunächst müssen Sie natürlich den Pfad der einzulesenden Datei bekannt geben, was je nach Anforderung relativ zur Programmdatei mit einem festen Pfad oder einem Datei-öffnen-Dialog geschehen kann. Damit können Sie direkt und ohne Umstände ein Workbook-Objekt, das die Mappe darstellt, erzeugen. Davon leiten Sie ein Worksheet-Objekt zur Darstellung des Tabellenblatts mit den Daten ab:

Path = "Pfad\Daten.xls"
Set xlWbk = CreateObject(Path)
Set xlSht = _
     xlWbk.Worksheets("Pivot")

Die folgenden Anweisungen ermitteln die letzte beschriebene Spalte und Zeile des Datenbereichs, damit Sie bezüglich der Datenmenge völlig flexibel sind. Das dort aufgebaute Recordset identifiziert die Zieltabelle und dient dazu, die Daten dort einzutragen:

LastRow = xlSht.Cells(&H10000, 1).End(xlUp).Row
LastCol = xlSht.Cells(2, &H100&).End(xlToLeft).Column

Die eigentliche Arbeit leistet die Schleife aus Listing 1. Die Variable c durchläuft die Spaltenindizes, r die Zeilenindizes. Mit der Cells-Methode des Worksheet-Objekts wird dynamisch jeweils ein Range, der nur eine Zelle enthält, identifiziert. Die Schleife beginnt mit c = 2 und r = 3, also der Zelle B3, weil dort die Nutzdaten anfangen (siehe Bild 2).

Username or Email

Passwort

Angemeldet bleiben