Herbers Excel/VBA-Archiv

Pivot Tabelle in normale Tabelle umwandeln

• Pivot Tabelle in normale Tabelle umwandeln von dip vom 29.08.2014 18:20:07
• AW: Pivot Tabelle in normale Tabelle umwandeln - von fcs am 31.08.2014 12:49:13
  • AW: Pivot Tabelle in normale Tabelle umwandeln - von dip am 01.09.2014 10:17:27
    • AW: Pivot Tabelle in normale Tabelle umwandeln - von dip am 01.09.2014 11:27:37
      • AW: Pivot Tabelle in normale Tabelle umwandeln - von fcs am 01.09.2014 12:55:41
        • AW: Pivot Tabelle in normale Tabelle umwandeln - von dip am 02.09.2014 13:03:36
    • AW: Pivot Tabelle in normale Tabelle umwandeln - von fcs am 01.09.2014 12:31:46

Betrifft: Pivot Tabelle in normale Tabelle umwandeln von: dip
Geschrieben am: 29.08.2014 18:20:07

Hallo Allerseits,

Gerne würde ich folgende Problemstellung in das Forum einbringen, in der Hoffnung, jemand kennt die Lösung dazu :-)

Ich habe ein Excel File mit einer Pivot Tabelle. Da der Pivot Tabelle eine grosse Menge an Daten zugrunde liegt inkl. Verbindung zu einer Access Datenbank, möchte ich das Pivot in eine ganz normale Tabelle umwandeln, damit einerseits das Dokument nur die angezeigten Werte enthält und somit das File keine mehrere MB gross ist und andererseis die Verbindung zur Access Datenbank nicht mehr besteht.
Ganz genial wäre es, wenn die Schaltflächen zum erweitern/reduzieren beibehaltet werden können.

Ich stelle mir das zusammen mit einem Makrobutton vor, "Pivot Tabelle exportieren".
Wenn der Benutzer auf diesen Makrobutton klickt, dann wird die Pivot Tabelle in ein neues Excel File als Tabelle kopiert und er kann das File wo er möchte abspeichern.

Wäre dies so möglich? Oder gäbe es andere Möglichkeiten?

Bin für jeden Tipp/Hilfe sehr dankbar!

Beste Grüsse
Patrick

Betrifft: AW: Pivot Tabelle in normale Tabelle umwandeln von: fcs
Geschrieben am: 31.08.2014 12:49:13

Hallo Patrick,

das Kopieren eines Pivotdatenbereichs ist kein Problem.
Erweitern/reduzieren ist jedoch eine Pivot-Funtion, die nicht kopierfähig ist.

Die Dateigröße kannst ggf. schon reduzieren indem du unter den Pivotoptionen im Register "Daten" die Option "Quelldaten mit Satei speichern" deaktivierst.

Gruß
Franz

Makros zum übertragen der Pivot-Tabelle in eine neue Datei

Sub CopyPivotIntoNewFile_1()
    Dim pvTab As PivotTable
    Dim wksZiel As Worksheet
    
    Set pvTab = ActiveSheet.PivotTables(1)
    
    Workbooks.Add Template:=xlWBATWorksheet
    Set wksZiel = ActiveWorkbook.Worksheets(1)
    
    pvTab.TableRange2.EntireColumn.Copy
    With wksZiel.Cells(1, 1)
        .PasteSpecial Paste:=xlPasteFormats
        .PasteSpecial Paste:=xlPasteValues
    End With
    Application.CutCopyMode = False
    Range(pvTab.DataBodyRange.Range("A1").Address).Select
    ActiveWindow.FreezePanes = True
    
End Sub


Sub CopyPivotIntoNewFile_2()
    Dim pvTab As PivotTable
    Dim wbZiel As Workbook, wksZiel As Worksheet
    
    ActiveSheet.Copy
    Set wbZiel = ActiveWorkbook
    Set wksZiel = wbZiel.Worksheets(1)
    Set pvTab = wksZiel.PivotTables(1)
    If wbZiel.Connections.Count > 0 Then
        wbZiel.Connections(1).Delete
    End If
    'bis hier sieht die Tabelle optisch noch wie die Pivot-Tabelle aus, _
            sie hat aber keine Dazenquelle mehr und Pivot-Befehle funktionieren nicht mehr
Exit Sub 'Diese Zeile löschen, wenn Pivotformatierungen entfernt werden sollen
'die folgenden Zeilen entdernen dann pivotspezifische Formate
    wksZiel.UsedRange.Copy
    wksZiel.UsedRange.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End Sub

Betrifft: AW: Pivot Tabelle in normale Tabelle umwandeln von: dip
Geschrieben am: 01.09.2014 10:17:27

Guten Tag Franz,

Herzlichen Dank für die Makros und den Tipp!

Wie könnte man den Befehl "Quelldaten mit Datei speichern deaktivieren" in das Makro Nr. 2 einbinden?
Es ist zu beachten, dass pro Sheet mehrere Pivots vorhanden sein könnten...

Danke und Beste Grüsse
Patrick

