Excel Datei komplett OHNE CODE und OHNE FORMELN, aber mit Formatierungen... speichern!!....

  • Excel

Es gibt 24 Antworten in diesem Thema. Der letzte Beitrag () ist von ul-piloten.

    Excel Datei komplett OHNE CODE und OHNE FORMELN, aber mit Formatierungen... speichern!!....

    Hallo liebe Community!

    Ich habe eine Frage...

    Ich habe eine Excel-Datei (Endlung: .xlsm) in der ich viele Arbeitsblätter habe. Auf diesen Arbeitsblättern sind in den Zellen meistens Formeln eingebaut, die mir dann einen Wert anzeigen.
    Des Weiteren, habe ich in den verschiedensten Tabellenblättern, mehrere AcitveX-Buttons und habe in der ganzen Datei, ziemlich viel Code hinterlegt!

    Mein Bestreben / Wunsch, ist es nun, dass ich diese Datei, nochmals, praktisch als eine Kopie von sich selbst speichern möchte (dh: alle Tabellenblätter und Formatierungen sind komplett ident....) ABER,
    1.) alle Felder sollen ausschließlich nur Werte enthalten! --> Somit: Jedes Feld, was eine Formel enthält soll nur den Wert, den die Formel ausgibt beinhalten, aber nicht mehr die Formel selbst!
    und
    2.) Sämtlicher Code, muss aus der Mappe entfernt sein! - Es soll kein Code mitgespeichert werden

    So... Zu 2.) habe ich gesehen, dass es ja möglich ist, die Excel Datei einfach als .xlsx-Datei zu speichern und dann werden automatisch der Code bzw. die Makros nicht mitgespeichert!
    Jedoch bleiben mir natürlich die Formeln erhalten :((


    Wie könnte man das in VBA umsetzen, dass ich einen Button habe, der mir bei Betätigung eine Kopie von der aktuellen Datei speichert, aber mit den zuvor genannten Bedingungen??

    Ich habe mal ein bisschen recherchiert... und bin dabei auf das hier gestoßen:

    Visual Basic-Quellcode

    1. Sub SpeicherMirsAlsNeueMappe()
    2. Dim vntPathAndFile As String
    4. Rem Speicherndialog aufrufen und Pfad/Name abfragen
    5. vntPathAndFile = Application.GetSaveAsFilename(InitialFileName:=ActiveSheet.Name & Format(Now, " dd.mm.yyyy ") & ".xlsx", _
    6. FileFilter:="Excel Files(*.xlsx), *.xlsx", Title:="Speichern als")
    8. Rem Wenn Dialog abgebrochen, dann Makro verlassen
    9. If vntPathAndFile = "Falsch" Then Exit Sub
    11. Rem Neue Arbeitsmappe erstellen
    12. Workbooks.Add
    14. Rem Bildschirmmeldungen auschalten
    15. Application.DisplayAlerts = False
    17. Rem Einfügen und speichern
    18. ThisWorkbook.Sheets(1).UsedRange.Copy
    20. With ActiveWorkbook
    22. With .ActiveSheet
    23. .Range("A1").PasteSpecial xlPasteValues
    24. End With
    26. .SaveAs Filename:=vntPathAndFile, FileFormat:=xlOpenXMLWorkbook
    27. .Close
    29. End With
    31. Rem Zwischenablage leeren
    32. Application.CutCopyMode = False
    34. Rem Bildschirmmeldungen einschalten
    35. Application.DisplayAlerts = True
    36. End Sub



    Jedoch möchte ich die Datei komplett speichern und ich möchte auch dasss alle Formatierungen erhalten bleiben... lediglich Code und Formeln und stattdessen nur Werte sollen gesetzt sein....

    Hat jemand eine Idee??


    LG Tim

    Eine Idee wäre ...

    Visual Basic-Quellcode

    1. Application.Workbooks.Add ' Neue Mappe erstellen
    3. Dim counter As Integer
    4. Dim wbNew As Workbook
    5. Dim shtOld, shtNew As Worksheet
    7. Set wbNew = Application.Workbooks(Application.Workbooks.Count)
    8. Do While wbNew.Worksheets.Count < ThisWorkbook.Worksheets.Count
    9. wbNew.Worksheets.Add ' Weitere Tabellen hinzufügen, falls nötig
    10. Loop
    12. ' Tabellen kopieren
    13. For counter = 1 To ThisWorkbook.Worksheets.Count
    14. Set shtOld = ThisWorkbook.Worksheets(counter) ' Quelltabelle
    15. Set shtNew = wbNew.Worksheets(counter) ' Zieltabelle
    16. shtNew.Name = shtOld.Name ' Tabellenname übernehmen
    18. shtOld.UsedRange.Copy ' Quelldaten und -format kopieren
    19. shtNew.Range("A1").PasteSpecial xlPasteValues ' Werte einfügen
    20. shtNew.UsedRange.PasteSpecial xlPasteFormats ' Format übernehmen
    21. Next
    23. wbNew.SaveAs "..."

    wie ist das mit .SaveAs Methode??

    wbNew.SaveAs(Mappe_NEU,xlWorkbookNormal) ?? da ist die synthax falsch... wie muss ich das hinschreiben...

    wenn ichs es ohne code sichern will... also als xlsx...??


    und... ich weiß ja noch nicht ob der mir dann was bei der Formatierung etc. ändert...

    aber die Datei, die erstellt wird, hat leider nicht die gleiche Formatierung wie die ursprüngliche Datei... und... des Weiteren wird die Pivottabelle, nicht als Pivottabelle gespeichert... :((

    Weil...

    Ich bräuchte wirklich eine komplett idente Version von meiner ursprünglichen Datei, aber eben so, dass kein VBA Code mehr vorhanden ist (vl. als xlsx Datei speichern lassen...) und eben alle Formeln in Werte umgewandelt werden...

    bin leider immer noch nicht draus schlau geworden... :((

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „ereza“ ()

    ereza schrieb:

    wbNew.SaveAs(Mappe_NEU,xlWorkbookNormal) ?? da ist die synthax falsch
    Methode werden ohne Klammern aufgerufen.

    Anderer Vorschlag:

    Visual Basic-Quellcode

    1. Sub SaveWorkbookCopy()
    2. Dim wb As Workbook, ws As Worksheet, sh As Shape
    3. Set wb = Workbooks.Add(xlWBATWorksheet)
    4. wb.Sheets(1).Name = "deleteMe"
    5. For Each ws In ThisWorkbook.Worksheets
    6. ws.Copy After:=wb.Sheets(wb.Sheets.Count)
    7. Next
    8. For Each ws In wb.Worksheets
    9. UsedRange.Formula = UsedRange.Value
    10. For Each sh In ws.Shapes
    11. sh.Delete
    12. Next
    13. Next
    14. Application.DisplayAlerts = False
    15. wb.Sheets("deleteMe").Delete
    16. wb.SaveAs Replace(ThisWorkbook.FullName, ".xlsm", ".xlsx"), xlWorkbookNormal
    17. Application.DisplayAlerts = True
    18. wb.Close False
    19. End Sub
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --

    ok danke...

    ich verstehs aber noch nicht ganz.. .wo wird da dann die erstellte Kopie gespeichter? und wie heißt die dann??
    und was wird jz gelöscht? --> wb.Sheets("deleteMe").Delete

    Gespeichert wird's unter demselben Namen wie die Original .xlsm, aber als .xlsx.
    Wenn der das nicht zusagt, nimm einen anderen Namen.

    Gelöscht wird das leere Initialblatt, die beim Erzeugen des neuen Workbooks automatisch angelegt wird.

    Grundidee ist:
    - Neues Workbook erzeugen
    - Alles Sheets ins neue Workbook kopieren
    - Dort bereinigen
    - Speichern
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --

    ahaa... ok... verstehe...

    hmmm... ich habe das nun ausprobiert... aber dann bekomme ich eine Fehlermeldung von Excel, wenn ich die erzeugte Datei öffnen möchte, dass diese beschädigt ist, da das Dateiformat oder die Dateierweiterung ungültig ist.... ich soll überprüfen ob die Datei beschädigt ist und ob das Format stimmt...

    woran kanns liegen?? - weil die dateierweiterung passt!

    Stimmt.
    xlWorkbookNormal schreibt den Typ ".xls"
    Nimm statt dessen
    xlOpenXMLWorkbook

    Und wenn du den Tagesstempel auch noch benötigst:
    wb.SaveAs Replace(ThisWorkbook.FullName, ".xlsm", "_" & Format(Date, "yyyymmdd") & ".xlsx"), xlOpenXMLWorkbook

    Hinweis:
    Wenn du Zeitstempel in Dateinamen unterbringst, nimm immer ein sortierfähiges Format.
    Deine oben vorgeschlagene Variante ​ActiveSheet.Name & Format(Now, " dd.mm.yyyy ") & ".xlsx" erzeugt mit der Zeit das blanke Chaos im Verzeichnis.
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „petaod“ ()

    Ja super!! Genau an dem lags...!! Jz kann ich die erzeugte Datei öffen...

    die makros sind natürlich auch alle weg...

    das einzige was jz noch komisch ist, ist die Verlinkung bzw. Formel-Geschichte...

    denn... wenn ich auf Zellen klicke wo im Orginal eine Formel stand, dann steht da eigentlich immer noch eine Formel... aber angepasst mit Verlinkung... also...z.B.:

    =INDEX('\\PFAD...\Desktop\[Testlauf Pivot 3.xlsm]1 - Alle Aufträge zu Equipments'!K:K;VERGLEICH($K82;''\\PFAD...\Desktop\[Testlauf Pivot 3.xlsm]1 - Alle Aufträge zu Equipments'!$J:$J;0))

    So... das lustige an der Sache ist jz nun aber, dass wenn ich die Quelldatei umbenenne... und die zuvor erzeugte Kopie öffne...

    dann weist mich Excel darauf hin dass mindestens eine Verlinkung besteht und diese aktualisiert werden müssen... - so... wenn ich nun bewusst nein sage und mir dadurch erwarte... dass überall dort wo eine Verlinkung auf die ursprüngliche orginaldatei eingetragen ist, ein #NV als wert stehen müsste oder so etwas...

    dann steht trotzdem noch der wert da... obwohl er eigentlich gar nicht mehr den wert haben dürfte....

    woran liegt das?? wird trotz ersetzen des inhalts von formel auf Value, irgendwo noch die Information von der ursprünglichen formel gespeichert oder so??

    bzw. wie könnte man das auch noch bereinigen?

    Vielen Dank!

    ich glaube mittlererweile dass es an der Zeile irgendwie liegen müsste:

    Visual Basic-Quellcode

    1. UsedRange.Formula = UsedRange.Value


    irgendwie scheint das nicht zu klappen... weiß aber auch nicht warum...

    weil eigentlich müsste der doch laut Gleichung, alles was in used range ist und eine formel hat zu einem value machen oder??

    habe mittlererweile herum probiert, aber es haut nicht hin... leider... :((

    Vor Zeile 14

    Visual Basic-Quellcode

    1. Do While wb.Connections.Count > 0
    2. wb.Connections.Item(1).Delete
    3. Loop

    Edit:
    Vermutlich meinst du aber eher das:

    Visual Basic-Quellcode

    1. For Each Link In wb.LinkSources(xlLinkTypeExcelLinks)
    2. wb.BreakLink Name:=Link, Type:=xlLinkTypeExcelLinks
    3. Next
    Das kannst du vermutlich auch vor Zeile 8 einfügen, ohne dass es Böses macht.
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --

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

    hmmm vor Zeile 14... (in meinem fall ists 1 mehr... da ich noch am anfang eine hinweismeldung ausgebe...) das heißt mein Code schaut jz so aus:

    Visual Basic-Quellcode

    1. Sub CommandButton1_Click()
    2. MsgBox "Bla Bla Hinweis..."
    3. Dim wb As Workbook, ws As Worksheet, sh As Shape
    4. Set wb = Workbooks.Add(xlWBATWorksheet)
    5. wb.Sheets(1).Name = "deleteMe"
    6. For Each ws In ThisWorkbook.Worksheets
    7. ws.Copy After:=wb.Sheets(wb.Sheets.Count)
    8. Next
    9. For Each ws In wb.Worksheets
    10. UsedRange.Formula = UsedRange.Value
    11. For Each sh In ws.Shapes
    12. sh.Delete
    13. Next
    14. Next
    15. Do While wb.Connections.Count > 0
    16. wb.Connections.Item(1).Delete
    17. Loop
    18. Application.DisplayAlerts = False
    19. wb.Sheets("deleteMe").Delete
    20. wb.SaveAs Replace(ThisWorkbook.FullName, ".xlsm", "_" & Format(Date, "yyyymmdd") & ".xlsx"), xlOpenXMLWorkbook
    21. Application.DisplayAlerts = True
    22. wb.Close False
    23. End Sub


    haut aber leider immer noch nicht hin... oder hab ich das falsch platziert?? meintest du vl. in der äußeren For-Schleife drinnen?









    NACHTRAG:

    Edit:
    Vermutlich meinst du aber eher das:Visual Basic-Quellcode
    For Each Link In wb.LinkSources(xlLinkTypeExcelLinks)
    wb.BreakLink Name:=Link, Type:=xlLinkTypeExcelLinks
    Next

    Das kannst du vermutlich auch vor Zeile 8 einfügen, ohne dass es Böses macht.


    PERFEKT!!!! Super!!! Jz hauts hin!!! VIELEN HERZLICHE DANK!!

    nur... kannst du mir vl. kurz erklären, falls du zeit hast, was denn diese Code Stelle eigentlich genau macht? weil ich blick leider immer noch nicht ganz durch... - würde es aber gerne verstehen...

    Vielen Dank mal wieder!! ;)

    LG Tim

    Dieser Beitrag wurde bereits 2 mal editiert, zuletzt von „Marcus Gräfe“ ()

    Ach... eine Frage hätte ich da noch...

    Wie funktioniert das mit dem Shape-Objekt? für was ist das gut?? also...

    Was macht:

    Visual Basic-Quellcode

    1. For Each sh In ws.Shapes
    2. sh.Delete
    3. Next


    eigentelich??

    Laut Internet-Recherche repräsentiert .shapes ein Objekt im Zeichnungslayer... wie Grafiken usw... - aber...da ist ja nix... und ich denke... ich habe noch nicht wirklich verstanden was shapes überhaupt genau ist...

    auch als Datentyp ---> Dim sh As Shape


    Kannst du mir das vl. kurz nochmals erklären, was shapes ist und was die for-schleife dann eigentlich macht?? Bzw. für was die dann gut ist...?

    würde das nämlich auch wieder gerne verstehen wollen... damit ich a bissl mehr durchblick erhalte... ;)

    Vielen Dank!

    LG Tim

    ahhh ok... Buttons auch... alles klar..!!! na, die hab ich naütrlich schon drinnen...! dann brauch i die shapes...

    ok...

    ABER SUPER!! jz weiß i des auch!!!

    Danke!! petaod!!

    LG Tim

    @petaod

    ich habe die gleiche herausforderung welche du in oberem beitrag für den anderen user so toll gelöst hast.
    wenn ich deinen code kopiere und starte, bekomme ich bei UsedRange.value immer einen laufzeitfehler '424 - Objekt erforderlich.

    hast du eine idee woran das liegen kann?

    vielen dank!