none
Access Abfrageergebnis nach Excel Pivot übertragen RRS feed

  • Frage

  • Hallo,

    ich muss das Ergebnis zweier Access Abfragen in eine Excel Datei übertragen. Diese Daten sollen in der Excel Datei in zwei Pivot Tabellen dargestellt werden.

    Ich habe es mal mit einer Template Datei versucht, die ich von Access heraus aktualisieren wollte (appExcel.RefreshAll).
    Das funktioniert aber nicht, da ich bei der Ausführung die Fehlermeldung bekomme, das Access gesperrt ist.

    Eine andere Idee wäre die beiden Datenbereiche in jeweils eine eigene Mappe in der Excel Datei zu exportieren.

    Auf den Daten dieser Mappen müssten dann die beiden Pivot Tabellen beruhen.
    Die Datenmengen sind auch nicht besonders groß.
    Den Aufwand betreibe ich, weil ich für jeden Monat eine eigene Datei benötige, die nicht versehentlich durch drücken des Refresh Buttons mit aktuellerten Daten überschrieben werden dürfen.

    Hat jemand eine Idee, wie ich da weiter komme?

    Gruß

    cheapy

    Donnerstag, 29. Dezember 2016 14:16

Antworten

  • Ich habe es jetzt anders gelöst.

    Für den Datenbereich habe ich eine Tabelle angelegt, was den Vorteil hat das die Pivot immer den richtigen Datenbereich im Zugriff hat, wenn sich die Zeilenzahl verändert.

    Der Code dazu ist folgender:

    Private Sub ExportExcel(ByVal FilePathName As String, ByVal ExcelSheet As String, ByValstrSQL As String)
    Dim xlApp           As Object ' Excel.Application
    Dim xlBook          As Object ' Excel.Workbook
    Dim xlSheet         As Object ' Excel.Worksheet
    Dim rst             As ADODB.Recordset
    Dim conn            As ADODB.Connection
    Dim i               As Integer
    
        Set rst = New ADODB.Recordset
        Set conn = CurrentProject.Connection
    
        With rst
            .Open strSQL, conn, adOpenKeyset, adLockOptimistic
            If Not .EOF Then
                Set xlApp = CreateObject("Excel.Application")
                xlApp.Visible = False
                Set xlBook = xlApp.Workbooks.Open(FilePathName)
                Set xlSheet = xlBook.Sheets(ExcelSheet)
    '            xlSheet.Cells.ClearContents
    
                For i = 1 To rst.Fields.Count
                    xlSheet.Cells(1, i) = rst.Fields(i - 1).Name
                Next i
                xlSheet.Range("A2").CopyFromRecordset rst
                xlBook.RefreshAll
                rst.Close
                xlBook.Save
                xlBook.Close
                xlApp.Quit
    
            End If
        End With
        Set rst = Nothing
        Set xlSheet = Nothing
        Set xlBook = Nothing
        Set xlApp = Nothing
        
    End Sub

    Voraussetzung ist allerdings, dass diese Tabelle schon in der Excel Datei vorhanden ist.
    Ich habe sie deshalb nur mit einer Zeile, die die Feldnamen enthält angelegt.
    Das klappt so weit. Allerdings würde ich gerne weitere Zeilen in dieser Tabelle löschen, falls außer der Zeile 1 noch weitere existieren.

    Dazu habe ich aber noch keine Lösung gefunden.
    Kann mir da jemand helfen?

    Der Versuch mit

    xlSheet.Cells.ClearContents

    zerstört die Tabelle, ist also keine Lösung.
    Daum ist die Zeile im vorstehenden Code auch auskommentiert.

    Ach, falls sich jemand an dem übergebenen SQP-String stößt, ich bearbeite damit mehrere Tabellen und mehrere Abfragen mit variierenden Filtern und das ist dann so aus meiner Sicht der einfachste Weg, weil ich dann nicht für jeder Variante eine eigene Abfrage erstellen muss. Da die Erghebnismenge der Abfragen jeweils weniger als 1000 Datensätze liefern, sollte es auch kein Performanceproblem geben.



    Dienstag, 3. Januar 2017 09:10

