 | Unser Angebot für Sie! Lesen Sie diesen Beitrag und 500 andere sofort im Onlinearchiv, und erhalten Sie alle zwei Monate brandheißes Access-Know-how auf 72 gedruckten Seiten! Plus attraktive Präsente, zum Beispiel das bald erscheinende Buch 'Access 2010 - Das Grundlagenbuch für Entwickler'! |
| | | | | |
Von Access nach Excel und zurück
Manfred Hoffbauer, Düsseldorf
Access verfügt über mehrere komfortable Methoden für den Datenaustausch mit Excel-Arbeitsblättern. Wer diese Methoden häufig nutzt, kann seine Arbeit mit dem Assistenten aus diesem Beitrag weiter optimieren: Er speichert die Einstellungen für den Datenaustausch mit Excel in einer Tabelle.
Die Menübefehle
Für den gelegentlichen Datenaustausch mit Excel sind die Menübefehle von Access am besten geeignet.
Sie sind ohne zusätzliche Installationsarbeiten sofort verfügbar.
Tabelle speichern unter
Das Exportieren einer Access-Tabelle in ein Excel-Arbeitsblatt erfolgt über den Befehl Datei/Speichern unter/Exportieren.
Zur Anwendung des Befehls gehen Sie wie folgt vor:
Öffnen Sie das Datenblatt der Tabelle, die Sie exportieren wollen.
Wählen Sie den Befehl Datei ( Speichern unter/Exportieren aus der Menüzeile.
Markieren Sie in dem sich öffnenden Dialog mit der Titelzeile Speicher unter ... die Option In eine externe Datei oder Datenbank.
In dem sich öffnenden Dialog Speichern Tabelle ‚Name’ in ... legen Sie das Laufwerk, den Ordner, den Dateinamen und den Dateityp der Zieldatei fest.
Mit einem Klick auf den Button Exportieren starten Sie den Vorgang. (
Mit dem Kombinationsfeld Dateityp bestimmen Sie das Format der Datei, in welche die Tabelle exportiert wird
(s. Abb. 1). Access zeigt in der Liste mit der Beschriftung Dateityp alle Dateitypen an, in die es exportieren kann.
Abb. 1: Mit der Liste Dateityp legen Sie das Format für die Exportdatei fest.
Für einen Export in ein Excel-Arbeitsblatt sollten Sie einen der mit Microsoft Excel gekennzeichneten Dateitypen auswählen.
Abb. 2: Die exportierte Datei sollte sich mit Excel öffnen lassen.
Mit der Auswahl eines Dateityps passt Access die Erweiterung der Zieldatei an. Wenn Sie beispielsweise Microsoft Excel als Dateityp auswählen, dann ändert sich die Dateierweiterung auf .xls.
Ein Klick auf die Schaltfläche Exportieren startet den Vorgang. Access generiert die Arbeitsblattdatei und schließt den Dialog. Zur Kontrolle können Sie die generierte Datei mit Excel öffnen (s. Abb. 2).
Externe Daten importieren
Der Befehl Datei ( Externe Daten ( Importieren ist das Gegenstück zu Speichern unter.
Mit diesem Befehl importieren Sie die Daten aus einem Excel-Arbeitsblatt in eine Access-Tabelle. Zur Anwendung des Befehls gehen Sie wie folgt vor:
Wählen Sie den Befehl Datei ( Externe Daten ( Importieren aus der Menüzeile.
In dem sich öffnenden Dialog Importieren sollten Sie als Erstes den Dateityp (zum Beispiel Microsoft Excel (*.xls)) der zu importierenden Datei auswählen.
Nach der Auswahl des Dateityps zeigt der Dialog nur noch Ordner und die Dateien des gewählten Typs an. Im Beispiel werden also nur noch Ordner und Excel-Arbeitsblätter angezeigt.
Markieren Sie die Datei, die Sie importieren wollen.
Ein Klick auf die Schaltfläche Importieren öffnet den Dialog Import-Assistent für Kalkulationstabellen, mit dem Sie weitere Einstellungen vornehmen können.
Wählen Sie auf jeder der folgenden Dialogseiten die gewünschten Optionen und klicken Sie jeweils auf die Schaltfläche Weiter.
Mit einem Klick auf die Schaltfläche Fertig stellen starten Sie den Importvorgang entweder von der letzten Dialogseite des Assistenten oder von einer vorherigen. Falls Sie vor der letzten Dialogseite auf Fertig stellen klicken, setzt Access für die restlichen Optionen Standardwerte ein. (
Abb. 3: Der Import-Assistent für Kalkulationstabellen bietet fast die gleichen Optionen wie die TransferSpreadsheet-Methode.
Ein genauer Blick auf die verschiedenen Optionen des Import-Assistenten für Kalkulationstabellen lohnt sich auf jeden Fall. Der Grund: Die meisten Optionen finden Sie später in der TransferSpreadsheet-Methode wieder.
Tabellenblätter und benannte
Bereiche
Der Import-Assistent für Kalkulationstabellen zeigt auf der ersten Dialogseite eine Auswahlliste der Tabellenblätter der Excel-Arbeitsblattdatei an. Die Tabellenblätter sind die Register des Arbeitsblatts.
Über eine zweite Option können Sie eine Liste der benannten Bereiche des Arbeitsblatts abfragen. Diese Option ist nur dann sinnvoll, wenn Sie die zu importierenden Daten vorher in Excel als benannten Bereich definiert haben.
Erste Zeile enthält
Spaltenüberschriften
Auf der zweiten Dialogseite stellen Sie mit dem gleichnamigen Kontrollkästchen ein, ob die erste Zeile der zu importierenden Excel-Datei die Feldnamen enthält. Falls Sie das Kontrollkästchen ankreuzen, verwendet Access die Bezeichnungen in der ersten Zeile als Feldnamen für die Importtabelle.
Optionen für die Importtabelle
Auf den nächsten drei Dialogseiten können Sie mehrere Optionen für die Importtabelle festlegen. Der Import kann in eine neue oder eine bestehende Tabelle erfolgen.
Außerdem können Sie für jede Spalte aus der Excel-Tabelle ein Datenfeld in der Access-Tabelle zuweisen, Sie können Datentypen festlegen, Feldnamen ändern und einiges mehr. Diese Optionen stehen in der TransferSpreadsheet-Methode nicht zur Verfügung und werden deshalb an dieser Stelle nicht weiter erläutert.
Assistent im Eigenbau
Die Menübefehle für den Datenaustausch sind sehr komfortabel, erfordern aber auch die Einstellung zahlreicher Optionen. Mit dem Assistenten aus diesem Beitrag können Sie wiederkehrende Aufgaben weiter optimieren. Er bietet Ihnen die Möglichkeit, die verschiedenen Optionen in einer Tabelle zu speichern. Damit können Sie mehrere Import- und Exportvorgänge einmal festlegen und jederzeit wieder abrufen.
HinweisDie für Access 97 geeignete Version des Assistenten finden Sie in der Datenbank TransferSpreadsheet97.mdb. Die Version für Access 2000 und XP ist in der Datenbank TransferSpreadsheet2000.mdb enthalten. (
Das genaue Äquivalent zu den Menübefehlen von Access ist die TransferSpreadsheet-Methode. Sie können diese Methode als TransferArbeitsblatt-Methode in Makros oder als TransferSpreadsheet-Methode in VBA verwenden. Die Programmierung des Assistenten erfolgt mit VBA.
Die Parameter der
TransferSpreadsheet-Methode
Die Syntax für den Aufruf der TransferSpreadsheet-Methode lautet wie folgt:
DoCmd.TransferSpreadsheet [Transfertyp]
[, Dateiformat], Tabellenname,
Dateiname[, Besitzt Feldnamen][, Bereich]
Die Beispieldatenbank zu diesem Beitrag enthält ein Formular, mit dem Sie alle Parameter der TransferSpreadsheet-Methode einstellen und in einer Tabelle speichern können.
AAbb. 4: Das Formular des Datentransfer-Assistenten
Damit können Sie die Einstellungen für mehrere Im- und Exportvorgänge komfortabel verwalten und jederzeit wieder abrufen. Das Formular hat den Namen frmTransfers, die Tabelle heißt tblTransfers.
Transfertyp
Für den optionalen Parameter Transfertyp können Sie die folgenden drei Parameter angeben:
Parameter |
Wert |
acImport |
0 |
acExport |
1 |
acLink |
2 |
Tab. 1: Die zulässigen Werte für Transfertyp
Damit diese Werte nicht nur als Konstanten in VBA, sondern auch für den Assistenten verfügbar sind, wurde die Tabelle tblTransfertypen angelegt. Sie speichert die Werte aus obiger Tabelle in den Feldern TransfertypID und Transfertyp.
Abb. 5: Das Datenblatt der Tabelle tblTransfertypen
Beim Aufruf der TransferSpreadsheet-Methode steuert der Transfertyp die durchzuführende Aktion. Sie können mit der gleichen Methode Daten importieren oder exportieren.
Im Formular frmTransfers können Sie den Transfertyp über ein Kombinationsfeld auswählen. Das Formular speichert die TransfertypID des ausgewählten Eintrags im gleichnamigen Feld der Tabelle tblTransfers. Das Feld TransfertypID ist ein Nachschlagefeld, das mit der Tabelle tblTransfertypen verknüpft ist.
Dateiformat
Mit dem Parameter Dateiformat legen Sie das Format der Datei fest, aus der Sie Daten importieren oder in die Sie Daten exportieren wollen. Auch diese Parameter sind als Konstanten in VBA definiert. Die Tabelle tblDateiformate enthält die mit Access 97 und Excel verwendbaren Dateiformate.
Der Datentransfer-Assistent speichert das ausgewählte Dateiformat im Feld DateiformatID der Tabelle tblTransfers. Es handelt sich um ein Nachschlagefeld, das mit der DateiformatID aus tblDateiformate verknüpft ist. Wenn Sie einen Listeneintrag aus dem Kombinationsfeld auswählen, dann speichert Access automatisch die entsprechende ID in der Tabelle.
Abb. 6: Diese Dateiformate stehen ab Access 97 zur Verfügung.
HinweisDas Dateiformat acSpreadsheetTypeExcel9 ist erst ab Access 2000 verfügbar. Die Konstante hat wie acSpreadsheetTypeExcel97 den Wert 8. Der Grund besteht darin, dass die Dateiformate von Excel 97 und Excel 2000, zumindest soweit es den Datenaustausch mit Access angeht, kompatibel sind. Die Konstante acSpreadsheetTypeExcel97 heißt bei Access 2000 acSpreadsheetTypeExcel8. (
Tabellenname
Mit dem Parameter Tabellenname geben Sie den Namen einer Access-Tabelle als Zeichenkette an. Beim Importieren ist dies der Name der Tabelle, in die die Daten des Arbeitsblatts importiert werden. Beim Exportieren ist es der Name der Tabelle, deren Daten Sie exportieren wollen.
Access fügt beim Importieren die Daten automatisch an eine bestehende Tabelle an. Dies kann nur dann funktionieren, wenn die Datenfelder und -typen der Tabelle zu den Importdaten passen. Sie können auch den Namen einer neuen Tabelle angeben - Access legt diese Tabelle dann automatisch neu an. Die neue Tabelle erhält die Datenfelder und -typen, die nach einer Analyse der Exceldatei am besten zu passen scheinen.
Private Sub TabellenLesen()
Dim tdf As TableDef
Dim sSource As String
For Each tdf In CurrentDb.TableDefs
If ((tdf.Attributes And dbSystemObject) Or _
(tdf.Attributes And dbHiddenObject)) = 0 _
Then
sSource = sSource & tdf.Name & ";"
End If
Next
If Len(sSource) > 0 Then
sSource = Left(sSource, Len(sSource) - 1)
End If
Me.Tabellenname.RowSource = sSource
End Sub
Quellcode 1
Falls Sie in eine bestehende Tabelle importieren oder aus einer bestehenden Tabelle exportieren wollen, dann können Sie den Tabellennamen einfach aus dem Kombinationsfeld Tabellenname auswählen.
Die Eigenschaft Herkunftstyp des Kombinationsfeldes hat den Wert Wertliste. Das Füllen der Wertliste erfolgt über den Aufruf der Prozedur TabellenLesen in der Beim Öffnen-Ereigniseigenschaft des Formulars. Die Prozedur können Sie Quellcode 1 entnehmen.
Die Schleife durchläuft alle Tabellen der aktuellen Datenbank. Die Namen der Tabellen werden durch Semikola getrennt in einer Zeichenkette aneinandergefügt. Die Systemtabellen der Datenbank und eventuell vorhandene versteckte Tabellen werden dabei übersprungen.
Die Trennung durch Semikola bewirkt, dass die entstehende Zeichenkette direkt der Eigenschaft Datensatzherkunft des Kombinationsfeldes zugewiesen und dort angezeigt werden kann.
Fast automatische Erkennung von
Datentypen
Access analysiert beim Import nicht alle Zeilen des Arbeitsblatts. Befinden sich in einem Arbeitsblatt mit 100 Zeilen in einer Spalte nur ganze Zahlen, dann legt Access für diese Spalte ein Feld mit dem Datentyp Zahl - Double an.
Wenn Sie in die Zeilen 50 und 75 eine Zeichenkette wie beispielsweise "x" eingeben, bleibt es bei dem Double-Feld. Nur bei den Datensätzen, die aus den Zeilen 50 und 75 importiert werden sollen, bleibt das Feld leer. Ursache: Das "x" lässt sich nicht in einen Double-Wert umwandeln.
Wenn Sie aber in die dritte Zeile ein "x" eingeben, dann legt Access für diese Spalte ein Feld mit dem Datentyp Text an. Es liegt also nahe, dass Access nur die Datentypen der ersten Zeilen aus dem Excel-Arbeitsblatt analysiert.
|
