Office: VBA - Makros in zweiter Mappe mittels Makro löschen

Hallo,

ich habe eine Datei, die jede Woche einmal als Kopie abegspeichert wird.
Da sie eventuell auch zum Kunden geht, dürfen dort keine Makros drin sein (damit keiner Blödsinn machen kann der sich nicht auskennt).

Bisher kopiere ich ziehmlich aufwendig alle Blattinhalte und setze danach die Formatierungen.

Das würde ich jetzt gerne etwas schlauer und schneller gestalten und ganz normal eine Kopie der Mappe als .xls speichern und alles rauslöschen was weg soll.

Die auf den Blättern vorhandenen ActiveX-Steuerelemente sollte ich vermutlich so:

Code:

    For Each obj In ActiveSheet.OLEObjects
        obj.delete
    Next obj

gelöscht bekommen.
(zumindest so ähnlich sollte es ja gehen, muss ich mir noch in Ruhe anschauen)

Dann bleiben aber immernoch die Makros.
Kann ich mit VBA den Code in einer anderen Mappe löschen?
Wenn ja, wie?

 

Halo achim,

dazu ist der Zugriff auf das VBA Projekt notwendig, ich vermute das sagt Dir nichts. Ich als Dein Kunde würde das bei meinem Excel nicht einstellen, da dann Fremde schlimme Sachen anstellen kann.
Also lasse den Gedanken fallen, ist meine Empfehlung.

Gruß hajo

 

Hallo Hajo.

ich vermute da liegt ein fundamentales Missverständnis vor.
Der Kunde soll und braucht garnichts zu tuen, zu erlauben, einzustellen, freizugeben.
Mit dieser Datei arbeite ich (und manchmal Kollegen), nicht der Kunde.
Wenn der Kunde aber doch einmal die Daten bekommen soll, bzw. auch um einen wöchentlichen Stand abzuspeichern, dann soll die Datei als Kopie von und bei UNS (nicht beim Kunden) gespeichert werden.
Und weil dann ja ein Stand abgespeichert wurde, die Datei also eh nicht mehr verändert werden sollte, und weil beim Kunden vermutlich niemand Ahnung von Makros hat und die dann die programmierten Funktionen eh nicht brauchen, genau darum möchte ich eine Kopie ohne Makros speichern.

Den Zugriff auf die andere Datei kann ich mir selber freigeben, das ist kein Problem.

Wenn ich allerdings eine Kopie einfach nur als .xls speichere, dann sind die Makros weiterhin mitgespeichert.
Deshalb suche ich eine Möglichkeit den Kompletten Code der Kopie aus meiner Arbeitsdatei heraus zu löschen.

Ich hoffe jetzt ist verständlicher was ich möchte.
Ansonsten einfach nachfragen.

 

Halo Achim,

dann kürzer

dazu ist der Zugriff auf das VBA Projekt notwendig, ich vermute das sagt Dir nichts.
Also lasse den Gedanken fallen, ist meine Empfehlung.

Gruß hajo

 

Hallo!

Du hast Excel2007 als Version angegeben.
Dann kannst du die Mappe doch einfach als .xlsx speichern, oder denke ich hier zu simpel?

 

Hallo Hajo,

okay, das Missverständniss lag auf meiner Seite.
Habe mal nach "Zugriff auf das VBA Projekt" gegoogelt.
Von der Einstellung lasse ich wirklich die Finger.

Danke für Deine Antwort.

Schallo Hatzi ;O)
könnte ich, aber vermutlich denkst Du da zu simpel, oder ich habe meinen Fehler noch nicht gefunden.
Egal ob ich als .xlsx oder als .xls speichere, die makros sind noch drin.
Und wenn man in Excel die Einstellungen so hat, das Makros mit Benachrichtigung deaktiviert werden, sprich man gefragt wird ob man sie zulassen will oder nicht, dann taucht bei jedem Öffnen dieser neuen Datei eine solche Frage auf.
1. nervig
2. da ich nicht weiß ob meine Kollegen immer daran denken die Makros dann nicht zu erlauben, und da ich absolut nicht weiß wie die Einstellungen bei unseren Kunden, bzw. deren Umgang damit ist, möchte ich sowas halt vermeiden.


Anscheinend bleibt nix anderes als mein alter Weg, ein ellenlanges Makro welches die benutzten bereiche jedes Blattes in eine neue Datei kopiert und danach alle Einstellungen, Formatierungen etc. macht.