Alle Antworten

  • Hallo,

    ich habe es jetzt hin bekommen, dass ich die Daten in eine vorhandene Excel Datei unter Angabe eines Tabellenblattnames übertragen kann allerdings werden die Daten in ein neues Tabellenblatt mit einer  an den vorgegebenen Namen angehängten 1 gespeichert, sofern das angegebene Tabellenblatt schon vorhanden ist.

    Also die Dasten wurden in das Tabellenblatt "Ausgabedaten1" geschrieben, obwohl ich "Ausgabedaten" angegeben habe.
    Wenn ich nun den Inhalt des Tabellenblattes "Ausgabedaten1" lösche und die ausgabe erneut starte, sind wieder Daten in "Ausgabedaten1".

    Wie kann das sein?

    Wie kann ich die Daten in ein vorhandenen Tabellenblatt schreiben?

    Ohne das wird ja meine Pivot nicht funktionieren.

    Gruß

    cheapy


    Donnerstag, 29. Dezember 2016 15:21
  • Am 29.12.2016 schrieb Cheaptrick_la:

    Also die Dasten wurden in das Tabellenblatt "Ausgabedaten1" geschrieben, obwohl ich "Ausgabedaten" angegeben habe.
    Wenn ich nun den Inhalt des Tabellenblattes "Ausgabedaten1" lösche und die ausgabe erneut starte, sind wieder Daten in "Ausgabedaten1".

    Probier es doch mal andersrum, Exceldatei öffnen, Tabellenblatt
    "Ausgabedaten" löschen, speichern und Exceldatei schließen. Erst jetzt
    wieder in die Datei exportieren. Funktioniert das?

    Servus
    Winfried


    WSUS Package Publisher: http://wsuspackagepublisher.codeplex.com/
    HowTos zum WSUS Package Publisher http://www.wsus.de/wpp
    GPO's: http://www.gruppenrichtlinien.de
    NNTP-Bridge für MS-Foren: http://communitybridge.codeplex.com/

    Donnerstag, 29. Dezember 2016 17:46
  • Grund für diese Aktion ist doch das eine Pivot Tabelle auf diesem Tabellenblatt "Ausgabedaten" basieren soll.

    Was passiert mit der Pivot, wenn ich das Tabellenblatt aus der Excel-Datei entferne?

    Donnerstag, 29. Dezember 2016 18:16
  • Am 29.12.2016 schrieb Cheaptrick_la:

    Grund für diese Aktion ist doch das eine Pivot Tabelle auf diesem Tabellenblatt "Ausgabedaten" basieren soll.

    Was passiert mit der Pivot, wenn ich das Tabellenblatt aus der Excel-Datei entferne?

    Weiß ich nicht, probier es aus, dann weißt Du es.

    Servus
    Winfried


    WSUS Package Publisher: http://wsuspackagepublisher.codeplex.com/
    HowTos zum WSUS Package Publisher http://www.wsus.de/wpp
    GPO's: http://www.gruppenrichtlinien.de
    NNTP-Bridge für MS-Foren: http://communitybridge.codeplex.com/

    Freitag, 30. Dezember 2016 17:08
  • Ich habe es jetzt anders gelöst.

    Für den Datenbereich habe ich eine Tabelle angelegt, was den Vorteil hat das die Pivot immer den richtigen Datenbereich im Zugriff hat, wenn sich die Zeilenzahl verändert.

    Der Code dazu ist folgender:

    Private Sub ExportExcel(ByVal FilePathName As String, ByVal ExcelSheet As String, ByValstrSQL As String)
    Dim xlApp           As Object ' Excel.Application
    Dim xlBook          As Object ' Excel.Workbook
    Dim xlSheet         As Object ' Excel.Worksheet
    Dim rst             As ADODB.Recordset
    Dim conn            As ADODB.Connection
    Dim i               As Integer
    
        Set rst = New ADODB.Recordset
        Set conn = CurrentProject.Connection
    
        With rst
            .Open strSQL, conn, adOpenKeyset, adLockOptimistic
            If Not .EOF Then
                Set xlApp = CreateObject("Excel.Application")
                xlApp.Visible = False
                Set xlBook = xlApp.Workbooks.Open(FilePathName)
                Set xlSheet = xlBook.Sheets(ExcelSheet)
    '            xlSheet.Cells.ClearContents
    
                For i = 1 To rst.Fields.Count
                    xlSheet.Cells(1, i) = rst.Fields(i - 1).Name
                Next i
                xlSheet.Range("A2").CopyFromRecordset rst
                xlBook.RefreshAll
                rst.Close
                xlBook.Save
                xlBook.Close
                xlApp.Quit
    
            End If
        End With
        Set rst = Nothing
        Set xlSheet = Nothing
        Set xlBook = Nothing
        Set xlApp = Nothing
        
    End Sub

    Voraussetzung ist allerdings, dass diese Tabelle schon in der Excel Datei vorhanden ist.
    Ich habe sie deshalb nur mit einer Zeile, die die Feldnamen enthält angelegt.
    Das klappt so weit. Allerdings würde ich gerne weitere Zeilen in dieser Tabelle löschen, falls außer der Zeile 1 noch weitere existieren.

    Dazu habe ich aber noch keine Lösung gefunden.
    Kann mir da jemand helfen?

    Der Versuch mit

    xlSheet.Cells.ClearContents

    zerstört die Tabelle, ist also keine Lösung.
    Daum ist die Zeile im vorstehenden Code auch auskommentiert.

    Ach, falls sich jemand an dem übergebenen SQP-String stößt, ich bearbeite damit mehrere Tabellen und mehrere Abfragen mit variierenden Filtern und das ist dann so aus meiner Sicht der einfachste Weg, weil ich dann nicht für jeder Variante eine eigene Abfrage erstellen muss. Da die Erghebnismenge der Abfragen jeweils weniger als 1000 Datensätze liefern, sollte es auch kein Performanceproblem geben.



    Dienstag, 3. Januar 2017 09:10
  • Am 03.01.2017 schrieb Cheaptrick_la:

    Ich habe es jetzt anders gelöst.

    Für den Datenbereich habe ich eine Tabelle angelegt, was den Vorteil hat, das die Pivot immer den richtigen Datenbereich im Zugriff hat, wenn sich die Zeilenzahl verändert.

    Vielen Dank für deine Rückmeldung. ;)

    Voraussetzung ist allerdings, dass diese Tabelle schon in der Excel Datei vorhanden ist.
    Ich habe sie deshalb nur mit einer Zeile, die die Feldnamen enthält angelegt.
    Das klappt so weit. Allerdings würde ich gerne weitere Zeilen in dieser Tabelle löschen, falls außer der Zeile 1 noch weitere existieren.

    Ist denn die erste Zeile immer in Zeile 1? Wenn ja, könntest Du doch
    ab Zeile 2 alles löschen, was Inhalt hat.

    Servus
    Winfried


    WSUS Package Publisher: http://wsuspackagepublisher.codeplex.com/
    HowTos zum WSUS Package Publisher http://www.wsus.de/wpp
    GPO's: http://www.gruppenrichtlinien.de
    NNTP-Bridge für MS-Foren: http://communitybridge.codeplex.com/

    Dienstag, 3. Januar 2017 17:31