Office Forum
www.Office-Loesung.de
Access :: Excel :: Outlook :: PowerPoint :: Word :: Office :: Wieder Online ---> provisorisches Office Forum <-
Datenexport aus Access in eine Excel-Vorlage
zurück: Fehlerbehandlung in VBA / Access weiter: TempVars-Collection - Stiefkind aus der Makroprogrammierung Unbeantwortete Beiträge anzeigen
Neues Thema eröffnen   Neue Antwort erstellen     Status: Tutorial Facebook-Likes Diese Seite Freunden empfehlen
Zu Browser-Favoriten hinzufügen
Autor Nachricht
astern
Datenmodell-Missionar


Verfasst am:
04. Jul 2009, 14:47
Rufname: Andreas
Wohnort: Rastede

Datenexport aus Access in eine Excel-Vorlage - Datenexport aus Access in eine Excel-Vorlage

Nach oben
       Version: Office 2007

Hallo!
Ich habe kürzlich eine Lösung für den Export von Daten aus Access nach Excel entwickelt, die ich hier einmal vorstellen möchte. Das Besondere daran ist folgendes: Der Export soll nicht einfach in irgendeine Exceldatei erfolgen, sondern in eine nach einer Excel-Vorlage formatierten Excel-Datei – und zwar sollen die Daten aus Access dort in ganz bestimmte Zellen ganz bestimmter Blätter. Da ich dafür keine fertigen VBA-Befehle gefunden habe (wenn es welche gibt, bitte ich um Nachricht!), hier mein „workaround“:

Access erwartet eine .xltm-Datei (=“Excel2007 Vorlage MIT Makros“) in demselben Verzeichnis, in dem auch die Access-Datei liegt (im Beispiel „schiffsdaten.xltm“). Ein Click auf den Button „Datenexport“ im Formular „Schiffe“ bewirkt dann folgendes: Die Vorlagendatei wird von Access per VBA-Code geöffnet. Dadurch wird automatisch eine Datei „schiffsdaten1.xlsm“ erstellt - also eine Excel2007-Datei MIT Makros. Diese Datei wird in demselben Verzeichnis gespeichert, in dem auch die Access-Datei liegt. Anschließend wird von Access eine Abfrage ausgeführt, die die zu exportierenden Daten in eine temporäre Tabelle schreibt. Diese Tabelle wird in die vorbereitete Datei „schiffsdaten1.xlsm“ exportiert. Anschließend wird diese Datei geöffnet.

In der geöffneten Excel-Datei befindet sich ein Blatt mit dem Namen „FE_EXPORT_SCHIFF“. Das ist der Name der temporären Tabelle. Der Export von Access nach Excel erfolgt nämlich immer so, dass ein neues Blatt mit dem Namen der exportierten Tabelle angelegt wird.

Was kann man jetzt in Excel mit den importierten Daten anfangen? Man kann in der Excel-VORLAGEdatei in eine beliebige Zelle eines beliebigen Blattes z.B. hineinschreiben:

=FE_EXPORT_SCHIFF!G2

Das bewirkt, dass ein von Access nach Excel in die Zelle G2 des Blattes FE_EXPORT_SCHIFF exportierter Wert auf ein anderes Blatt übernommen wird. Ich habe das in dem Schiffsbeispiel mit dem Namen, dem Baujahr, dem Heimathafen, dem Land und dem Register gemacht.

Wenn man die Vorlage entwirft, gibt es das Blatt FE_EXPORT_SCHIFF noch gar nicht, denn das wird ja von Access beim Export erst angelegt. Wenn man also in eine Zelle einen Bezug auf das nicht vorhandene Blatt eingibt (z.B. „=FE_EXPORT_SCHIFF!G2“) dann antwortet Excel mit der Fehlermeldung „#BEZUG!“. Das darf Euch aber nicht irritieren – die Fehlermeldung verschwindet in der xlsm-Datei , wenn die Vorlage zum Datenexport aus Access benutzt wird, weil dann ja ein Blatt dieses Namens angelegt wird.