Wenn ich ".cells.copy" benutze, muss ich zwar die Spaltenbreite und Zellenhöhe nicht mehr anpassen (wird mit übernommen), aber die ganzen Zellformatirungen (Zahl, Text, Datum...), der Druckbereich, die Seitenumbrüche, der Zellschutz, und vieles mehr werden nicht mit übernommen.
Das muss ich alles per Makro "nachschieben".
Wenn ich Bereiche kopiere werden nicht mal Höhe und Breite übernommen.

Oder gibt es da eine schlauere Möglichkeit?

 

Ich baue meine Auswertungen normalerweise so auf:
1. Blatt "Auswertung": Hier stehen Parameter wie z.B. Pfade zu Dateien, die importiert werden sollen, numerische Parameter für Bereichs- oder Datums-Grenzen usw. Außerdem sind hier auch Radio-Buttons, Checkboxen usw. untergebracht, die die Auswertung steuern.
2. Blatt "Legende": Hier sind kurze Hinweise zu der Auswertung, ggf. eine kleine Anleitung, Erklärung des Ergebnisses usw.
3. bis x. Blatt: Hier liegen Hilfstabellen, die "statisch" sind, also nicht bei jeder Auswertung importiert werden müssen.

Auf dem ersten Blatt liegen dann auch alle Buttons, die die eigentliche Auswertung starten. Und diese Auswertung erstellt dann ein neues Blatt, auf dem das Ergebnis der Auswertung berechnet wird.

Ich setze gerne Formeln per VBA in die Zellen, anstatt die Ergebnisse selbst per Code zu berechnen. Meistens kopiere ich dann am Ende der Auswertung alle Zellen des Blattes und setze mit .PasteSpecial nur die Werte wieder ein.

Und ein .Copy auf dieses Ergebnisblatt erstellt mir eine neue Arbeitsmappe mit diesem Blatt als Inhalt. Das läßt sich prima abspeichern und beinhaltet weder Formeln noch VBA-Code. Den Dateinamen kann ich z.B. mit dem Tagesdatum eindeutig vergeben und tlw. verschicke ich die Dinger sogar direkt über Lotus Notes per E-Mail.

Langer Rede kurzer Sinn: Das Ergebnis der Auswertung ist von vornherein vom VBA-Code getrennt. Der befindet sich im Modul zum Blatt "Auswertung" bzw. in extra Code-Modulen, während das Ergebnisblatt VBA-frei ist.

Ein neues Blatt erstellen? Eigentlich ganz simpel:

Code:

Sub Sht_Erstellen(TargetSheet$, Optional FormatSheet As Boolean = True)

    ThisWorkbook.Activate

    Application.DisplayAlerts = False
    On Error Resume Next
    ThisWorkbook.Worksheets(TargetSheet).Delete
    On Error GoTo 0
    Application.DisplayAlerts = True

    ThisWorkbook.Worksheets.Add
    ThisWorkbook.ActiveSheet.Name = TargetSheet
    ActiveSheet.Move after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)

    ThisWorkbook.Worksheets(TargetSheet).Activate
    ThisWorkbook.ActiveSheet.Range("a1").Activate
    ThisWorkbook.ActiveSheet.Range("a1").Select

    If (FormatSheet) Then
        With ThisWorkbook.ActiveSheet.PageSetup
            '.Application
            .BlackAndWhite = False
            .BottomMargin = Application.CentimetersToPoints(2)
            .CenterFooter = "&CPage &P / &N" '"bottom center"
            .CenterHeader = "" '"top center"
            .CenterHorizontally = False
            .CenterVertically = False
            '.ChartSize
            '.Creator
            .Draft = False
            .FirstPageNumber = xlAutomatic
            .FitToPagesTall = 1
            .FitToPagesWide = 1
            .FooterMargin = Application.CentimetersToPoints(1)
            .HeaderMargin = Application.CentimetersToPoints(1)
            .LeftFooter = "&LConfidential Yellow" '"bottom left"
            .LeftHeader = "" '"top left"
            .LeftMargin = Application.CentimetersToPoints(1)
            .Order = xlOverThenDown
            .Orientation = xlPortrait
            .PaperSize = xlPaperA4
            '.Parent
            .PrintArea = False
            .PrintComments = xlPrintNoComments
            .PrintGridlines = False
            .PrintHeadings = False
            .PrintNotes = False
            .PrintTitleColumns = False '"a:a"
            .PrintTitleRows = False '"1:1"
            .RightFooter = "&RPrint Date: &D" '"bottom right"
            .RightHeader = "" '"top right"
            .RightMargin = Application.CentimetersToPoints(1)
            .TopMargin = Application.CentimetersToPoints(1)
            .Zoom = 100
        End With
    End If

