Office: Export - Tabellenblatt löschen

Hallo,

ich möchte von Access zu Excel exportieren. Leider überschreibt er mir die Daten nicht. Stattdessen kommt die Fehlermeldung 3435 "Zellen in Tabelle können nicht gelöscht werden".
Gibt es einen Weg, aus Access heraus Tabellenblätter zu löschen bzw. eine andere Möglichkeit, dass der Export funktioniert und die alten Daten mit den neuen überschreiben werden?

Danke für eure Mithilfe.
MfG
Florian

 

Wie genau wird der Export denn erledigt? Ich denke, da wird, ggf. hinter einem Button versteckt, eine kleine VBA-Routine laufen, oder? Dann kann man da bestimmt irgendwie was einbauen, um das Blatt vorher zu löschen.

Gruß, Michael

 

Den Export mache ich über den Makro "Transferarbeitsblatt". Dann wird mit dem Makro "AusführenAnwendung" die Excel Mappe geöffnet, in welcher dann ein Makro durchläuft, der das Tabellenblatt löscht.
On Error Resume Next
Application.DisplayAlerts = False
Sheets("aufwand_salden").Delete
Application.DisplayAlerts = True

Läuft alles, aber natürlich sehr langsam. Ich bin nicht sonderlich fit was VBA betrifft. Deswegen wüsste ich nicht, wie eine kleine "VBA-Routine" laufen könnte.

MfG
Florian

 

Ähm... In der Reihenfolge? Was bringt Dir dann der Export, wenn Du ihn direkt danach wieder löscht?

Das Löschen geht übrigens schief, wenn versucht wird, das letzte / einzige Blatt in der Mappe zu löschen... (was Du dann wegen "On Error..." nicht mitbekommen würdest.)

Was ich mir vorstellen könnte:
- Access erstellt ein Excel-Objekt (1), mit dem die "alte" Version der Daten geöffnet wird.
- Dann wird das "alte" Blatt gelöscht. (2)
- Die Mappe wird wieder abgespeichert.
- Die neuen" Daten werden von Access in die Mappe exportiert.
- Die aktualisierte Mappe wird mit Excel geöffnet. (3)

(1) Das hat den Vorteil, daß man Excel quasi "fernsteuern" kann und nicht auf Autostart-Makros in der Mappe angewiesen ist.
(2) im Detail würde ich wohl:
- Umbenennen des Datenblatts in "tmp"
- Einfügen eines neuen, leeren Blatts
- neues Blatt in "aufwand_salden" umbenennen
- Blatt "tmp" löschen
(3) Hier nicht über ein Excel-Objekt, sondern z.B. per ShellExecute.

Ist jetzt nicht bis ins Details durchdacht, aber wenn nichts dazwischen kommt, müßte das ein gangbarer Weg sein.

Gruß, Michael

 

Ich habe mich falsch ausgedrückt. Natürlich wird das alte Tabellenblatt erst gelöscht bevor der Export statt findet.

Genau so läuft es im Moment. Aber der Schritt (2) störrt mich.
Aber es ist OK. Danke. Wie funktioniert ShellExecute? Dann könnte ich doch auf den AusführenAnwendung Makro verzichten? Der ist nämlich nicht Access 2007 kompatiebel und muss, wenn möglich, auch verschwinden.

MfG
Florian

 

Während (1) noch mit "Bordmitteln" innerhalb des VBA im Access erledigt werden kann (CreateObject), muß für (3) eine API-Funktion deklariert werden.

Ich kann Dir sagen, wie es unter Windows XP mit Office 2000 funktioniert. Ob das aber unter Windows 7 auch noch funktioniert, weiß ich nicht 100%ig. Solange es 32-bittig bleibt, müßte es aber theoretisch...

Ich schau mir das mal genauer an und meld mich dann wieder...

Gruß, Michael

 

Nun bin ich über eine Kleinigkeit gestolpert...

Es bringt gar nichts, in Excel ein leeres Blatt für den Export aus Access bereitzustellen, da Access beim Export sowieso ein neues Blatt anlegt.

Von daher ist die Vorgehensweise etwas anders...

Das bestehende Daten-Blatt muß in der Excel-Mappe gelöscht werden, es darf aber kein neues mit dem Namen angelegt werden.

Der Name ist quasi von Access vorgegeben, denn der lautet so, wie die exportierte Tabelle / Abfrage.

Wenn einem das nicht gefällt, müßte man also nach dem Export nochmals in der Excel-Mappe herumdoktorn, um den Export-Blattnamen in den gewünschten umzubenennen.

Ich hab da mal was zusammengebastelt, will das aber noch kurz etwas überarbeiten...

Gruß, Michael

 

Ach so stimmt, dieses hätte ich dir auch sagen können, wenn ich daran gedacht hätte.
Im Moment wird 1.das Blatt gelöscht, 2.die Daten exportiert und 3. aktualisiert - ein Kreislauf.
Ich würde den Schritt mit dem "Löschen" übergehen und einfach überschreiben.
Das ist aber nicht möglich, habe ich mir sagen lassen, weil das Tabellenblatt mit Formeln und Makros bei der Aktualisierung "aufgehübscht" wird. Somit entspricht es nicht mehr dem ursprünglichen und eine Überschreibung funktioniert nicht mehr. Deswegen der ganze HickHack mit dem "löschen".
Vielen Dank schon einmal für die Mühe die du dir machst.
MfG
Florian

 

