Office Forum
www.Office-Loesung.de
Access :: Excel :: Outlook :: PowerPoint :: Word :: Office :: Wieder Online ---> provisorisches Office Forum <-
Datensatz an Exceltabelle anhängen
zurück: automatisches Frontendupdate weiter: Transponieren von Tabellen und Abfragen 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
steffen0815
VBA-Programmierer


Verfasst am:
16. Apr 2006, 20:32
Rufname:

Datensatz an  Exceltabelle anhängen - Datensatz an Exceltabelle anhängen

Nach oben
       

Hallo Leute,
mehrfach blieben im Forum Fragen nach einem Export von einzelnen Werten, oder eines Datensatzes nach Excel unbeantwortet.
Vor allem sollten bestehende Exceltabellen fortgeschrieben werden.

Auf die "CopyFromRecordset-Methode" wurde u.a. verzichtet, weil u.U. damit Memofelder abgeschnitten werden.
Ebenso ist eine Anfügeabfrage in der Art
Code:
"INSERT INTO [Sheet1$] IN '' [Excel 8.0;Database=" & ...
nicht geeignet Memofelder sauber zu übergeben.

Im folgenden Code kann der aktuelle Datensatz aus einem Formular oder beliebige Einzelwerte in eine Exceltabelle angehängt werden.

Dabei ist es egal ob die Exceldatei bzw. die Tabelle existieren.
Der Anwender hat außerdem die Wahl, ob die Exceldatei am Bildschirm sichtbar bleiben soll.

Es werden keinerlei Formatierungen vorgenommen, da es meist darum geht in bestehende Tabellen zu schreiben.

Anwendungsbeispiele:
1) beliebige Werte
Code:
Sub StartExport()
    ' Exportiert die Werte : EinenText; 123; Aktuelles Datum; Aktuelle Uhrzeit
    '            nach      : c:\mytestxy.xls
    '            in Tabelle: Tabelle11
    '            true      : Excel bleibt offen
    OLEExcelExport "c:\mytestxy.xls", "Tabelle11", True, "EinenText", 123, _
                   33, Date, Time
End Sub

2) Aktueller Datensatz eines Formulars
Code:
Private Sub Bef2Excel_Click()
    ' exportiert den aktuellen Datensatz nach Excel
    ' die Exceldatei wird nach dem Export wieder geschlossen
    Const ExcelDatNam = "c:\mytestxy.xls"
    Const ExcelTabNam = "Tabelle11"
    Dim Werteliste(), i As Integer

    ' Liste mit allen Werten des aktuellen Datensatzes füllen
    ReDim Werteliste(Me.RecordsetClone.Fields.Count - 1)
    For i = 0 To Me.RecordsetClone.Fields.Count - 1
        Werteliste(i) = Me(RecordsetClone.Fields(i).Name)
    Next
    ' Datenübergabe nach Excel
    OLEExcelExport ExcelDatNam, ExcelTabNam, True, Werteliste
End Sub

3) Exportiert 2 Zeilen
Code:
Sub StartExportZweiZeilen()
    ' Exportiert Spaltenüberschriften und
    '            die Werte : EinenText; 123; Aktuelles Datum; Aktuelle Uhrzeit
    '            nach      : c:\mytestxy.xls
    '            in Tabelle: Tabelle11
    '            false      : Excel bleibt offen
    OLEExcelExport "c:\mytestxy.xls", "Tabelle12", True, "AktText", _
                   "AktZahl1", "AktZahl2", "Datum", "Uhrzeit"
    OLEExcelExport "c:\mytestxy.xls", "Tabelle12", False, "EinenText", 123, _
                   33, Date, Time
End Sub

Folgender Code (ab Access 97) sollte in ein allgemeines Modul (z.B. modOLEExcelExport):
Code:
Option Compare Database
Option Explicit

Function OLEExcelExport(ExcelFullDatNam As String, ExcelTabNam As String, _
                        ExcelOffenLassen As Boolean, _
                        ParamArray Werteliste()) As Boolean
