Excel VBA - Kopieren einer gesamten Datei ohne Formeln, Verknüpfungen und Codes trotz PivotTables

  • Excel

Es gibt 6 Antworten in diesem Thema. Der letzte Beitrag () ist von INOPIAE.

    Excel VBA - Kopieren einer gesamten Datei ohne Formeln, Verknüpfungen und Codes trotz PivotTables

    Liebe VBA Profis,

    ich habe bereits eine Lösung von petaod gefunden. Leider kann ich diese nicht zu 100% verwenden.

    Es werden alle Arbeitsblätter kopiert und die Formeln/Verlinkungen/Makrofelder sind gelöscht, ABER...

    ...ich bekomme leider immer eine Fehlermeldung (Laufzeitfehler 1004 - Nullwert kann nicht als Element oder Feldname in einen PivotTable-Bericht aufgenommen werden.). Die Pivottabellen sind fast auf jedem Arbeitsblatt, bekommen Ihre daten aber nur von einem Datenblatt, in welchem keine Nullwerte zu sehen sind.
    Meinem Newbie-Verständnis nach, sollte es reichen, wenn das kopieren der Tabellenblätter nur mit Werten erfolgt.

    Quasi: Wenn dieses ws.copy (Zeile 14) nur die Werte anstelle der Pivot-Tabelle kopieren würde.
    For Each ws In ThisWorkbook.Worksheets
    ws.Copy After:=wb.Sheets(wb.Sheets.Count)

    Der Code von petaod inkl. kleinen Anpassungen durch mich:

    Quellcode

    1. Sub Jahreswechsel()
    2. 'neuer Dateiname
    3. Dim Dateiname As String
    4. Dateiname = "Q-Meldungen_" & Worksheets("Beschreibung").Range("XFD2").Value
    5. 'neuer Speicherort
    6. Dim Speicherort As String
    7. Speicherort = "hier steht sonst der Speicherpfad"
    10. Dim wb As Workbook, ws As Worksheet, sh As Shape
    11. Set wb = Workbooks.Add(xlWBATWorksheet)
    12. wb.Sheets(1).Name = "deleteMe"
    14. For Each ws In ThisWorkbook.Worksheets
    15. ws.Copy After:=wb.Sheets(wb.Sheets.Count)
    17. Next
    19. For Each Link In wb.LinkSources(xlLinkTypeExcelLinks)
    20. wb.BreakLink Name:=Link, Type:=xlLinkTypeExcelLinks
    21. Next
    23. For Each ws In wb.Worksheets
    24. ws.UsedRange.Formula = ws.UsedRange.Value
    26. For Each sh In ws.Shapes
    27. sh.Delete
    29. Next
    31. Next
    33. Do While wb.Connections.Count > 0
    34. wb.Connections.Item(1).Delete
    36. Loop
    38. Application.DisplayAlerts = False
    39. wb.Sheets("deleteMe").Delete
    40. wb.SaveAs _
    41. Filename:=Speicherort & Worksheets("Beschreibung").Range("XFD2") & "\" & Dateiname & "_" & Format(Date, "yyyymmdd") & ".xlsx"
    42. Application.DisplayAlerts = True
    43. wb.Close False
    45. End Sub


    Ich hoffe, dass ich alle notwendigen Infos gegeben habe und mir Jemand helfen kann, das Problem zu lösen. Bei Rückfragen fragt gern vorher, bevor Ihr euch die Mühe macht etwas auszudenken. Ich DENKE alle notwendigen Infos gegeben zu haben, mir fehlt aber auch einfach das Verständnis für derartige Codes...
    Ich hoffe auch, dass keine Beispieldatei benötigt wird: das wir sonst relativ aufwändig...

    Beste Grüße
    Luca

    Dieser Beitrag wurde bereits 3 mal editiert, zuletzt von „Luca441“ ()

    Hm VBA habe ich schon länger nicht mehr geschrieben und als ich es geschrieben habe, hatte ich gar keine Ahnung was ich da tue.
    Ob man bei deinem Code also eventuell einen kleinen Handgriff hinzufügen kann und dann läufts, kann ich nicht sagen.

    Ich greife aber heute auch noch aus VB auf Excel und Exceldaten zu. Dabei sehe ich immer zu, dass ich mir einfach nur das, was ich brauche erstmal in sauberen verwalteten Code sauge, bevor ich irgendwas damit mache und sei es stumpf ein Object-Array aus primitiven Datentypen.
    So ein Object Array kannst du aus jedem WS rausholen. Und dann wiederum in ein anderes WS packen. Das ist an sich das Kopieren der reinen Werte.

    Danke für die zügige Antwort!

    An einen Code mit Array habe ich auch schon gedacht, leider habe ich damit noch nie gearbeitet und auch nichts gefunden, was bisher so gut funktioniert, wie der Code von petaod.

    Ich komme zwar Stück für Stück dahinter, was die Codes machen, aber ohne die unzähligen Videos auf Youtube und Foren wie dieses wäre es unvorstellbar, dass ich meine Makros zum laufen gebracht hätte...

    Deswegen musste ich es jetzt doch hier im Forum versuchen, da mein Latein schon längst am Ende ist. Also wenn jemand eine Idee für einen Code mit Array hat, dann sehr gern her damit :D

    Wenn möglich wäre ich aber froh, wenn der Makro auch alle Formatierungen übernimmt, außer eben die Pivot Tabellen. Es geht nur um die Archivierung einer Datensammlung über ein Jahr und das ich die selbe Liste "zurücksetze" und für das nächste Jahr verwenden kann. Die Daten bleiben dann also identisch.

    Beste Grüße

    Wo kommen denn die Daten für die Pivottable her?
    Schau Dir mal den Datenbereich der Pivottable an. Ist der nach Deinem Löschen leer oder verweist der auf einen Pfad den es nicht mehr gibt?
    NB. Es ist doch schön, wenn man lesbare Namen vergibt. Siehe auch [VB.NET] Beispiele für guten und schlechten Code (Stil).

    Luca441 schrieb:

    leider habe ich damit noch nie gearbeitet und auch nichts gefunden
    Ein Array kriegst du so:
    Dim arr = Range("A1:A2")

    Andersrum ist derselbe Befehl auch eine Range (zumindest in VBA, in .NET ist das ein Range Objekt und nichts anderes.)
    Range("B1:B2") = arr
    kopiert dann auch schon A1:A2 nach B1:B2

    Vielen Dank für Eure Antworten.

    Die Daten kommen aus dem SAP in Form einer Excel heraus. Die Spalten sind dank einer Variante immer die selben, die Zeilen Variabel.
    Deshalb habe ich alles als Tabelle deklariert und mit den Codes für Strg+Alt und Pfeiltaste recht/unten die Tabelle dynamisch definiert.

    Für die Pivot selber ist nur die Tabelle definiert, welche nicht zu 100% gelöscht wird --> ich lösche alle Daten ab der 2. Eintragungszeile und ersetze in der ersten Zeile alle Inhalte durch "Löschvermerk" - inzwischen habe ich herausgefunden, dass dies das Problem ist. Die Pivot funktioniert einwandfrei, solange die Daten aus dem SAP darinstehen - sobald nur eine Zeile mit "Löschvermerk" übrig ist, dann lässt sich die Tabelle nicht mehr aktualisieren. Sobald ich die Datei wieder mit Daten füttere wird die Zeile "Löschvermerk" mit einem weiteren Makro (der auch die Daten einliest) gelöscht.

    Damit ist das eine mir ausreichende Lösung, dann die kopierte Datei wird niemals leer sein - das war nur für einen Test der Fall.
    Sorry für die Umstände.

    Vielen Dank für Eure Hilfe!!

    Luca441 schrieb:

    Sobald ich die Datei wieder mit Daten füttere wird die Zeile "Löschvermerk" mit einem weiteren Makro (der auch die Daten einliest) gelöscht.

    Warum so kompliziert?
    Hole Dir die Daten mit PowerQuery in die Tabelle und aktualisiere dann einfach die PowerQuery-Abfrage.
    NB. Es ist doch schön, wenn man lesbare Namen vergibt. Siehe auch [VB.NET] Beispiele für guten und schlechten Code (Stil).