<\/p>\n
Autor: Manfred Hoffbauer, Düsseldorf<\/p>\n
<\/b><\/p>\n
Eine häufige Anforderung an die Datenbank Microsoft Access besteht darin, die gespeicherten Daten auch unter Excel zur Verfügung zu stellen. Excel eignet sich hervorragend für Auswertungen und Diagramme, die ad hoc am Bildschirm erstellt werden. Hier kann auch der Anwender ohne Programmierfahrung mit entsprechender Fachkenntnis schnell zu überzeugenden Ergebnissen kommen.<\/b><\/p>\n
Export ganz schnell<\/b><\/p>\n
Aber wie gelangen die Daten aus der Datenbank in ein Arbeitsblatt Die einfachste Variante bietet das Office-Symbol von Microsoft Access. Um die Daten einer Tabelle oder Abfrage an Excel zu übergeben, gehen Sie wie folgt vor:<\/p>\n
![](\"..\/fileadmin\/_temp_\/{645124FA-5868-4ABF-AB89-D65E11FCA858}\/pic001.png\")
<\/p>\n
<\/b><\/p>\n
Der weitere Ablauf erfolgt automatisch: Access exportiert die Daten in eine .xls-Datei und öffnet diese mit der Version von Microsoft Excel, die auf Ihrem PC als Standard für .xls-Dateien definiert ist.<\/p>\n
Der Vorteil dieser Vorgehensweise liegt auf der Hand: Die gesamte Abwicklung mit Excel übernimmt Access selbständig. Es ist also wirklich keine Programmierung erforderlich. Die Programmfunktion zum Exportieren nach Excel steht sofort nach der Installation von Microsoft Access zur Verfügung.<\/p>\n
![](\"..\/fileadmin\/_temp_\/{645124FA-5868-4ABF-AB89-D65E11FCA858}\/pic002.png\")
<\/p>\n
<\/b><\/p>\n
![](\"..\/fileadmin\/_temp_\/{645124FA-5868-4ABF-AB89-D65E11FCA858}\/pic003.png\")
<\/p>\n
Bild 2: So sehen die Artikeldaten im Excel-Arbeitsblatt aus. <\/span><\/b><\/p>\n <\/b><\/p>\n Für das Erstellen der Abbildungen wurde die Artikeltabelle der Nordwind-Datenbank nach Excel 2000 exportiert. Hier fällt ein weiterer Vorteil des Office-Symbols auf: Das entstehende Arbeitsblatt erhält automatisch auch einige Formatierungen, die beispielsweise die überschriftenzeile besonders hervorheben (siehe Bild 2).<\/p>\n Ein weiterer Vorteil des Office-Symbols führt gleichzeitig zu einem triftigen Nachteil: Access hält sich beim Excel-Export an die Programmfunktion von Excel 5.0 als kleinsten gemeinsamen Nenner. Das ist vermutlich der Grund dafür, dass generell nur Texte mit einer Länge von bis zu 255 Zeichen exportiert werden können. Insbesondere beim Export von Memofeldern tritt das Problem auf, das Excel darüber hinausgehende Texte einfach abschneidet.<\/p>\n Bestimmte Formatierungen von Zahlenwerten (z.B. Währungssymbole), Hyperlinks und Zeilenumbrüchen in Textfeldern gehen ebenfalls beim Export verloren oder werden fehlerhaft umgewandelt. So wandelt Excel einige Zahlen in Texte um und das führt dazu, dass Excel mit diesen Werten nicht rechnen kann.<\/p>\n Die genannten Probleme lassen sich allesamt lösen, erfordern jedoch ein wenig Programmieraufwand. Die folgenden Abschnitte beschreiben die erforderlichen Lösungsansätze.<\/p>\n Export automatisch<\/b><\/p>\n Wie bei Access nicht anders zu erwarten, können Sie die Programmfunktion zum Export nach Excel auch in VBA benutzen. Um dies an einem einfachen Beispiel nachvollziehen zu können, öffnen Sie die Nordwind-Datenbank oder die Beispieldatenbank ExportToExcel.mdb.<\/p>\n Mit einer Abfrage stellen Sie die zu exportierenden Daten zusammen. Sie können entweder eine eigene Abfrage entwerfen oder die Abfrage qryBestellungenUndKunden aus der Beispieldatenbank zu diesem Beitrag verwenden (siehe Bild 3).<\/p>\n Fügen Sie der Abfrage alle Felder hinzu, die Sie exportieren möchten. Definieren Sie auch die Kriterien, mit denen Sie die zum Export vorgesehenen Datensätze selektieren. Um das VBA-Programm für den Export zu entwerfen, gehen Sie wie folgt vor:<\/p>\n <\/b><\/p>\n Als Ergebnis dieser Schritte erhalten Sie eine Befehlsschaltfläche (siehe Bild 4). Das Bild der Schaltfläche gleicht dem des Office-Symbols für den Export nach Excel. Um die Schaltfläche zu programmieren, gehen Sie wie folgt vor:<\/p>\n Mit den genannten Schritten haben Sie die Ereignisprozedur für das BeimKlicken-Ereignis der Schaltfläche vollständig definiert. In der Formularansicht können Sie das Ergebnis Ihrer Arbeit überprüfen und die Schaltfläche anklicken (siehe Bild 5). Access öffnet daraufhin das Datenblatt der Abfrage qryBestellungenUndKunden und exportiert die Daten nach Excel. Der Export hat die gleichen Vor- und Nachteile wie die Verwendung des Office-Symbols.<\/p>\n <\/b><\/p>\n <\/b><\/p>\n Export in eine Datei<\/b><\/p>\n Die beiden bisher beschriebenen Methoden führen zu dem gleichen Ergebnis: Access exportiert die Daten in eine Excel-Datei und öffnet diese mit Excel. Die entstehende Datei enthält auch einige Formatierungen, die automatisch übertragen werden.<\/p>\n Das Generieren der Formatierungen ist zwar komfortabel, benötigt aber Zeit. Wenn Sie große Datenmengen an Excel weitergeben wollen, dann sollten Sie stattdessen die TransferText- oder die TransferSpreadSheet-Methode verwenden. Beide Methoden stehen als VBA-Befehl und als Makroaktion zur Verfügung.<\/p>\n Mit der TransferText-Methode können Sie die Daten beispielsweise in eine .csv-Datei exportieren. .csv-Dateien enthalten für jeden Datensatz eine eigene Textzeile.<\/p>\n Die Felder des Datensatzes sind durch Semikola getrennt. Textfelder sind durch doppelte Anführungszeichen eingeklammert.<\/p>\n Der folgende Befehl exportiert die Daten der Abfrage qryBestellungenUndKunden in die Textdatei Bestellungen.csv:<\/p>\n Der Vorteil von .csv-Dateien besteht darin, dass Sie aus reinem Text bestehen. Sie können Sie also auch mit einem Editor wie Notepad öffnen und ansehen (siehe Bild 6).<\/p>\n Für den Export in eine Datei mit dem Format von Excel-Dateien hält Access einen eigenen Befehl bereit. Die folgende VBA-Anweisung exportiert die Daten der Abfrage qryBestellungenUndKunden in die .xls-Datei Bestellungen.xls:<\/p>\n Der Vorteil dieser Methode besteht darin, dass Sie die .xls-Datei ohne Konvertierung direkt mit Excel öffnen können. Mit dem zweiten Parameter, der im Beispiel den Wert 8 hat, geben Sie übrigens die Excel-Version an, deren Dateiformat der Export verwenden soll. Die 8 steht für acSpreadsheetTypeExcel8, was dem Dateiformat von Excel 2000 entspricht.<\/p>\n .csv- und .xls-Dateien sind aufgrund der guten Performance beim Erzeugen der Dateien insbesondere für den Austausch großer Datenmengen sehr gut geeignet. Da .csv-Dateien wirklich nur die reinen Daten (zwar als Text- und nicht als Binärdaten) enthalten, benötigen sie außerdem vergleichsweise wenig Speicherplatz.<\/p>\n Der größte Nachteil besteht darin, dass Sie keine Formatierungen an Excel übergeben können. So lassen sich beispielsweise Zahlenwerte nicht ohne weiteres auch direkt mit dem Währungssymbol in Excel formatieren. Die Tätigkeit des Formatierens muss der Anwender nach jedem Datenexport selbst vornehmen.<\/p>\n Export mit Automatisierung<\/b><\/p>\n Die optisch ansprechendste und gleichzeitig langsamste Methode zum Export von Daten nach Excel ist die Verwendung der Automatisierung (ehemals: OLE-Automation). Mit dieser Methode können Sie die Daten einer Tabelle oder Abfrage sozusagen zellenweise in ein Excel-Arbeitsblatt übertragen.<\/p>\n Da Sie bei der Automatisierung auf alle Excel-Methoden und -Eigenschaften zugreifen können, stehen Ihnen bei der Formatierung des Arbeitsblatts alle Möglichkeiten von Excel zur Verfügung. Aber auch der Nachteil dieser Methode soll an dieser Stelle nicht verschwiegen werden:<\/p>\n Um alle Möglichkeiten ausschöpfen zu können, müssen Sie praktisch jeden einzelnen Schritt mit VBA programmieren. Der Preis für die Vielfalt an Möglichkeiten besteht also in einem vergleichsweise hohen Programmieraufwand. Außerdem ist die OLE-Automation die langsamste Methode zur übertragung von Daten nach Excel.<\/p>\n Als Grundlage für den Datenexport dient die Abfrage qryBestellungenUndKunden aus der Beispieldatenbank zu diesem Beitrag. Die Datenbank enthält das Formular frmBestellungenFiltern, mit dem sich Bestellungen filtern und als Liste anzeigen lassen. Das Formular enthält die Schaltfläche btnExportToExcel mit der Bitmap MS Excel als Symbol. Bei einem Klick auf die Schaltfläche führt Access die Prozedur aus Quellcode 1 aus.<\/p>\n Die Ereignisprozedur fragt mit Me.Filter den Filter des aktuellen Formulars ab und speichert ihn in der String-Variablen sFilter. Die folgende If-Anweisung prüft, ob überhaupt ein Filter definiert ist. Falls nein, fragt die MsgBox-Funktion, ob der Anwender alle Datensätze an Excel übergeben möchte. Wenn der Anwender diese Frage mit einem Klick auf die Schaltfläche Nein beantwortet, beendet die Exit Sub-Anweisung die Prozedur. Andernfalls speichert Access die folgende SQL-Anweisung als Datenquelle für den Datenexport:<\/p>\n Wenn das Formular einen Filter hat, soll dieser auch für den Datenexport verwendet werden. In diesem Fall fügt Access den gesamten Ausdruck einfach als Where-Klausel an die Select-Anweisung an. Wenn der Anwender die Bestellungen beispielsweise nach Alfreds Futterkiste gefiltert hat, führt der Ausdruck<\/p>\n zu folgendender Select-Anweisung:<\/p>\n <\/p>\n Ob nun mit oder ohne Filter: Die Ereignisprozedur speichert die SQL-Anweisung in der String-Variablen sSQL. Für den weiteren Ablauf ist aber eine Abfrage als Datenquelle für den Export erforderlich.<\/p>\n Aus diesem Grund weisen die folgenden Anweisungen die SELECT-Anweisung der SQL-Eigenschaft eines QueryDefs-Objekts zu. Da das QueryDefs-Objekt nicht jedes Mal neu generiert wird, muss es vor Aufruf der Prozedur bereits in der aktuellen Datenbank enthalten sein. Dabei genügt es allerdings, irgendeine Auswahlabfrage als Rahmen unter dem Namen qryExportToExcel zu speichern.<\/p>\n Den Abschluss der Ereignisprozedur bildet der Aufruf der Prozedur ExportQueryToExcel. Diese Prozedur ist im Modul modExcelTransfer der Beispieldatenbank enthalten und führt den Export der Daten nach Excel durch.<\/p>\n Die erste Aufgabe der Prozedur ExportQueryToExcel besteht darin, einen Namen für die neue Excel-Datei zu ermitteln. Dies geschieht durch einen Aufruf der Funktion ExcelDateiNameErmitteln( ), die einen Dateinamen inklusive Pfad als Funktionsparameter zurückgibt. Aber nach welchen Regeln wird der neue Name gebildet Die Funktion ermittelt zunächst mit GetDBPath( ) den Pfad des aktuellen Projektes. Der Pfad wird um den Dateinamen ergänzt, der sich aus dem Text Bestellungen, dem aktuellen Tagesdatum im Format JJJJMMTT, einer dreistelligen fortlaufenden Nummer und der Erweiterung .xls zusammensetzt.. Das Listing aus Quellcode 2 zeigt die Funktion im Detail.<\/p>\n Damit keine Datei unbeabsichtigt überschrieben wird, überprüft die Funktion auch die vorhandenen Dateien. Wenn sich die Beispieldatenbank im Verzeichnis C:\\ExportToExcel befindet, liefert die Funktion beim ersten Aufruf den Dateinamen<\/p>\n und wenn diese Datei bereits angelegt ist, den folgenden Dateinamen:<\/p>\n Da die Vergabe der fortlaufenden Nummer auf drei Stellen begrenzt ist, lautet der höchste Dateiname:<\/p>\n Dieser Wert wird erst überschritten, wenn mehr als 999 Dateien an einem Tag angelegt werden. Falls Sie an diese Grenze stoßen sollten, können Sie die Anzahl der Stellen in der Format-Funktion und in der While-Anweisung erhöhen.<\/p>\n Der Aufruf der Funktion ExcelDateiNameErmitteln erfolgt aus der Prozedur ExportToExcel heraus mit den Anweisungen aus Quellcode 3.<\/p>\n Für den Fall, dass die Funktion keinen gültigen Dateinamen ermitteln kann, bricht Access die Ausführung der Prozedur ExportToExcel mit Exit Sub ab.<\/p>\n Um auf die Eigenschaften und Methoden von Excel zugreifen zu können, müssen Sie zuerst ein Objekt definieren und ihm eine Excel-Instanz zuweisen. Der VBA-Code aus Quellcode 4 führt diese Aufgabe durch.<\/p>\n In der If-Anweisung wird die Funktion IstExcelGestartet( ) aufgerufen. Falls diese Funktion den Wert True liefert, erhält das Objekt ExcelSheet einen Verweis auf die laufenden Excel-Applikation. Andernfalls erzeugt die CreateObject( )-Funktion ein neues Excel-Objekt und liefert gleichzeitig den Verweis darauf als Funktionswert zurück.<\/p>\n In jedem Fall kann nach der Ausführung einer der beiden Funktionen davon ausgegangen werden, dass die Objekt-Variable ExcelSheet auf eine laufende Excel-Applikation verweist. Zur Sicherheit wird mit den Anweisungen aus Quellcode 5 noch einmal überprüft, ob der Vorgang auch wirklich erfolgreich abgeschlossen werden konnte.<\/p>\n Falls das Excel-Objekt nicht erzeugt werden konnte, trifft die Bedingung Is Nothing zu. Dieser Fall kann beispielsweise dann eintreten, wenn Excel auf dem PC nicht installiert ist.<\/p>\n überprüfen, ob Excel gestartet ist<\/b><\/p>\n Aber wie kann die Funktion IstExcelGestartet überhaupt feststellen, ob Excel gestartet ist Die Prüfung erfolgt – wie der Programmcode aus Quellcode 6 zeigt – durch den Aufruf der GetObject-Funktion in Kombination mit einer On Error Resume Next-Anweisung.<\/p>\n Mit der Set-Anweisung wird der Objektvariablen obj eine Referenz auf eine Excel-Applikation zugewiesen. Falls Excel zu diesem Zeitpunkt nicht gestartet sein sollte, dann führt die Set-Anweisung zu einem Fehler und in der Folge auch zum Programmabbruch.<\/p>\n Die On Error Resume Next-Anweisung verhindert jedoch den Programmabbruch und führt dazu, dass die auf Set folgende Anweisung in jedem Falls ausgeführt wird. Diese Anweisung ermittelt nun anhand der aktuellen Fehlernummer, ob das Excel-Objekt erfolgreich zugewiesen werden konnte. Falls kein Fehler aufgetreten ist, ist Excel bereits gestartet und die Funktion liefert den Wert True zurück.<\/p>\n Eine Excel-Arbeits-mappe öffnen<\/b><\/p>\n Die Objektvariable ExcelSheet enthält eine Referenz auf die laufende Excel-Applikation. Mit ihr können Sie nun auf alle Eigenschaften und Methoden von Excel zugreifen. Bei der Programmierung verwenden Sie prinzipiell die gleiche Punktnotation wie in Access. Die Anweisungen aus Quellcode 7 machen die Excel-Applikation sichtbar und bringen das Fenster auf Normalgröße.<\/p>\n Da das Excel-Fenster von Windows in den Vordergrund gestellt wird, können Sie bei einem Test der Prozedur den weiteren Ablauf direkt am Bildschirm verfolgen und beobachten, wie die folgenden Anweisungen (s. Quellcode 8) ein neues Excel-Arbeitsblatt anlegen und es auf der Festplatte speichern. Als Dateiname wird hier der von der Funktion ExcelDateiNameErmitteln ermittelte Name verwendet.<\/p>\n Die If-Anweisung überprüft den Erfolg der Aktion. Falls Excel das Arbeitsblatt nicht anlegen konnte, liefert die Count-Eigenschaft des Workbook-Objekts den Wert 0. Access zeigt dann eine Fehlermeldung auf dem Bildschirm an und beendet die Prozedur ExportQueryToExcel.<\/p>\n Nachdem nun alle Vorbereitungen abgeschlossen sind, kann die Datenübertragung endlich beginnen. Die erste Aufgabenstellung ist die übertragung der Feldnamen in die erste Zeile des Excel-Arbeitsblatts. Die Feldnamen dienen als überschriften und werden später auch als solche formatiert.<\/p>\n Der Zugriff auf die zuvor definierte Abfrage qryExportToExcel erfolgt über das DAO-Recordset rDaten. Die Anweisung rDaten.MoveFirst positioniert den Datensatzzeiger auf den ersten Datensatz des Recordsets (s. Quellcode 9).<\/p>\n Mit der Fields-Auflistung des QueryDefs-Objekts greifen Sie direkt auf die Feldnamen der Abfrage zu. Die For Next-Schleife (s. Quellcode 10) durchläuft die gesamte Auflistung, liefert also die Ordinalnummern aller Felder beginnend bei 0.<\/p>\n Der mit Fields(iSpalte).Name ermittelte Name einer Spalte wird zunächst in der Variablen vCellValue zwischengespeichert (s. Quellcode 11). In der darauffolgenden Anweisung gibt<\/p>\n zunächst die durch Zeilennummer und Spaltennummer identifizierte Zelle des Excel-Arbeitsblatts an (siehe Bild 7). Hier ist darauf zu achten, dass die Nummerierung bei Excel mit 1 beginnt. Die in iSpalte hinterlegte Spaltennummer darf deshalb nur mit dem Offset 1 verwendet werden.<\/p>\n <\/b><\/p>\n Auf die übertragung der Feldnamen erfolgt die übertragung der einzelnen Datensätze. Da das Recordset rDaten weiter oben schon mit MoveFirst auf den ersten Datensatz positioniert wurde, kann es direkt mit einer While-Schleife losgehen. Access durchläuft den Schleifenrumpf für jeden Datensatz der Abfrage qryExportToExcel einmal.<\/p>\n Innerhalb der Leseschleife durchläuft eine zweite Schleife alle Felder des Datensatzes. Diese zweite Schleife ist wegen der Besonderheiten der Felder E-Mail und Hyperlink erforderlich.<\/p>\n Besonderheiten für E-Mail- und Hyperlink-Felder<\/b><\/p>\n In der Beispieldatenbank haben die Felder E-Mail und Hyperlink den Datentyp Hyperlink. Durch das einfache Zuweisen der Feldwerte in ein Excel-Arbeitsblatt werden zwar die Web- und E-Mail-Adressen übertragen, aber leider nur als Text. <\/p>\n Als besonderen Komfort können Sie Excel aber veranlassen, die übertragenen Texte auch direkt als Hyperlinks zu interpretieren. Das hat den Vorteil, dass der Excel-Anwender die Hyperlinks nur anzuklicken braucht, um eine Web-Seite mit dem Standardbrowser zu öffnen oder um eine Mail mit dem Standard-Mailprogramm zu versenden.<\/p>\n Damit das Ganze funktioniert, wird auf der Access-Seite mit<\/p>\n die eigentliche Adresse aus dem Hyperlink-Feld extrahiert und in der Variablen vCellValue gespeichert. Bei der Zuweisung des Werts an eine Excel-Zelle kommt eine weitere Funktion zum Einsatz, die aber diesmal aus dem Sprachvorrat von Excel stammt. Mit Hyperlink.Add fügen Sie in Excel ein neues Hyperlink-Feld hinzu. Mit Anchor spezifizieren Sie die Zelle für den Hyperlink und mit Adresse die eigentliche Web- oder Mail-Adresse.<\/p>\n Diese Anweisung fügt den Wert von vCellValue im Excel-Arbeitsblatt als Hyperlink ein. Wenn der Anwender den Hyperlink später anklickt, startet Excel die Standardanwendung (in der Regel Internet Explorer oder Microsoft Outlook) für den Hyperlink (siehe Bild 8).<\/p>\n <\/b><\/p>\n <\/b><\/p>\n In der Tabelle Kunden der Beispieldatenbank sind sowohl Hyperlink als auch E-Mail als Hyperlink-Felder definiert. Die unterschiedliche Bedeutung der beiden Felder ergibt sich erst aus der Dateneingabe. Wenn Sie beispielsweise den Text http:\/\/www.fullaccess.de eingeben, interpretiert Access das Feld als Web-Adresse und startet beim einem Klick auf den Hyperlink den Web-Browser. Falls Sie hingegen mailto:ihrkontakt@fullaccess.de eingeben, startet Access den E-Mail-Client und öffnet ein Mailfenster mit ihrkontakt@fullaccess.de als Empfängeradresse. <\/p>\n Mehrzeilige Texte<\/b><\/p>\n Die Tabelle Kunden der Beispieldatenbank wurde aus der im Lieferumfang von Access enthaltenen Nordwind-Datenbank entnommen und ist auch in der Abfrage qryExportToExcel enthalten. Deshalb wirkt sich eine Besonderheit dieser Tabelle auch auf den Excel-Export aus: Im Feld Straße einiger Datensätze befinden sich mehrzeilige Eingaben. Bei Access dient die Tastenkombination Strg + Enter zur Eingabe von erzwungenen Zeilenvorschüben.<\/p>\n Eine nähere Untersuchung einer solchen Texteingabe zeigt, dass Access an der Stelle des Zeilenvorschubs eine Folge der ASCII-Zeichen 13 und 10 (also: carriage return und line feed) speichert.<\/p>\n Falls Sie einen solchen Text per Automation einer Excel-Zelle zuweisen, kann Excel den Zeilenvorschub leider nicht als solchen interpretieren. Bei Excel geben Sie Zeilenvorschübe nämlich mit der Tastenkombination Alt + Enter ein. Excel speichert an dieser Stelle dann das ASCII-Zeichen 10 (line feed). Aus diesem Grund sind die folgenden beiden Zeilen erforderlich:<\/p>\n Mit der Replace-Funktion werden einfach alle Folgen der Zeichen 13 und 10 durch das ASCII-Zeichen 10 ersetzt. Damit lassen sich dann auch mehrzeilige Texte fehlerfrei nach Excel einfach übertragen.<\/p>\n Zahlenfelder<\/b><\/p>\n Die VBA-Routine für den Datenexport unterscheidet nur zwischen Hyperlink- und anderen Feldern. Eine weitere Unterscheidung zwischen Text- und Zahlenfeldern erfolgt an dieser Stelle nicht und ist prinzipiell auch nicht erforderlich.<\/p>\n Es sei denn, dass Sie mit den Zahlen in Excel auch rechnen wollen. Dann werden Sie nämlich feststellen, dass Access zwar die Feldwerte sauber nach Excel überträgt, diese dort aber nicht als Zahlen, sondern als Texte eingefügt werden. Das Rechnen mit diesen Werten ist deshalb nicht möglich.<\/p>\n Die Ursache für dieses Verhalten besteht darin, dass Access die Feldwerte offenbar mit dem Komma als Dezimaltrennzeichen an Excel liefert, Excel die Werte bei der „Eingabe“ über VBA aber in der englischen Schreibweise, also mit dem Punkt als Dezimaltrennzeichen erwartet.<\/p>\n In der Beispieldatenbank macht sich das Konvertierungsproblem bei den Frachtkosten bemerkbar. Um es zu lösen, hat die Abfrage qryBestellungenUndKunden das berechnete Feld Fracht mit folgendem Ausdruck erhalten:<\/p>\n Die Funktion ToEnglish finden Sie im Modul modExcelTransfer. Sie ersetzt das Komma durch den Punkt als Dezimaltrennzeichen. Das alleine reicht aus, damit Excel die Frachtkosten als Zahlen und nicht als Texte interpretiert (s. Quellcode 12).<\/p>\n Nachdem Sie alle Daten in das Excel-Arbeitsblatt übertragen haben, empfiehlt sich die Formatierungen der Zeilen und Spalten. Dieser Bereich des Programmcodes beginnt mit der Definition der Variablen sMaxZeile und sRange. sMaxZeile enthält die Nummer der letzten Zeile des Arbeitsblatts in die Daten geschrieben wurde. sRange enthält den Wert des gesamten Bereichs des Arbeitsblatts als Text (z. B. A1:R10).<\/p>\n Frachtkosten als Währungsfeld formatieren<\/b><\/p>\n Mit der oben beschriebenen Funktion ToEnglish haben Sie das Zahlenfeld Frachtkosten nunmehr auch als Spalte mit numerischen Werten im Arbeitsblatt generiert. <\/p>\n Um die Werte dieser Spalte mit dem Euro-Symbol zu markieren, genügt der folgende Zweizeiler:<\/p>\n Die Select-Methode des Range-Objekts markiert den Bereich, auf den die NumberFormat-Methode angewendet wird.<\/p>\n Wenn Sie sich jetzt fragen, wie man als Programmierer auf solche Zahlenformate kommt, kann ich Sie beruhigen. Sie erhalten den Programmcode, indem Sie in Excel den Befehl Extras \/ Makro aufzeichnen wählen. Excel zeichnet dann alle Befehle, die Sie über die Oberfläche initiieren, als VBA-Code auf, bis Sie den Vorgang mit einem Klick auf das Symbol Aufzeichnung beenden beenden. Anschließend können Sie mit Alt + F11 den Editor von Excel öffnen und sich den aufgezeichneten Code ansehen. Um den Programmcode auch in Access einsetzen zu können, müssen Sie z. B. Range(„C3:E8“).Select durch ExcelSheet.ActiveSheet. Range(„C3:E8“).Select ersetzen, also den Namen des Excel- und des Active-Objekts voranstellen. <\/p>\n Weitere Formatierungen<\/b><\/p>\n Der Programmcode aus Quellcode 13 ist weitgehend selbsterklärend. Er verrichtet folgende Aufgaben:<\/p>\n Enthaltene Beispieldateien:<\/p>\n ExportToExcel00.mdb<\/p>\n![](\"..\/fileadmin\/_temp_\/{645124FA-5868-4ABF-AB89-D65E11FCA858}\/pic004.png\")
<\/p>\nAbfrage entwerfen<\/h3>\n
![](\"..\/fileadmin\/_temp_\/{645124FA-5868-4ABF-AB89-D65E11FCA858}\/pic005.png\")
<\/p>\nDoCmd.OpenQuery _ \"qryBestellungenUndKunden\"\r\nDoCmd.RunCommand acCmdOutputToExcel<\/pre>\n
![](\"..\/fileadmin\/_temp_\/{645124FA-5868-4ABF-AB89-D65E11FCA858}\/pic006.png\")
<\/p>\n![](\"..\/fileadmin\/_temp_\/{645124FA-5868-4ABF-AB89-D65E11FCA858}\/pic007.png\")
<\/p>\nExport als .csv-Datei<\/h3>\n
DoCmd.TransferText acExportDelim, \"\", _ \"qryBestellungenUndKunden\", _ \"C:\\bestellungen.txt\", True, \"\"<\/pre>\n
Export als XLS-Datei<\/h3>\n
DoCmd.TransferSpreadsheet acExport, _ 8, \"qryBestellungenUndKunden\", _ \"C:\\Bestellungen.xls\", True, \"\"<\/pre>\n
Vor- und Nachteile von .csv- und.xls-Dateien<\/h3>\n
Anlegen einer Befehlsschaltfläche<\/h3>\n
SELECT * FROM qryBestellungenUndKunden<\/pre>\n
sSQL = \"SELECT * FROM qryBestellungenUndKunden WHERE \" & sFilter<\/pre>\n
SELECT * FROM qryBestellungenUndKunden WHERE [Kunden-Code] = \"ANTON\"\r\nPrivate Sub btnExportToExcel_Click()\r\n Dim sFilter As String\r\n Dim sSQL As String\r\n sFilter = Me.Filter\r\n If Len(Nz(sFilter)) = 0 Then\r\n If MsgBox(\"Wollen Sie alle Datensätze exportieren\", vbYesNo, _ \"Kein Filter definiert.\") = vbNo Then\r\n Exit Sub\r\n Else\r\n '' Datenquelle für alle Datensätze definieren\r\n sSQL = \"SELECT * FROM qryBestellungenUndKunden\"\r\n End If\r\n Else\r\n '' Datenquelle mit Filter definieren\r\n sSQL = \"SELECT * FROM qryBestellungenUndKunden WHERE \" & sFilter\r\n End If<\/pre>\n
Dim dbs As Database\r\nSet dbs = CurrentDb\r\ndbs.QueryDefs(\"qryExportToExcel\").SQL _ = sSQL\r\ndbs.QueryDefs(\"qryExportToExcel\").Close\r\nExportQueryToExcel<\/pre>\n
Public Function ExcelDateiNameErmitteln() As String\r\n Dim sDateiname As String\r\n sDateiname = GetDBPath() & \"\\Bestellungen\" & Format(Date, \"yyyymmdd\")\r\n Dim sTestname As String\r\n Dim i As Integer\r\n i = 0\r\n Dim lDateiVorhanden As Boolean\r\n lDateiVorhanden = True\r\n '' Ende, wenn bereits 1.000 Dateien an diesen angelegt wurden.\r\n While lDateiVorhanden And i <= 999\r\n sTestname = sDateiname & Format(i, \"000\") & \".xls\"\r\n If Not FileExists(sTestname) Then\r\n lDateiVorhanden = False\r\n sDateiname = sTestname\r\n End If\r\n i = i + 1\r\n Wend\r\n If Not lDateiVorhanden Then\r\n ExcelDateiNameErmitteln = sDateiname\r\n End If\r\nEnd Function\r\nQuellcode 2<\/pre>\n
C:\\ExportToExcel\\Bestellungen20010706000.xls<\/pre>\n
C:\\ExportToExcel\\Bestellungen20010706001.xls<\/pre>\n
C:\\ExportToExcel\\Bestellungen20010706999.xls<\/pre>\n
strExcelDatei = ExcelDateiNameErmitteln()\r\nIf Len(strExcelDatei) = 0 Then\r\n MsgBox \"Es konnte kein Dateiname für die Excel-Datei ermittelt werden. Bitte \" _ & \"löschen Sie nicht mehr benötigte Dateien aus dem Verzeichnis.\", vbCritical, _ sAppname\r\n Exit Sub\r\nEnd If\r\nDim ExcelSheet As Object\r\n''überprüfen, ob Excel gestartet werden muss.\r\nIf IstExcelGestartet() Then\r\n Set ExcelSheet = GetObject(, \"Excel.Application\") '' Excel war schon gestartet\r\nElse\r\n Set ExcelSheet = CreateObject(\"Excel.Application\") '' Excel starten\r\nEnd If\r\n'' Anstehende Ereignisse des Betriebssystems verarbeiten lassen.\r\nDoEvents\r\n'' überprüfen, ob das Excel-Objekt erfolgreich instanziert wurde.\r\nIf ExcelSheet Is Nothing Then\r\n MsgBox \"Das Excel-Objekt konnte nicht erstellt werden.\", vbCritical, sAppname\r\n Exit Sub\r\nEnd If<\/pre>\n
Public Function IstExcelGestartet() As Boolean\r\n '' Stellt fest, ob Excel gerade geladen ist\r\n Dim obj As Object\r\n On Error Resume Next\r\n '' Prüfen, ob Excel-Objekt instanziert werden kann.\r\n Set obj = GetObject(, \"Excel.Application\")\r\n IstExcelGestartet = (Err.Number = 0)\r\n Set obj = Nothing\r\nEnd Function\r\n'' Excel mit Hilfe des Application-Objekts bereitstellen\r\nExcelSheet.Application.Visible = True\r\nConst xlMinimized = -4140\r\nConst xlNormal = -4143\r\nIf ExcelSheet.WindowState = xlMinimized Then\r\n ExcelSheet.WindowState = xlNormal\r\nEnd If\r\n'' Neues Workbook hinzufügen\r\nExcelSheet.workbooks.Add\r\n'' Das Excel-Sheet speichern\r\nExcelSheet.ActiveWorkbook.SaveAs Filename:=strExcelDatei\r\nIf ExcelSheet.workbooks.Count = 0 Then\r\n MsgBox \"Momentan ist keine Excel-Mappe geöffnet.\", _ vbCritical, sAppname\r\n Exit Sub\r\nEnd If<\/pre>\n
Set dbs = CurrentDb()\r\n Set rDaten = dbs.OpenRecordset(\"qryExportToExcel\")\r\n rDaten.MoveFirst\r\n '' Feldnamen in Kopfzeile schreiben\r\n Dim iSpalte As Integer\r\n Dim iZeile As Integer\r\n iZeile = 1\r\n For iSpalte = 0 To dbs.QueryDefs(\"qryExportToExcel\").Fields.Count - 1\r\n vCellValue = dbs.QueryDefs(\"qryExportToExcel\").Fields(iSpalte).Name\r\n ExcelSheet.ActiveSheet.Cells(iZeile, iSpalte + 1).Value = vCellValue\r\n Next iSpalte\r\n'' Daten übertragen\r\niZeile = 2\r\nWhile Not rDaten.EOF\r\n For iSpalte = 0 To dbs.QueryDefs(\"qryExportToExcel\").Fields.Count - 1\r\n If rDaten.Fields(iSpalte).Name = \"Homepage\" Or rDaten.Fields(iSpalte).Name = _ \"E-Mail\" Then\r\n '' Adresse des Hyperlink-Feldes ermitteln\r\n vCellValue = HyperlinkPart(Nz(rDaten.Fields(iSpalte)), acAddress)\r\n '' Hyperlink in Excel einfügen\r\n ExcelSheet.ActiveSheet.Hyperlinks.Add Anchor:=ExcelSheet. _\r\n ActiveSheet.Cells(iZeile, iSpalte + 1), Address:=vCellValue\r\n Else\r\n '' ASCII 13,10 durch ASCII 10 ersetzen\r\n vCellValue = Replace(Nz(rDaten.Fields(iSpalte).Value), vbCrLf, vbLf)\r\n ExcelSheet.ActiveSheet.Cells(iZeile, iSpalte + 1).Value = vCellValue\r\n End If\r\n Next iSpalte\r\n iZeile = iZeile + 1\r\n rDaten.MoveNext\r\nWend '' Daten übertragen<\/pre>\n
Sheet.ActiveSheet.Cells(iZeile, iSpalte + 1).Value<\/pre>\n
![](\"..\/fileadmin\/_temp_\/{645124FA-5868-4ABF-AB89-D65E11FCA858}\/pic008.png\")
<\/p>\nvCellValue = HyperlinkPart(Nz(rDaten. _ Fields(iSpalte)), acAddress)<\/pre>\n
ExcelSheet.ActiveSheet.Hyperlinks.Add _ Anchor:=ExcelSheet.ActiveSheet. _ Cells(iZeile, iSpalte + 1), _ Address:=vCellValue<\/pre>\n
![](\"..\/fileadmin\/_temp_\/{645124FA-5868-4ABF-AB89-D65E11FCA858}\/pic009.png\")
<\/p>\n![](\"..\/fileadmin\/_temp_\/{645124FA-5868-4ABF-AB89-D65E11FCA858}\/pic010.png\")
<\/p>\nHinweis<\/h3>\n
Public Function ToEnglish(V As Variant) As String\r\n ToEnglish = Replace(Format(V, \"General Number\"), \",\", \".\")\r\nEnd Function\r\nvCellValue = Replace (Nz(rDaten. _ Fields(iSpalte).Value),vbCrLf, _ vbLf)\r\nExcelSheet.ActiveSheet.Cells(iZeile, _ iSpalte + 1).Value = vCellValue<\/pre>\n
Fracht: ToEnglish([Frachtkosten])<\/pre>\n
Dim sMaxZeile As String\r\nsMaxZeile = LTrim(Str(iZeile - 1))\r\nDim sRange As String\r\nsRange = \"A1:R\" & sMaxZeile<\/pre>\n
ExcelSheet.ActiveSheet.Range(\"J2:J\" _ & sMaxZeile).Select\r\nExcelSheet.Selection.NumberFormat = _\r\n \"_ * #,##0.00_ [$€-1]_ ;_ * -_ #,##0.00 [$€-1]_ ;_ * _ \"\"-\"\"_ [$€-1]_ ;_ @_ \"\r\n'' Ganzes Sheet formatieren\r\nExcelSheet.ActiveSheet.Range(sRange).Select\r\nWith ExcelSheet.Selection\r\n .WrapText = False\r\n .Orientation = 0\r\n .ShrinkToFit = True\r\n .MergeCells = False\r\nEnd With\r\n'' Spaltenbreite anpassen\r\nExcelSheet.ActiveSheet.columns(\"A:R\"). _ Entirecolumn.autofit\r\n'' überschrift formatieren\r\nExcelSheet.ActiveSheet.Range(\"A1:R1\").Select\r\nWith ExcelSheet.Selection\r\n .Interior.ColorIndex = 36\r\n .Font.ColorIndex = 1\r\n .Font.Bold = True\r\nEnd With\r\nQuellcode 13<\/pre>\n
Praxis-Tipp<\/h3>\n
'' Ausrichtung für gesamtes Sheet ohne überschriftenzeile '' einstellen\r\nExcelSheet.ActiveSheet.Range(\"A2:R\" & sMaxZeile).Select\r\nDim xlGeneral As Integer\r\nxlGeneral = 1\r\nDim xlTop As Integer\r\nxlTop = -4160\r\nDim xlBottom As Integer\r\nxlBottom = -4107\r\nWith ExcelSheet.Selection\r\n .HorizontalAlignment = xlGeneral\r\n .VerticalAlignment = xlBottom\r\n .WrapText = True\r\n .Orientation = 0\r\n .AddIndent = False\r\n .ShrinkToFit = True\r\n .MergeCells = False\r\nEnd With\r\n'' Zeilenhöhe anpassen\r\nExcelSheet.ActiveSheet.Rows(\"1:\" & sMaxZeile).EntireRow.autofit\r\nExcelSheet.ActiveSheet.Cells(1, 1).Select\r\nExit_ExportQueryToExcel:\r\n'' Schließen von Excel mit der Quit-Methode des\r\n'' Application-Objekts.\r\nExcelSheet.ActiveWorkbook.Save\r\n'' Freigeben der Objektvariable.\r\nSet ExcelSheet = Nothing\r\nrDaten.Close\r\nSet rDaten = Nothing\r\nSet dbs = Nothing\r\nExit Sub\r\n''Fehlerbehandlung:\r\nErr_ExportQueryToExcel:\r\n MsgBox Err.Description, vbCritical, \"Fehler beim Export zu Excel\"\r\n Resume Exit_ExportQueryToExcel\r\nQuellcode 13 (Fortsetzung)<\/pre>\n
Downloads zu diesem Beitrag<\/h3>\n