End Sub

Wie Du siehst macht ein Großteil die Formatierung des Seitenaufbaus aus. Ggf. kannst Du den Default-Wert im Prozedur-Header ja auch auf "false" setzen. Dieses dödelige PageSetup Objekt ist nämlich unangenehm langsam.

Gruß, Michael

 

Lieben Dank Michael,

generell wäre so eine Trennung natürlich das einfachste ( und sinnvollste).
In meinen anderen (bisherigen) Projekten habe ich das im Prinzip auch gemacht.
Dort habe ich reine Datenblätter, Blätter mit dauerhaften Stammdaten und temporären Hilfsdaten.
Je nach Aufbau brauche ich nur komplette Blätter zu kopieren, oder ich sammle die benötigten Daten auf einem ganz neuen Blatt.

Bei dieser Datei hier geht das aber leider nicht.
Es wird direkt in den Datenblättern gearbeitet, und einige dieser Blätter enthalten auch Code (Change Ereignis u.a.).
Und kopiert wird keine Auswertung, sondern die komplette Datei.

Bei der "ruckzuck-mal-eben-schnell" Erstellung war das damals der sinnvollste Aufbau.
Wie so vieles sollte es ja auch nur ein Provisorium für kurze Zeit werden.

Jetzt wächst die Datei, und der Kopiervorgang dauert halt immer länger, deshalb habe ich mich überhaupt nur wieder damit beschäftigt.

Naja, da die Kopiererei ja nur einmal pro Woche gemacht werden muss, lass ichs erstmal so laufen.
Die Datei komplett umzustricken (am besten mit Eingabemaske etc.) habe ich momentan einfach nicht die Zeit (und Lust).
Außerdem soll sie ja eigentlich auch irgendwann mal durch eine ganz andere Lösung ersetzt werden....
Ich glaubs wenns denn dann passiert ist.

Lieben Dank für Eure Zeit.

 

Hi Achim,

arbeite mit Klassenprogrammierung, dann kannst du alle Ereignisse, die normalerweise im Codemodul der Tabellenblätter liegen, in das Klassenmodul schreiben. Beim Kopieren der Tabellenblätter wird dann auch kein Code mit kopiert, da alles ja im Klassenmodul steht.

Bis später,
Karin

 

Hallo Karin,
wow, das hört sich stark nach einer Lösung an.
Okay, ich habe zwar absolut keinen Plan von "Klassenprogrammierung", aber gestern Abend wenigstens schonmal ein wenig gegoogelt.
Jetzt weiß ich schonmal in welche Richtung das geht.

Wie man sowas dann umsetzt, muss ich noch rausbekommen.
(werd hier bestimmt noch die eine oder andere Frage dazu stellen) ;O)

Danke für den Tipp.

 

Hi Achim,

ich habe mal ein einfaches Beispiel erstellt - vielleicht hilft es dir als Anregung: Klassen Arbeitsblatt. Bei Eingabe in B10 beider Tabellenblätter wird eine MsgBox angezeigt.

Bis später,
Karin

 

Hallo Karin,

ganz lieben Dank dafür.
Es hat mich sofort zum Ausprobieren angeregt. ;O)


Und natürlich habe ich eine Frage:

im Standardmodul hast Du die Routine "Sub ... " genannt. Fehlt da nicht das "Public" ? Oder ist das automatisch und man kann es auch weglassen?



Ich glaube, ich bekomme das hin.
Den Ansatz habe ich jetzt gefunden.


Einen Teil vom Code habe ich in "DieseArbeitsmappe" verlegt.
Und den für den Rest habe ich herausgefunden wie ich aufgerufen bekomme und die einzelnen Steuerelemente ansprechen muss.

Der Code macht hauptsächlich folgendes:
Wenn die Checkbox "Wareneingang" aktiviert ist, wird der alte Zellwert vor der Eingabe gespeichert und die neue Eingabe dazu addiert.
Bei "Warenausgang" wird halt subtrahiert.
Dies spart mir das Rechnen, und ich kann einfach die entsprechenden Stückzahlen der Lieferscheine bzw. sonstigen Listen eintippen.

Der Rest ist ein bisschen Datumsgefummel, aufräumen usw.