Das klingt alles wahrscheinlich etwas verworren – bitte probiert das Ganze mal an dem Schiffsbeispiel aus! Wenn jemand noch Verbesserungen hat, bin ich (bzw. sind wir alle) für jeden Hinweis dankbar!

Und das ist die VBA-Prozedur für den Datenexport:
Code:
Public Sub datenexport(selectinto As String, tabname As String, _
                       dateiname As String)
' Achtung: Unter Extras / Verweise muss "Microsoft Excel 12.0 Object Library"
' aktiviert sein!
' Aufruf z.B.:
'   sqltext = "SELECT * INTO FE_EXPORT_SCHIFF FROM schiff " & _
'             " WHERE schiff_key=" & Str(Me!schiff_key)
'   Call datenexport(sqltext, "FE_EXPORT_SCHIFF", "schiffsdaten")
    Dim xlVorlage As String
    Dim xlDatei As Object
    Dim xlApp As Object
    Dim xlDateiname As String
    Dim fs As Object
    Dim td As DAO.TableDef
       
    ' Schreiben der Daten in eine temporäre Tabelle
    For Each td In CurrentDb.TableDefs
        If td.Name = tabname Then CurrentDb.Execute "DROP TABLE " & tabname
    Next tdf
    CurrentDb.Execute selectinto
    xlDateiname = Application.CurrentProject.Path & "\" & dateiname & "1.xlsm"
    ' Falls es die Exceldatei schon gibt, wird sie gelöscht
    On Error Resume Next
    Set fs = CreateObject("Scripting.FileSystemObject")
    If fs.FileExists(xlDateiname) Then fs.DeleteFile xlDateiname
    ' Falls die Exportdatei noch geöffnet ist, muss sie erst geschlossen
    ' werden.
    ' Das Schließen habe ich bis jetzt noch nicht per VBA hingekriegt ;-[[
    If Err.Number = 70 Then
        MsgBox "Die Datei " & xlDateiname & " ist noch geöffnet." & vbCrLf & _
               "Bitte erst schließen!"
        Exit Sub
    End If
    ' Erzeugen eine Excel-Objektes
    If Not excel_offen Then
        Set xlApp = CreateObject("Excel.Application")
      Else
        Set xlApp = GetObject(, "Excel.Application")
    End If
    ' Mit dem Öffnen der Vorlage wird eine entspr. Exceldatei erzeugt
    ' und unter dem oben festgelegten Namen gespeichert
    xlVorlage = Application.CurrentProject.Path & "\" & dateiname & ".xltm"
    Set xlDatei = xlApp.Workbooks.Open(xlVorlage)
    xlDatei.SaveAs Application.CurrentProject.Path & "\" & dateiname & _
                   "1.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
    xlDatei.Application.Quit
    Set xlDatei = Nothing
    ' Exportieren der temporären Tabelle in die Exceldatei
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, tabname, _
                              xlDateiname, True
    ' Excel öffnen
    Shell "excel.exe """ & xlDateiname & """", vbMaximizedFocus
End Sub
In der Excel-Vorlage-Datei schiffsdaten.xltm gibt es noch ein weiteres „Überraschungsei“: Wenn man nämlich Verknüpfungen auf Blätter anlegt, die es gar nicht gibt, dann erscheint die Fehlermeldung „#BEZUG!“ (s.o.). Wenn das zuerst nicht vorhandene Blatt dann aber durch den Export von Access angelegt wird, verschwindet die Fehlermeldung nicht automatisch. Die folgende VBA-Prozedur fasst alle Zellen in den ersten 20 Zeilen und Spalten einmal an und erzwingt so eine Aktualisierung der Verknüpfungen. Auch hier gilt: Wenn jemand etwas besseres weiß, sind wir alle für jeden Hinweis dankbar!
Code:
Private Sub Workbook_Open()
    Dim zeile As Long
    Dim spalte As Long
   
    Application.DisplayAlerts = False
    For zeile = 1 To 20
        For spalte = 1 To 20
            Worksheets("Tabelle1").Cells(zeile, spalte).Formula = _
                           Worksheets("Tabelle1").Cells(zeile, spalte).Formula
        Next spalte
    Next zeile
End Sub
Es gibt auch andere Lösungen für die Problematik, z.B. Daten in eine Excel-Vorlage exportieren. Die besondere Stärke meiner Lösung besteht darin, dass man für eine Modifikation der Exceldatei NICHT an den Access-VBA-Code heran muss. Es wird ja die gesamte Tabelle exportiert und dann kann sich jemand, der sich mit Excel, aber nicht mit Access-VBA auskennt, daranmachen, das Layout der Exceldatei nach Belieben zu verändern!
_________________
1. Access-Gebot: Du sollst lange und gründlich über Dein Datenmodell nachdenken!
2. Access-Gebot: Du sollst keine Formulare erstellen ohne gutes Datenmodell!



excel-export-v01.zip
 Beschreibung:

Download
 Dateiname:  excel-export-v01.zip
 Dateigröße:  74.48 KB
 Heruntergeladen:  403 mal

astern
Datenmodell-Missionar


Verfasst am:
20. Aug 2009, 15:14
Rufname: Andreas
Wohnort: Rastede

AW: Datenexport aus Access in eine Excel-Vorlage - AW: Datenexport aus Access in eine Excel-Vorlage

Nach oben
       Version: Office 2007

Hallo!
Ich habe nochmal an meiner Excel-Export-Lösung weiter gearbeitet. Es ging dabei um das Problem: Wie handhabe ich den Fall, dass die zu erstellende Excel-Datei noch geöffnet ist bzw. ganz generell, dass Excel schon läuft. Ich habe aber keine wirklich befriedigende Lösung gefunden. Insbesondere wenn Excel läuft, ohne dass eine Datei geöffnet ist (also ein "leeres Excel"), dann liefert Workbooks.Count Null, obwohl mehrere Excel-Instanzen laufen. Damit habe ich keinen Zugriff auf die noch offene Excel-Datei, um die es mir eigentlich geht.
Ich habe das jetzt "workaroundmäßig" so gelöst, dass ich vor dem Aufruf der Exportfunktion checke, ob Excel läuft und dann den Benutzer auffordere, sich darum zu kümmern, dass alle Excel-Fenster ordnungsgemäß geschlossen werden.
Hier der Code für die Exportfunktion:
Code:
Public Sub datenexport(selectinto As String, tabname As String, _
                       dateiname As String)
' ACHTUNG: Extras/Verweise: "Microsoft Excel 12.0 Object Library" notwendig!)

' Aufruf z.B.:
'   sqltext = "SELECT * INTO FE_EXPORT_SCHIFF FROM schiff " & _
'             " WHERE schiff_key=" & Str(Me!schiff_key)
'   Call datenexport(sqltext, "FE_EXPORT_SCHIFF", "schiffsdaten")
    Dim xlVorlage As String
    Dim xlDatei As Object
    Dim excel As Object
    Dim xlDateiname As String
    Dim fs As Object
    Dim td As DAO.TableDef
   
    ' Schreiben der Daten in eine temporäre Access-Tabelle
    ' (Wenn sie bereits existiert, wird sie gelöscht)
   
    For Each td In CurrentDb.TableDefs
        If td.Name = tabname Then CurrentDb.Execute "DROP TABLE " & tabname
    Next td
    CurrentDb.Execute selectinto
    ' Definition des Namens der Excel-Datei
    ' (Sie liegt standardmäßig im Verzeichnis C:\Temp)
    xlDateiname = "C:\Temp\" & dateiname & "1.xlsm"
    ' Erzeugen eines Excel-Objektes
    If excel_laeuft Then
        Set excel = GetObject(, "Excel.Application")
      Else
        Set excel = CreateObject("Excel.Application")
    End If
    ' Falls es die Exceldatei schon gibt, wird sie gelöscht
    Set fs = CreateObject("Scripting.FileSystemObject")
    If fs.FileExists(xlDateiname) Then fs.DeleteFile xlDateiname
    ' Mit dem Öffnen der Vorlage wird eine entspr. Exceldatei erzeugt
    ' und unter dem oben festgelegten Namen gespeichert
    xlVorlage = Application.CurrentProject.Path & "\" & dateiname & ".xltm"
    Set xlDatei = excel.Workbooks.Open(xlVorlage)
    xlDatei.SaveAs "C:\Temp\" & dateiname & "1.xlsm", _
                   FileFormat:=xlOpenXMLWorkbookMacroEnabled
    xlDatei.Application.Quit
    Set xlDatei = Nothing
    ' Der Inhalt der temporären Access-Tabelle wird in die Excel-Datei
    ' übertragen
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, tabname, _
                              xlDateiname, True
    ' Die Excel-Datei wird angezeigt
    excel.Workbooks.Open xlDateiname, True
    excel.Visible = True
End Sub
Das ist der Aufruf der Funktion:
Code:
Private Sub button_datenexport_Click()
    Dim sqltext As String
   
    If excel_laeuft Then
        MsgBox "Für den Datenexport nach Excel müssen alle bereits " & _
               "laufenden Excelprogramme geschlossen werden." & vbCrLf & _
               "Bitte wechseln Sie zu Excel, speichern Sie dort ggf. " & _
               "Ihre Daten und beenden Sie Excel." & vbCrLf & _
               "Starten Sie den Datenexport anschließend erneut!"
        Exit Sub
    End If
    sqltext = "SELECT * " & _
                "INTO FE_EXPORT_SCHIFF " & _
                "FROM schiff " & _
               "WHERE schiff_key=" & Str(Me!schiff_key)
    Call datenexport(sqltext, "FE_EXPORT_SCHIFF", "schiffsdaten")
End Sub
Und das ist die Hilfsprozedur "excel_laeuft()":
Code:
Public Function excel_laeuft() As Boolean
    Dim excel As Object
   
    On Error Resume Next
    Set excel = GetObject(, "Excel.Application")
    If Err.Number = 0 Then
        excel_laeuft = True
      Else
        excel_laeuft = False
    End If
End Sub
Eine weitere Verbesserung: Die Aktualisierung der Bezüge in der Excel-Datei über den VBA-Code in Workbook_Open() dauert bei großen Bereichen (schon ab 100x100!) ziemlich lange. Durch eine Frage hier im Forum (Bezug auf ein anderes Excelblatt per VBA aktualisieren) habe ich herausbekommen, wie man es besser macht: Man muss einfach die Bezüge auf die importierten Daten so schreiben:
Code:
=INDIREKT("FE_EXPORT_SCHIFF!J2")
Dann erscheint zwar auch zunächst die Fehlermeldung "#BEZUG!" - sie verschwindet aber nach dem Daten-Import!

A*

_________________
1. Access-Gebot: Du sollst lange und gründlich über Dein Datenmodell nachdenken!
2. Access-Gebot: Du sollst keine Formulare erstellen ohne gutes Datenmodell!



excel-export-v02.zip
 Beschreibung:

Download
 Dateiname:  excel-export-v02.zip
 Dateigröße:  51.94 KB
 Heruntergeladen:  411 mal

Willi Wipp
Moderator


Verfasst am:
26. Mai 2013, 10:58
Rufname:
Wohnort: Raum Wiesbaden


Re: Datenexport aus Access in eine Excel-Vorlage - Re: Datenexport aus Access in eine Excel-Vorlage

Nach oben
       Version: Office 2007

Nachfragen zum Thema bitte hier Datenexport aus Access in eine Excel-Vorlage {Nachgefragt} stellen.
_________________
Eine kurze Rueckmeldung waere nett
SL Willi Wipp

(Anleitung fuer das Anhaengen von Dateien: Klicke links auf [www], Gaeste muessen sich dafuer anmelden)
Neues Thema eröffnen   Neue Antwort erstellen Alle Zeiten sind
GMT + 1 Stunde

Diese Seite Freunden empfehlen

Seite 1 von 1
Gehe zu:  
Du kannst Beiträge in dieses Forum schreiben.
Du kannst auf Beiträge in diesem Forum antworten.
Du kannst deine Beiträge in diesem Forum nicht bearbeiten.
Du kannst deine Beiträge in diesem Forum nicht löschen.
Du kannst an Umfragen in diesem Forum nicht mitmachen.
Du kannst Dateien in diesem Forum nicht posten
Du kannst Dateien in diesem Forum herunterladen

Verwandte Themen
Forum / Themen   Antworten   Autor   Aufrufe   Letzter Beitrag 
Keine neuen Beiträge Access Programmierung / VBA: Datenexport aus Access in eine Excel-Vorlage {Nachgefragt} 7 Feeko 549 16. Dez 2013, 13:58
Thompsen00 Datenexport aus Access in eine Excel-Vorlage {Nachgefragt}
Keine neuen Beiträge Access Hilfe: Excel-Vorlage via Access öffnen / funktioniert nur bedingt 1 Oryon 106 02. Jul 2013, 12:29
Bitsqueezer Excel-Vorlage via Access öffnen / funktioniert nur bedingt
Keine neuen Beiträge Access Programmierung / VBA: VBA Datenexport ins Excel INKLUSIVE FORMATIERUNG 0 Eisermann02 298 10. Jun 2013, 07:05
Eisermann02 VBA Datenexport ins Excel INKLUSIVE FORMATIERUNG
Keine neuen Beiträge Access Programmierung / VBA: Datenexport über DoCmd.TransferText Feldtrennzeichen falsch 1 Siljen 334 16. Mai 2013, 09:46
KlausMz Datenexport über DoCmd.TransferText Feldtrennzeichen falsch
Keine neuen Beiträge Access Programmierung / VBA: Export einer Abfrage in Excel-Vorlage 8 Greco 405 26. Feb 2012, 23:50
Greco Export einer Abfrage in Excel-Vorlage
Keine neuen Beiträge Access Programmierung / VBA: Problem mit Datenexport nach Excel 9 joes1609 279 16. Dez 2011, 18:13
joes1609 Problem mit Datenexport nach Excel
Keine neuen Beiträge Access Programmierung / VBA: Problem Datenexport nach excel 6 Gordonsky 590 24. Feb 2011, 07:15
Gordonsky Problem Datenexport nach excel
Keine neuen Beiträge Access Programmierung / VBA: Problem mit datenexport nach excel 2 Gast 479 13. Jul 2010, 08:39
Gast Problem mit datenexport nach excel
Keine neuen Beiträge Access Tabellen & Abfragen: Problem mit Datenexport aus Access 5 webrus 1727 02. Jan 2010, 15:43
steffen0815 Problem mit Datenexport aus Access
Keine neuen Beiträge Access Programmierung / VBA: Datenexport Access - Excel 18 Robertxx 1857 09. Dez 2009, 10:12
Ropbertxx Datenexport Access - Excel
Keine neuen Beiträge Access Programmierung / VBA: Datenexport in txt-Datei mit Zusatzzeile (Information) 2 Azraelevilgod 794 19. Nov 2007, 15:49
Azraelevilgod Datenexport in txt-Datei mit Zusatzzeile (Information)
Keine neuen Beiträge Access Programmierung / VBA: DatenExport (CSV) 6 DieterB 6094 19. Okt 2007, 11:32
SlayerSH DatenExport (CSV)
 

----> Diese Seite Freunden empfehlen <------ Impressum - Besuchen Sie auch: Access Tabellen