' ============
' Ab Access 97
' ============
' Standard Excel-Export kann nur neue Tabellen erzeugen und komplette Abfragen
' exportieren
'
' Deshalb eigene Funktion:
' - Exportiert eine Datenzeile (bestehende) (offene) Exceltabelle
' - Daten werden an die letzte freie Zeile angehängt
' - Datei, Tabelle können bei Bedarf erzeugt werden
' - ist Excelanwendung kann offen gehalten werden
' ParamArray: übergeben werden entweder einzelne Werte oder ein Wertefeld
    Const xlUp = -4162 ' so spart man den Verweis auf MS Excel
    Dim WorkBookNam As String
    Dim TabExist As Boolean, ExcelIsOpen As Boolean, WorkbookIsOpen As Boolean
    Dim xlApp As Object ' Excel.Application
    Dim xlBook As Object  'Excel.Workbook
    Dim xlSheet As Object ' Excel.Worksheet
    Dim ErsteFreieZeile As Long, i As Integer, AktSpalte As Integer
    Dim j As Integer
   
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If xlApp Is Nothing Then
        Set xlApp = CreateObject("Excel.Application")
      Else
        ExcelIsOpen = True
    End If
    On Error GoTo 0
    ' 1. Exceldatei evtl. öffnen/anlegen
    ' evtl ".xls" anhängen
    If Len(ExcelFullDatNam) < 5 Then ExcelFullDatNam = ExcelFullDatNam & ".xls"
    If Mid(ExcelFullDatNam, Len(ExcelFullDatNam) - 3, 4) <> ".xls" Then _
                                     ExcelFullDatNam = ExcelFullDatNam & ".xls"

    If Len(Dir(ExcelFullDatNam)) > 0 Then
        WorkBookNam = NurDatNam(ExcelFullDatNam)
        For Each xlBook In xlApp.Workbooks
            If xlBook.Name = WorkBookNam Then WorkbookIsOpen = True
        Next
        If Not WorkbookIsOpen Then
            Set xlBook = xlApp.Workbooks.Open(ExcelFullDatNam)
          Else
            Set xlBook = xlApp.Workbooks(WorkBookNam)
        End If
      Else
        Set xlBook = xlApp.Workbooks.Add
        xlBook.SaveAs ExcelFullDatNam
    End If
    ' 2. Tabellenblatt zuweisen/anlegen
    For Each xlSheet In xlBook.Worksheets
        If xlSheet.Name = ExcelTabNam Then TabExist = True
    Next
    If TabExist Then
        Set xlSheet = xlBook.Worksheets(ExcelTabNam)
      Else
        Set xlSheet = xlBook.Worksheets.Add
        xlSheet.Name = ExcelTabNam
    End If
    With xlSheet
    ' erste freie Zeile (Spalte A leer) ermitteln
        .Activate ' falls Exceldatei geöffnet war
        ErsteFreieZeile = .Range("A65536").End(xlUp).row + 1
        If ErsteFreieZeile = 2 Then
            If .Cells(1, 1) = "" Then ErsteFreieZeile = 1
        End If
        For i = 0 To UBound(Werteliste)
            If IsArray(Werteliste(i)) Then
                For j = 0 To UBound(Werteliste(i))
                    AktSpalte = AktSpalte + 1
                    .Cells(ErsteFreieZeile, AktSpalte) = Werteliste(i)(j)
                Next j
              Else
                AktSpalte = AktSpalte + 1
                .Cells(ErsteFreieZeile, AktSpalte) = Werteliste(i)
            End If
        Next i
    End With
    If ExcelOffenLassen = True Then
        xlApp.Visible = True
      Else
         xlBook.Save
         xlBook.Close
         ' falls Excel geöffnet wurde jetzt schließen
         If Not ExcelIsOpen Then xlApp.Application.Quit
    End If
    Set xlSheet = Nothing
    Set xlBook = Nothing
    Set xlApp = Nothing
    OLEExcelExport = True
End Function

Function NurDatNam(FullDatNam As String) As String
' Ermittlung des Dateinamens aus dem Namen incl. Pfad
' Ab AC 2000 ist dies einfacher mit InstrRev
    Dim i As Integer, Pos As Integer
   
    For i = 1 To Len(FullDatNam)
        If Mid(FullDatNam, i, 1) = "\" Then Pos = i
    Next
    NurDatNam = Mid(FullDatNam, Pos + 1)
End Function

Ich stehe natürlich jeder Kritik offen gebenüber.

Interessant wäre auch die Frage, ob es Sinn macht das ganze für eine größere Anzahl Datensätze zu erweitern (ich bin der Meinung "nein"), dann müsste die Excelsteuerung (Sichtbarkeit) optimiert werden außerdem wäre "CopyFromRecordset" zum schreiben sinnvoll.