Ich habe mal die Originaldatei mit dem neuen Code (natürlich datenbereinigt) angehängt, als Zwischenstand... da ist noch viel Murks drin:

 

Hi Achim,

zu 1. eine Sub- oder Function-Prozedur in einem allgemeinen Modul ist immer "public", deshalb muss vor der Sub kein Public stehen.

zu 2. die Steuerelemente musst du natürlich auch initialisieren. Im geänderten Code geschieht das gleichzeitig mit dem Initialisieren der Tabellnblätter. Dabei werden die CheckBoxen aller Tabellenblätter in die Klasse eingelesen, denn Excel "weiß" ja, zu welchem Tabellenblatt die betreffende CheckBox gehört.

Ich habe den Code entsprechend geändert - die Ändergunden erkennst du daran, dass ich davor und danach je eine Zeile '************* eingefügt habe. Einige der Variablen habe ich auch als Public um-deklariert, sodass sie in allen Prozeduren verwendet werden können.

Bis später,
Karin

 

Hallo Karin,

ganz lieben Dank für Deine Mühe mit den Änderungen in meiner Datei.
Das hat mich einen riesen Schritt weiter gebracht.

Ich habe noch alles angepasst, und bin eigentlich da wo ich hinwollte...
Eigentlich klappt jetzt alles genauso wie ich das brauche...
Tja... eigentlich...

Wenn da nicht die obligatorischen Probleme wären:

Beim Öffnen der Mappe werden die Klassen zwar initialisiert (meistens, auch nicht immer), aber des öfteren "verliert" sich dies im Laufe der Zeit, sprich die Ereignisse werden einfach nicht mehr aufgerufen. Ich habe noch kein System/keine Regel hinter diesem Verhalten entdecken können. Tritt dies ein. muss ich die Datei schliessen und erneut öffnen und irgendwie steigen auch nicht immer alle Ereignisse gleichzeitig aus, sondern teilweise erst so nach und nach.
Haben Klassen eine " Zeitbegrenzung " o.ä.?
Kann man diese " Terminierung " irgendwie verhindern? Bzw. wie und wo muss ich die Neuinitialisierung einbauen?

Im Anhang ist meine überarbeitete Version zu finden.




Und dann habe ich natürlich (wie könnte es auch anders sein) noch Fragen:

Aus Deiner Datei:

Code:

Private Sub clCheckBox_Click()
'**********************************
   Dim chkBox As OLEObject
   Set chkBox = clCheckBox.Parent.OLEObjects(clCheckBox.Name)
   If blnCheckBox = False Then
      Application.EnableEvents = False
      For Each chk In clCheckBox.Parent.OLEObjects
         If chk.progID = "Forms.CheckBox.1" Then
            If chk.Name <> clCheckBox.Name Then chk.Object = Not chkBox.Object
         End If
      Next chk
      Set chkBox = Nothing

Hier verstehe ich nicht woher Excel weiß welche CheckBox mit " blnCheckBox " gemeint ist, und wofür diese Variable überhaupt notwendig ist.
Ebenso warum " Application.EnableEvents = False " an dieser Stelle steht und was dieser Befehl

Code:

Then chk.Object = Not chkBox.Object

konkret bedeutet/macht.

 

Hi Achim,

Klassen haben keine "zeitliche Begrenzung" - nur wenn du am Code irgend etwas veränderst, müssen sie neu initialisiert werden.

Die Variable blnCheckBox ist nur eine Variable die prüfen soll, ob der Code bereits ausgeführt wurde oder nicht, denn andernfalls würde er erneut ausgeführt, was ja nicht erforderlich ist.

Mit Application.EnableEvents wird der Code daran gehindert, mehrmals durchlaufen zu werden. Am besten testest du das, indem du einen Haltepunkt auf die Prozedur setztund alles im Einzelschrittmodus ablaufen lässt.

Die Zeile

Code:

Then chk.Object = Not chkBox.Object

macht nichts anderes, als dass sie den Zustand der CheckBox umschaltet - ist sie aktiviert, wird sie deaktivert und umgekehrt.

Aber mal generell: das ganze Prozedere würde vereinfacht werden, wenn du keine ActiveX-Steuerelemente sondenr die Steuerelemete aus der Formular-Symbolleiste verwenden würdest. Dann könntest du den Code in ein allgemeines Modul auslagern und er müsste nicht - so wie derzeit - im Tabellenblatt berücksichtigt und dadurch auch die Steuerelemente initialisiert werden.

Bis später,
Karin