Betrifft: AW: Pivot Tabelle in normale Tabelle umwandeln von: dip
Geschrieben am: 01.09.2014 11:27:37

... und vorhandene Verknüpfungen auch alle entfernt werden?...

Beste Grüsse
Patrick

Betrifft: AW: Pivot Tabelle in normale Tabelle umwandeln von: fcs
Geschrieben am: 01.09.2014 12:55:41

Hallo Patrick,

und hier dann mit Entfernen der Verknüpfungen

Gruß
Franz

Sub CopyPivotIntoNewFile_2()
    Dim pvTab As PivotTable
    Dim wbZiel As Workbook, wksziel As Worksheet
    Dim varAuswahl As Variant, varElement As Variant
    
    ActiveSheet.Copy
    Set wbZiel = ActiveWorkbook
    Set wksziel = wbZiel.Worksheets(1)
    
    'Pivottabellen ohne Quelldaten speichern
    For Each pvTab In wksziel.PivotTables
      pvTab.SaveData = False
    Next
    
    'alle Datenverbindungen löschen
    Do Until wbZiel.Connections.Count = 0
        wbZiel.Connections(wbZiel.Connections.Count).Delete
    Loop
    
    'alle Excelverknüpfungen löschen
    If Not IsEmpty(wbZiel.LinkSources(Type:=xlExcelLinks)) Then
      For Each varAuswahl In wbZiel.LinkSources(Type:=xlExcelLinks)
        wbZiel.BreakLink Name:=varAuswahl, Type:=xlLinkTypeExcelLinks
      Next
    End If
    
    'alle OLE-Links löschen
    If Not IsEmpty(wbZiel.LinkSources(Type:=xlOLELinks)) Then
      For Each varAuswahl In wbZiel.LinkSources(Type:=xlOLELinks)
        wbZiel.BreakLink Name:=varAuswahl, Type:=xlLinkTypeOLELinks
      Next
    End If
    varAuswahl = Application.Dialogs(xlDialogSaveAs).Show
    'bis hier sieht die Tabelle optisch noch wie die Pivot-Tabelle aus, _
            sie hat aber keine Dazenquelle mehr und Pivot-Befehle funktionieren nicht mehr
Exit Sub 'Diese Zeile löschen, wenn Pivotformatierungen entfernt werden sollen
'die folgenden Zeilen entfernen dann pivotspezifische Formate
    wksziel.UsedRange.Copy
    wksziel.UsedRange.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End Sub

Betrifft: AW: Pivot Tabelle in normale Tabelle umwandeln von: dip
Geschrieben am: 02.09.2014 13:03:36

Guten Tag Franz,

Vielen herzlichen Dank, dein Makro funktioniert genau wie es soll!
Damit hast Du mir sehr weitergeholfen in meiner Arbeit :-)

Nochmals Dankeschön für deine Hilfe und wünsche Dir einen schönen Tag!

Beste Grüsse
Patrick

Betrifft: AW: Pivot Tabelle in normale Tabelle umwandeln von: fcs
Geschrieben am: 01.09.2014 12:31:46

Hallo Patrick,

Wie könnte man den Befehl "Quelldaten mit Datei speichern deaktivieren" in das Makro Nr. 2 einbinden?
Das müssste dann vor dem Löschen der Daten-Verbindung passieren, dann danach gibt es keine Quelldaten mehr bzw. der Pivto-Bericht ist bis auf ein parr Formatierungsreste hinüber.
Ich hab aber keine Ahnung, ob da im Hintergund noch irgendwelche großen Daten in der Datei rumliegen. Ich hab das nur mit einer Klitzekleinen Datenbank (3 Datensätze mit 7 Feldern) gettestet.

Es ist zu beachten, dass pro Sheet mehrere Pivots vorhanden sein könnten...
Dann muss man ggf. in einer Schleife mehrere Datenverbindungen kappen.

Gruß
Franz

Sub CopyPivotIntoNewFile_2()
    Dim pvTab As PivotTable
    Dim wbZiel As Workbook, wksziel As Worksheet
    Dim varAuswahl As Variant
    
    ActiveSheet.Copy
    Set wbZiel = ActiveWorkbook
    Set wksziel = wbZiel.Worksheets(1)
    
    For Each pvTab In wksziel.PivotTables
      pvTab.SaveData = False
    Next
    
    
    Do Until wbZiel.Connections.Count = 0
        wbZiel.Connections(wbZiel.Connections.Count).Delete
    Loop
    
    varAuswahl = Application.Dialogs(xlDialogSaveAs).Show
    'bis hier sieht die Tabelle optisch noch wie die Pivot-Tabelle aus, _
            sie hat aber keine Dazenquelle mehr und Pivot-Befehle funktionieren nicht mehr
Exit Sub 'Diese Zeile löschen, wenn Pivotformatierungen entfernt werden sollen
'die folgenden Zeilen entfernen dann pivotspezifische Formate
    wksziel.UsedRange.Copy
    wksziel.UsedRange.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End Sub

Beiträge aus den Excel-Beispielen zum Thema "Pivot Tabelle in normale Tabelle umwandeln"