[edit 30.04.06]
Für den Fall, dass die Daten nicht in einer Zeile sondern in einer Excelspalte stehen sollten, wird in
AW: Access-Datensatz nach Excelvorlage exportieren ein Beispiel für das transponieren in der fertigen Exceldatei gegeben.

Prozedur kontrolliert jetzt, ob am Exceldateiname ".xls" angehängt wurde, wenn nicht wird dies kommentarlos getan.[/edit]

_________________
Gruß Steffen


Zuletzt bearbeitet von steffen0815 am 11. Okt 2006, 11:09, insgesamt 4-mal bearbeitet
Willi Wipp
Moderator


Verfasst am:
19. Apr 2006, 18:05
Rufname:
Wohnort: Raum Wiesbaden


Re: Datensatz an  Exceltabelle anhängen - Re: Datensatz an Exceltabelle anhängen

Nach oben
       

Hi steffen0815, schoene Loesung

{Dieser Beitrag nimmt das Thema aus den unbeantworteten Themen heraus}

Nachfragen zum Thema bitte hier Datensatz an Exceltabelle anhängen {Nachgefragt} stellen.
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 Tabellen & Abfragen: Excel-Tabelle in Access darstellen 2 hummi 690 14. Sep 2005, 09:04
fantasy Excel-Tabelle in Access darstellen
Keine neuen Beiträge Access Tabellen & Abfragen: unlöschbaren Datensatz erstellen 1 Vincent_Vega 899 15. Aug 2005, 21:45
stpimi unlöschbaren Datensatz erstellen
Keine neuen Beiträge Access Tabellen & Abfragen: (A03) DS in einer Excel-Tabelle mit Löschabfrage löschen 6 PrinceArthas 794 08. Aug 2005, 00:13
PrinceArthas (A03) DS in einer Excel-Tabelle mit Löschabfrage löschen
Keine neuen Beiträge Access Tabellen & Abfragen: Listenfeld, automatisch erster Datensatz markieren 1 Gast 2958 21. Jun 2005, 11:54
rita2008 Listenfeld, automatisch erster Datensatz markieren
Keine neuen Beiträge Access Tabellen & Abfragen: Daten aus einer Excel-Tabelle in eine Access-Tabelle einfüge 14 DerLehrling 1888 15. Jun 2005, 01:32
tobes Daten aus einer Excel-Tabelle in eine Access-Tabelle einfüge
Keine neuen Beiträge Access Tabellen & Abfragen: Datensatz über Formular suchen 3 Sandnet 2166 10. Jun 2005, 10:52
Dalmatinchen Datensatz über Formular suchen
Keine neuen Beiträge Access Tabellen & Abfragen: Reduzierung mehrerer Datensätze auf einen Datensatz 2 Pulldown 819 03. Jun 2005, 09:32
Pulldown Reduzierung mehrerer Datensätze auf einen Datensatz
Keine neuen Beiträge Access Tabellen & Abfragen: Excel-Tabelle in bestehende DB Importiren 10 MartinB 780 30. Mai 2005, 17:05
Gast Excel-Tabelle in bestehende DB Importiren
Keine neuen Beiträge Access Tabellen & Abfragen: Datensatz automatisch kopieren??? 0 Edelschnitt 701 11. Mai 2005, 19:41
Edelschnitt Datensatz automatisch kopieren???
Keine neuen Beiträge Access Tabellen & Abfragen: Anfügeabfrage soll nur einen Datensatz verschieben 2 Quamsman 1025 08. März 2005, 15:23
Quamsman Anfügeabfrage soll nur einen Datensatz verschieben
Keine neuen Beiträge Access Tabellen & Abfragen: Aus 1 Datensatz mit 10 Infos mache 10 Datensätze à 1 Info 2 Steve 914 14. Jan 2005, 00:43
Steve Aus 1 Datensatz mit 10 Infos mache 10 Datensätze à 1 Info
Keine neuen Beiträge Access Tabellen & Abfragen: CurrentUser() in neuen Datensatz eintragen 2 mafke 924 16. Aug 2004, 15:49
mafke CurrentUser() in neuen Datensatz eintragen
 

----> Diese Seite Freunden empfehlen <------ Impressum - Besuchen Sie auch: HTML Editoren Forum