Hallo,
ich speichere aus einer XLSM-Mappe mit Rechnungs-Vorlagen einzelne Sheets als XLSX, also ohne Makros ab, weil die dann weiterverarbeitet werden sollen.
Welche Parameter muss ich hinter
SaveAs ausser Name angeben, damit keine Rückfrage kommt, ob ich zulasse dass die Datei ohne Makrios als XLSX gespeichert wird. Die Warnung beim evtl. Überschreiben soll aber bleiben.
Ich habe die unerklärliche Erscheinung, dass bei wirklich gleichem Makrocoding NeueRechnung_Speichern bei einer Mappe die Rückfrage kommt, bei einer anderen nicht. Für unbedarfte Anwender, die die Rechnungen weiterbearbeiten sollen, ist das wohl irritierend.
Haben Sie einen Rat für mich ?
Danke, Rudolf Nitze
Nochmals herzlichen Dank, Herr Klemke.
Es war ein sehr angenehmer und erfreulicher Dialog mit Ihnen.
Viele Grüsse, vielleicht bis bald.
Rudolf Nitze
Schön, dass Ihre Frage beantwortet wurde!
Um weiterhin auf dem Laufenden zum Thema Technik und PC zu bleiben, nehmen Sie doch kostenlos teil an unseren monatlichen LIVE-Webinaren: hier klicken für Registrierungsseite. Bei diesen Online-Shows können Sie uns all Ihre Fragen rund um das Thema Computer stellen und lernen jeden Monat etwas Neues.
Sehr geehrter Herr Nitze,
wenn Sie das zu kopierende Blatt zuerst zu einer eigenen Arbeitsmappe machen, dann können Sie diese Arbeitsmappe anschließend mit SaveAs ohne Rückfrage wegen der VBA-Komponenten speichern. Die Warnung vor Überschreibung bleibt.
Das Programm dazu sieht im Prinzip so aus:
Sub Speichern()
Dim blattName As String
Dim wbBlatt As Workbook
Dim ws As Worksheet
´############# Hier anpassen ###########
blattName = „Rechnung 02“
´#######################################
Set ws = ThisWorkbook.Worksheets(blattName)
ws.Copy
Set wbBlatt = ActiveWorkbook
wbBlatt.SaveAs Filename:=blattName & „.xlsx“, _
FileFormat:=xlWorkbookNormal
wbBlatt.Close
End Sub
Mit freundlichen Grüßen
Dieter Klemke
Danke, Herr Klemke.
Die Abfrage wegen der Makros kommt nun wirklich nicht mehr, Überscheiben wird korrekt abgefragt, aber ich bekomme danach die Meldung:
„Die folgenden Features in dieser Arbeitsmappe werden von früheren Excel-Versionen nicht unterstützt …..usw “
Das Ursprungs-Sheet und das neu erzeugte weisen beide die Excel-Version „16“ aus.
Ich finde keine Hinweise, wie ich diese Version als FileFormat angeben kann.
FileFormat „xlExcel16“ kennt er nicht.
Und noch eine Frage: Wie kann ich in dem neu zu erzeugenden Sheet vor dem Speichern den „CommandButton1“ entfernen, mit dem ich im Ursprungs-Workbook das Kopieren und Speichern ausgelöst habe ? Es soll ja ein Sheet ohne jedes Makro entstehen. Mit Clear den Cellbereich löschen, wo es angezeigt wird, nützt nichts.
Danke für Ihren Rat.
Rudolf Nitze
Nachtrag:
die ertellte Datei lässt sich nicht öffnen. Ich habe Dateiformat von „xlsc“ in „xls“ geändert, dann kann sie geöffnet werden, bringt aber beim esten Öffnen eine Warnung.
Das FileFormat muss also irgendwie anders angegeben werden.
Danke, Rudolf Nitze
Sehr geehrter Herr Nitze,
es tut mir leid, mit dem FileFormat-Parameter habe ich mich vertan.
Er muss jetzt „xlOpenXMLWorkbook“ lauten.
Im folgenden Programm werden alle auf die Excel-Oberfläche geklebten Objekte entfernt, neben Schaltflächen und sonstigen Steuerelementen auch grafische Elemente und Bilder.
Wenn dadurch bei Ihnen zuviel entfernt wird, dann lässt sich das auch ganz gezielt auf Ihre Schaltfläche beschränken. Dazu müsste ich wissen, ob die Schaltfläche vom Typ „Formular“ oder von Typ „Active-X“ ist.
Ggf. können Sie mir auch Ihre Arbeitsmappe (Daten verändert und anonymisiert) an meine E-Mail „dieter.klemke@web.de“ schicken.
Sub Speichern()
Dim blattName As String
Dim pfad As String
Dim sh As Shape
Dim wbBlatt As Workbook
Dim ws As Worksheet
pfad = ThisWorkbook.Path & „“
Set ws = ActiveSheet
blattName = ws.Name
ws.Copy
Set wbBlatt = ActiveWorkbook
For Each sh In wbBlatt.Worksheets(1).Shapes
sh.Delete
Next sh
wbBlatt.SaveAs Filename:=pfad & blattName & „.xlsx“, _
FileFormat:=xlOpenXMLWorkbook
wbBlatt.Close
End Sub
Mit freundlichen Grüßen
Dieter Klemke
Vielen Dank Herr Klemke.
Habe Ihre Anregungen eingebaut, nun putzt mir das Makro die Schaltfläche auf dem neuen Sheet prima aus, aber leider auch das Logo, das als Bild importiert wurde.
Was muss ich da einschränken ?
Die Abfrage nach dem Speichern der Makros bleibt aber trotz des geänderten FileFormat.
Die Abfrage wegen ggf. Überschreiben kommt korrekt.
Ich sende Ihnen als Anlage das angepaßte Makro als Word-Datei.
Die erzeugte Exelmappe(Rechnung) wird bei mir nicht geschlossen, weil sie gleich weiterbearbeitet werden soll. Die Vorlage-Mappe dagegen schließe ich nach dem NeueRechnung_Speichern, sie soll nicht versehentlich überschrieben werden. Das funktioniert wie gewünscht.
Woran erkenne ich, ob die Schaltfläche Typ Formular oder Active-X ist ?
Das zugehörige Modul heißt Tabelle1 und Tabelle2, es sind CommandButton1 und CommandButton2.
Vielen Dank nochmals.
Rudolf Nitze
Sehr geehrter Herr Nitze,
Schaltflächen vom Typ „Formular“ erkennen Sie daran, dass der Mauszeiger die Form einer Hand annimmt, wenn Sie ihn über die Schaltfläche führen. Bei Schaltflächen vom Typ „ActiveX“ nimmt der Mauszeiger die Form eines nach links oben zeigenden Pfeils an.
Steuerelemente vom Typ „Formular“ entfernen Sie mit der folgenden Schleife:
For Each sh In wbBlatt.Worksheets(1).Shapes
If sh.Type = msoFormControl Then sh.Delete
Next sh
Bei Steuerelementen vom Typ „ActiveX“ sieht die Schleife so aus:
For Each sh In wbBlatt.Worksheets(1).Shapes
If sh.Type = msoOLEControlObject Then sh.Delete
Next sh
Nun geht aber aus Ihrer Problembeschreibung ziemlich eindeutig hervor, dass Ihre Schaltflächen vom Typ „ActiveX“ sind.
Es ist daher leider nicht damit getan, die Schaltfläche zu entfernen. Sie müssen auch die CommandButtonX_Click-Prozedur aus dem Code-Modul des jeweiligen Tabellenblattes entfernen. An diese Prozedur hatte ich leider bei meiner letzten Mail nicht gedacht.
Es gibt jetzt 2 Möglichkeiten, eine relativ einfache und eine etwas kompliziertere:
1. Die einfache Möglichkeit
Sie ersetzen die existierenden Schaltflächen durch Schaltflächen vom Typ „Formular“ (Register „Entwicklertools“ > Gruppe „Steuerelemente“ > Menü „Einfügen“ > Schaltfläche aus der oberen Gruppe verwenden). Sie ordnen der Schaltfläche folgendermaßen das auszuführende Programm zu:
Rechtsklick auf die Schaltfläche > Kontextmenüeintrag „Makro zuordnen…“).
Anschließend löschen Sie noch manuell die oben genannte CommandButtonX_Click-Prozedur aus dem Code-Modul des jeweiligen Tabellenblattes (im Projektexplorer Doppelklick auf „TabelleX(Rechnung …)“ und die Prozedur löschen.
2. Die kompliziertere Möglichkeit
Sie behalten Ihre ActiveX-Schaltflächen bei und löschen die oben genannte CommandButtonX_Click-Prozedur per Programm. Leider ist es dafür erforderlich, dass auf jedem Rechner, auf dem dieses Programm ausgeführt werden soll, bei den Makroeinstellungen (Datei > Optionen > Trust Center > Schaltfläche „Einstellungen für das Trust Center…“ > Makroeinstellungen) die CheckBox „Zugriff auf das VBA-Objektmodell vertrauen“ eingeschaltet sein muss.
Das gesamte Programm sieht dann folgendermaßen aus:
Sub Speichern()
Dim blattName As String
Dim cm As Object ´ VBIDE.CodeModule
Dim pfad As String
Dim sh As Shape
Dim vbc As Object ´ VBIDE.VBComponent
Dim wbBlatt As Workbook
Dim ws As Worksheet
pfad = ThisWorkbook.Path & „“
Set ws = ActiveSheet
blattName = ws.Name
ws.Copy
Set wbBlatt = ActiveWorkbook
Set ws = wbBlatt.Worksheets(1)
´ ActiveX-Steuerelemente aus dem Blatt ws entfernen
For Each sh In ws.Shapes
If sh.Type = msoOLEControlObject Then sh.Delete
Next sh
´ CommandButtonX_Click-Prozedur aus dem Codemodul von
´ Blatt ws entfernen
For Each vbc In wbBlatt.VBProject.VBComponents
If vbc.Type = 100 Then ´ vbext_ct_Document = 100
If vbc.Name = ws.CodeName Then
Set cm = vbc.CodeModule
cm.DeleteLines Startline:=1, _
Count:=cm.CountOfLines
Exit For
End If
End If
Next vbc
´ Arbeitsmappe mit dem einzelnen Blatt speichern
wbBlatt.SaveAs Filename:=pfad & blattName & „.xlsx“, _
FileFormat:=xlOpenXMLWorkbook
wbBlatt.Close
End Sub
Mit freundlichen Grüßen
Dieter Klemke
Sehr geehrter Herr Klemke,
ich danke Ihnen herzlich für die umfassende Erläuterung, ich habe wirklich viel von Ihnen lernen können.
Ich habe die einfache Mehode gewählt und die ActiveX-Elemente durch Formular-Elemente ersetzt. Das Ergebnis ist wie gewünscht. Habe allerdings nicht herausbekommen, wie man ein ActiveX-Element löscht, habe im Entwicklertools
mit Rechtsklick „Ausschneiden“ gewählt und dann ganz schnell das Workbook gespeichert. Danach war es weg. Gibt es einen offiziellen Weg ?
Die Abfrage wegen der Makrospeicherung kommt aber immer noch, habe alle Ihre Ratschläge befolgt. Siehe Anlage mein aktuelles Makro MakeRechnung.
Seltsamerweise habe ich eine weitere Datei mit anderen Rechnungsvorlagen, da habe ich exakt die gleichen Features eingebaut, und da kommt die Abfrage nicht, egal ob mit oder ohne FileFormat.
Mir fällt dazu nichts mehr ein. Muss man vielleicht die Makros im neuen Sheet vor der Speicherung explizit löschen ?
Also nochmals meinen herzlichen Dank.
Rudolf Nitze
Sehr geehrter Herr Nitze,
vielen Dank für Ihre freundliche Rückmeldung.
1. Zur Entfernung der Schaltfläche
Eine ActiveX-Schaltfläche können Sie am einfachsten folgendermaßen entfernen:
Sie wählen das Register „Entwicklertools“ und klicken in der Gruppe „Steuerelemente“ einmal auf die Schaltfläche „Entwurfsmodus“. Die Schaltfläche nimmt daraufhin einen dunklen Hintergrund an, um anzuzeigen, dass Sie sich im Entwurfsmodus befinden. Wenn Sie jetzt mit dem Mauszeiger auf die Schaltfläche zeigen, dann erscheint am Mauszeiger zusätzlich ein Pfeilkreuz. Durch Klick auf die Schaltfläche können Sie sie markieren und dann mit der Entf-Taste entfernen. Anschließend schalten Sie durch einen erneuten Klick auf die Schaltfläche „Entwurfsmodus“ den Entwurfsmodus wieder aus.
2. Abfrage wegen Makrospeicherung
Ihr Code ist nicht die Ursache für diese Abfrage, der ist OK.
Ich vermute, dass Sie die CommandButton_Click-Prozedur nicht aus dem Code-Modul Ihrer Rechnungsvorlage entfernt haben.
Um das zu tun, wechseln Sie zum VBA-Editor machen dort im Projektexplorer einen Doppelklick auf den Eintrag zu Ihrem Tabellenblatt. Daraufhin sehen Sie den zugehörigen Code und löschen diesen.
Das müssen Sie für alle Tabellenblätter machen, bei denen eine ActiveX-Schaltfläche vorhanden war.
(Die ActiveX-Schaltflächen haben immer eine Art Brückenkopf im Code-Modul der Tabelle, bei den Formular-Schaltflächen ist das nicht der Fall.)
Falls Ihr Problem damit nicht gelöst ist, können Sie mir gern Ihre Arbeitsmappe schicken, verändert und/oder anonymisiert (dieter.klemke@web.de).
Mit freundlichen Grüßen
Dieter Klemke
Sehr geehrter Herr Klemke,
vielen Dank für die schnelle und ausführliche Rückmeldung zu später Stunde. Offensichtlich sind Sie auch ein Nachtarbeiter, willkommen im Club.
Die verbliebenen CommandButton-Module hatte ich schließlich auch gefunden und beseitigt. Bei dem anderen Vorlagen-Workbook, bei dem die störende Rückfrage nicht kam, hatte ich wohl von vornherein eine Schaltfläche Typ Formular eingebaut, deshalb
das unterschiedliche Verhalten.
Vorher hatte ich es mit Application.DisplayAlerts = False versucht, aber das hat mir auch den Überscheiben-Dialog unterdrückt. Da habe ich mich entschlossen, die Existenz der neuen Rechnungsdatei vorher selbst abzufragen, und das hat zu einem besser strukturierten Modul geführt, wo man bei Ablehnung des Überschreibens sauber zur Ausgangslage zurückkehrt.
Jetzt läuft wirklich alles wie ich es mir vorgestellt habe, zum Wesentlichen dank Ihrer Unterstützung. Ich füge jetzt nochmal das Maro MakeRechnung in der hoffentlich endgültigen Form bei.
Das Einzige, das mit nicht gefällt, ist dass die Formular-Schaltfläche auf einen einfachen Linksklick reagiert und nicht erst auf einen Doppelklick. Naja, damit kann ich dann leben.
Nochmals vielen herzlichen Dank für Ihre umfassende Hilfe.
Rudolf Nitze
Sehr geehrter Herr Nitze,
vielen Dank für die ausführliche Rückmeldung und die Übersendung Ihres Programms.
Es funktioniert auch bei mir einwandfrei.
Es freut mich, dass ich Ihnen helfen konnte.
Ich wünsche Ihnen weiterhin viel Erfolg mit Excel und mit VBA.
Mit freundlichen Grüßen
Dieter Klemke