Ich weiß ja nun nicht genau, was denn noch das Excel-Makro macht, um das Tabellenblatt aufzubereiten. Aber von Access-Seite aus könnte es so wie im Anhang funktionieren.

Ich hab da eine Tabelle, darauf eine Abfrage und dazu passend ein Formular gebaut. Das Formular ist ein Endlos-Formular mit dem (einzigen) Datenfeld der Abfrage. Der eine Button schließt das Formular, der andere macht den Export in Richtung Excel.

Was im Source dahinter passiert:

Code:

    ' ==================================================
    ' (1)
    ' Blatt "datensatz" aus Mappe "datensatz.xls" löschen
    ' ==================================================

    ' Excel als COM-Objekt erstellen und anzeigen
    Set oExcel = CreateObject("Excel.Application")
    oExcel.Visible = True
    ' Mappe laden
    oExcel.workbooks.Open ExcelLaufwerk & ExcelPfad & ExcelDatei
    ' ggf. Temp-Blatt löschen
    On Error Resume Next: oExcel.worksheets(ExcelTmpBlatt).Delete: On Error GoTo 0
    ' Daten-Blatt in Temp-Blatt umbenennen
    On Error Resume Next: oExcel.worksheets(ExcelDatenBlatt).Name = ExcelTmpBlatt: On Error GoTo 0
    oExcel.displayalerts = False: On Error Resume Next: oExcel.worksheets(ExcelTmpBlatt).Delete: On Error GoTo 0: oExcel.displayalerts = True
    ' Mappe schließen und dabei speichern
    oExcel.workbooks(ExcelDatei).Close savechanges:=True
    ' Excel beenden und Objekt freigeben
    oExcel.Quit
    Set oExcel = Nothing

Hier wird, zugegeben etwas aufwändig, dafür aber sicher, das Blatt "datensatz" aus der Mappe "datensatz.xls" gelöscht. Genau genommen wird es zunächst nur umbenannt. Sollte das Löschen dann nämlich nicht gehen (z.B. einziges Blatt in der Mappe), dann ist zumindest der Blattname kein Konflickt mehr.

Ich hab dazu mal eine fast leere Mappe mit nur dem Blatt "xxx" dazugepackt. Wenn Du die in C:\ ablegst oder im Access-VBA die Konstanten anpaßt...

Code:

    ' ==================================================
    ' (2)
    ' Abfrage "qry_datensatz" in "datensatz.xls" exportieren
    ' ==================================================

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, AccessAbfrage, ExcelLaufwerk & ExcelPfad & ExcelDatei

Das hier ist dann der eigentliche Export. Danach ist wieder ein Blatt "qry_datensatz" in der Mappe.

Code:

    ' ==================================================
    ' (2a)
    ' Nacharbeit: Export-Blatt in Daten-Blatt umbenennen
    ' ==================================================

    ' Excel als COM-Objekt erstellen und anzeigen
    Set oExcel = CreateObject("Excel.Application")
    oExcel.Visible = True
    ' Mappe laden
    oExcel.workbooks.Open ExcelLaufwerk & ExcelPfad & ExcelDatei
    ' Temp-Blatt löschen
    oExcel.displayalerts = False: On Error Resume Next: oExcel.worksheets(ExcelTmpBlatt).Delete: On Error GoTo 0: oExcel.displayalerts = True
    ' Export-Blatt in Daten-Blatt umbenennen
    oExcel.worksheets(AccessAbfrage).Name = ExcelDatenBlatt
    ' Mappe schließen und dabei speichern
    oExcel.workbooks(ExcelDatei).Close savechanges:=True
    ' Excel beenden und Objekt freigeben
    oExcel.Quit
    Set oExcel = Nothing

Die Nachbereitung sorgt dann dafür, daß das "tmp"-Blatt spätestens hier weg ist und das "qry_datensatz"-Blatt dann in den gewünschten Namen "datensatz" umbenannt wird. Diese ganze Namensgebung ist übrigens weiter oben im Source in Konstanten hinterlegt.

Code:

    ' ==================================================
    ' (3)
    ' Datei "datensatz.xls" mit Excel öffnen
    ' ==================================================

    LaunchDocument ExcelLaufwerk & ExcelPfad & ExcelDatei

Zu guter Letzt wird dann die aktualisierte Excel-Mappe dann über die Shell-Verknüpfung in Excel geöffnet. Die Routine basiert dann auf einer Windows-API-Routine, die weiter oben deklariert ist.

Probier mal aus, ob und wie Du das Ding gebrauchen / adaptieren kanst...

Gruß, Michael

 

Entschuldige meinen saloppen Ton: Alter Falter ganz GROß!

Ich habe nur mal kurz rübergeschaut und die Pfade angepasst, aber der erste Eindruck ist hervorragend. Ich hoffe ich bekomme es für meine Zwecke angepasst.

Vielen Dank für die Mühe, die du dir gemacht hast! Ich melde mich nochmal, wenn es für meine Bedürfnisse läuft.