VisualBasic Excel Formular mit Makros und Zellschutz, als Kopie ohne beides erstellen

[schnuff]

Hallo Forum,

ich benötige folgende Hilfestellung! Ich habe ein Excel Formular (2013) mit versch. Makros und Befehlsschaltflächen, sowie Zellschutz. Dieses möchte ich als Kopie in ein anderes Verzeichnis unter C:\Desktop\ unter Angabe des Nachnamen und Rechnungsnummer speichern.

Nochmals detailliert:

* Makros und Befehlsschaltflächen entfernen. (Formatierung beibehalten)
* Zellschutz aufheben
* Speicherort "C:\Desktop\Nachname_Rechnungsnummer"
* Speichern der Datei unter dem Nachnamen (steht an 2. Stelle nach dem Vornamen in Spalte A3) und Rechnungsnummer in Spalte H4.

Wer kann mir mit einem Lösungsansatz helfen?

Im Voraus für euren Beitrag vielen Dank.

schnuff

 

[the_nobs]

Um die Makros zu entfernen sollte es reichen das formular als .xlsx abzuspeichern (das darf keine Macros beinhalten, die Version mit Macros heißt .xlsm)

Zellschutz aufheben -> ohne password nicht einfach, :-)
speicherort/Namen -> Macro schreiben :-) , sollte recht einfach umzusetzen sein.

 

[schnuff]

Vielen Dank für den Beitrag, jedoch nicht sehr zielführend. Das Formular unter .xlsx abzuspeichern war mir bekannt.
Ich verfolge das Ziel mein Formular mit den o.a. Vorgaben speichern zu können.

Vlt. gibt es ja noch weitere Lösungsvorschläge?

schnuff

 

[Wes2000]

Du willst in dein Formular Daten eigeben und die sollen dann gespeichert werden, je Datensatz in einer eigenen Datei?

Du erstellst einen weiteren Tap (Ausgabe-Tap), wo du die Daten aus dem Eingabe-Tap automatisch übertragen lässt und kannst diesen Tap so formatieren, wie du es dir vorstellst.
Beim Speichern drücken auf dem Eingabe-Tap, kann dann ein Speicher-Macro den Ausgabe-Tap dann so abspeichern wie du möchtest.

So würde ich es als Idee versuchen.
Die Programmierung so eines Macros geht leider über meine Fähigkeiten, aber da gibt es ja fähige Helferlein hier oder speziellen Excel-Foren.

 

[SOL Solutions]

Hey schnuff, immer noch am Rumbasteln?

Was fuer Steuerelemente verwendest Du? Ganz normale Buttons, oder AktivX Elemente?

Edit: Das ist der Basis Code auf welchem Du aufbaust.

Public Sub Speichern()
Dim neu As Workbook
Set neu = Application.Workbooks.Add

ThisWorkbook.Sheets(1).Range("A1:E13").Copy
With neu.Sheets(1).Range("A1")
    .PasteSpecial xlPasteAll
    .PasteSpecial xlPasteColumnWidths
End With

neu.SaveAs "C:\users\.....\desktop\test9.xlsx"

neu.Close False
Set neu = Nothing
End Sub

Deine Aufgabe ist es jetzt die Sub bei dir einzubauen und den richtigen Dateinamen zu Uebergeben. Des weiteren brauchst Du noch ne Kontrolle ob der Dateiname schon besteht und was passieren soll wenn ja. Die buttons werden hierbei nicht kopiert und der Zellschutz wahrscheinlich auch nicht, hab ich aber noch nicht getestet.

Gruss
SOL

 

[schnuff]

Hi Sol,

sorry für das späte Feedback. Danke für deinen Code. Jedoch liefert er die Meldung 'Die Methode SAVE AS für das workbook ist fehlgeschlagen. Des Weiteren sollte er das Tabellenblatt "Rechnung" kopieren. Er liefert immer das 1. Tabellenblatt (mein Mastersheet).

VG
schnuff

 

[SOL Solutions]

Hi schnuff,

um das Loesen zu koennen, muesste ich wissen wie Du meinen Code in dein Program integriert hast. Wie ich geschrieben hatte, musst du noch dafuer sorgen, dass der richtige Dateiname uebergeben wird und dass es eine Kontrolle gibt's ob dieser bereits existiert.

Wie auch immer, die SaveAs Funktion wirft einen Fehler aus wenn du z.B. keinen Schreibzugriff auf den Ordner hast in welchen du speichern moechtest, oder der Pfad nicht gefunden werden konnte. Ich denke, unzulaessige Zeichen fallen hier auch noch rein. Du musst auf jeden fall alles zwischen den "" durch deinen eigenen Pfad\Dateinamen ersetzen, entweder hardcoded, oder eben variabel.

Welche Tabelle er am Ende speichert bleibt dir ueberlassen. Ich habe aus Testzwecken einfach eine 1 reingeschrieben, fuer die erste Tabelle im Workbook. Das kann aber alles sein. Entweder nimmst du die Indexnummer welche deine Tabelle Rechnung repraesentiert, oder du schreibst Sheets("Rechnung").

Ich denke, dass dies auch eine gute Uebung ist um zu sehen wie eigentlich alles zusammenarbeitet. Ehrlich gesagt, haette ich von dir schon erwartet, dass du den "Fehler" mit dem Tabellenblatt selbst loesen kannst . Du hast hoffentlich nicht einfach meinen Code kopiert und 1 zu 1 so bei dir eingebaut. Denn das kann nicht zu 100% funktionieren. Alleine weil die Range, welche kopiert wird, nur bis Zeile 13 geht.

Also:

ThisWorkbook.Sheets(1).Range("A1:E13").Copy

Muss auf dein Arbeitsblatt Rechnung angepasst werden. Moeglicherweise reicht es sogar einfach nur die Spalten zu kopieren. Z.b. "A:E".

neu.SaveAs "C:\users\.....\desktop\test9.xlsx"

Muss mit deinem Zielpfad gefuetter werden. So wie es jetzt ist, funktioniert es nicht. Du kannst da einfach eine string Variable nehmen welche du vorher zusammen baust, oder ueber einen function call uebergibst.

Wenn es ums Pruefen des Dateinamen geht, hast Du mehrere Moeglichkeiten. Du kannst Excel anweisen einfach alles zu ueberschreiben, ohne zu fragen, oder du laesst Excel vorher fragen ob ueberschrieben werden soll. Dann musst Du allerdings noch eine Benutzereingabe einbauen, fuer den Fall dass ein alternativer Filename gebraucht wird. Die eleganteste Loesung ist, denke ich, einfach eine 1 anzuhaegen wenn die Datei schon existiert. Aber was ist wenn es diese Datei dann auch schon gibt? Eine 2?

Wie du siehst, kann man hier noch sehr tief gehen und dabei geht es nur um eine Nebenaufgabe. Am Besten beginnen wir damit das du mal zeigst wie du meinen Code implementiert hast und wie dieser ausgeloest wird.

Gruesse
SOL

Edit: Also wie es aussieht war ich da wohl ein wenig uebereifrig was die Tiefe das Problems anbelangt. Microsoft hat dafuer natuerlich genuegend Optionen parat. Einfach mal einen Blick in die Beschreibung werfen. Save As, Microsoft Handbuch (Englische Version)

 

[schnuff]

Hi SOL,

du hast dir wieder mal richtig viel Mühe gegeben. Lass es uns zusammen versuchen es zu lösen. Jedoch sind meine VBA Kenntnisse höchstens Kreisliga und ich benötige deine tatkräftige Unterstützung.
So nun der Reihe nach!
Ich habe natürlich den Workbook und SaveAs Code auf meine Ordnerstruktur angepasst. Im Anhang sende ich dir meinen eingestellten Code und das Original Formular zur Ansicht, mit der Bitte um Korrektur etc.

VG
schnuff

 

[SOL Solutions]

Aha, da haben wir schon mal das erste Problemchen. Dein Pfad ist unvollstaendig. Dort muss dann am Ende der komplette Pfad + Dateiname rein. Momentan versuchst du eine Datei namens .xlsx zu erstellen. Das funktioniert natuerlich nicht.

Warum ist bei Dir eine Zeile gelb markiert? Bekommst Du an de Stelle eine Fehlermeldung?

 

[schnuff]

Den Pfad habe ich vervollständigt.
Fehlermeldung des markierten Bereichs lautet: Laufzeitfehler 9 (Index außerhalb des gültigen Bereichs.
Ergänzung zum Code! Ich möchte die Datei in das sheet Copy Rechnung (Tabellenblatt 13) kopieren. Speichern soll es mir in der Reihenfolge Nachnamen+1.Vorname+Rechnungsdatum aus Spalte A3.

schnuff

A3.

 

[SOL Solutions]

Sorry, aber jetzt hast Du mich verloren Ich dachte Du moechtest auf einen Knopf druecken und dann soll eine neue Datei erstellt werden, in welcher die Rechnung abgelegt wird. Wo kommt jetzt ploetzlich die Tabelle Copy Rechnung her?

 

[Makkaroni]

...

Warum ist bei Dir eine Zeile gelb markiert? Bekommst Du an de Stelle eine Fehlermeldung?

... er erzeugt eine neue Exceldatei. Dann versucht er dort das Arbeitsblatt "Copy Rechnung" anzusprechen. Das gibt es aber nicht. Daher Fehlermeldung an dieser Stelle!

Nachtrag:
Wenn man eine Exceldatei neu öffnet, gibt es 3 Arbeitsblätter. Ich habe ein englisches Excel. Das erste Arbeitsblatt heißt dort "Sheet1" aber nicht "Copy Rechnung". Also entweder erstmal das Arbeitsblatt bennenen. Oder aber den Name "Tabelle1" verwenden. (Ich glaube das ist im deutschen Excel der Standard).

 

[SOL Solutions]

Das dachte ich auch erst, allerdings moechte er explizit Tabelle13 ansprechen, also offensichtlich nicht in der neuen Datei.

Edit: Es gibt natuerlich einen Unterschied zwischen dem was man will und dem was man tatsaechlich tut. Was den Fehler verursacht ist mir schon klar. Die Frage ist doch vielmehr, was moechte schnuff hier erreichen.

 

[schnuff]

Hi Makkaroni und SOL,

@Makkaroni, schön von dir zu hören.

Erreichen möchte ich auf einfachem Weg, meine originale Rechnung aus sheet (Rechnung) als Kopie in meinen Ordner
"c\bla bla bla\ ohne Makros, ohne Zellschutz und in der gleichen Formatierung mit dem Dateinamen Nachname+1.Vorname+Rechnungsdatum aus Spalte A3+H2 (sheet Rechnung) abzulegen. Das sheet Copy Rechnung habe ich als Schritt zum kopieren angelegt, ist aber unwichig. Wir können gerne den Namen Tabelle1 verwenden. Nur auf welchem Weg?

Danke für weitere Lösungssansätze.

 

[SOL Solutions]

Na dann wuerde ich das doch bei sheets(1) belassen. Man koennte es auch umbenennen, aber das ist nur eine Nebensaechlichkeit. Ich schreib morgen nochmal was zum Dateinamen, wenn Makkaroni bis dahin nicht schon was hat. Hier ist es inzwischen 1:30 und ich kann nicht mehr richtig gerade aus schauen :-)

Gute Nacht

 

[schnuff]

Hi SOL,
lass dir Zeit und geh schlafen. Bis morgen.....hab es gut.

VG
schnuff

 

[Makkaroni]

Also

with neu.sheets("copy rechnung")

kann es in einer neu erstellten Excel-Datei nicht geben!

Alternativen wären:
with neu.sheets("Tabelle1") (wenn Excel = deutsch)
oder

with neu.sheets(1) (geht immer da das erste Arbeitsblatt angesprochen wird, welches immer vorhanden ist)

Du kannst soetwas auch herausfinden indem du den Macro-Recorder aktivierst und dein Arbeitsblatt speicherst.

Anmerkung:
Wenn man eine Exceldatei, die Macros enthält, als xlsx-Datei speichert, gibt es eine Warnmeldung, die anmerkt, das Macros verloren gehen. In diesem Fall muesste man also die Warnmeldungen vorher deaktivieren.
Da du aber eine neue Datei erstellst, in die du ein Arbeitsblatt kopierst, umgehst du diese Hürde (ich nehme an, dass die Macros nicht im zu kopierenden Arbeitsblatt enthalten sind).

Für Fortgeschrittene gibt auch es die Möglichkeit Word zu verwenden.
D.h. man erstellt eine Exceldatei mit bestimmten Spalten. In Word verwendet man die Serienbrief-Funktion. D. h. man erstellt bestimmte Felder, die dann via Excel befüllt werden können.
Aber das ist dann mindestens Regional-Liga ...

Ergänzung (1. Dezember 2016)

1. Dim neu as Workbook
2. set neu = Application.Workbooks Add
3. thisworkbook.sheets("Rechnung").Range("A1:I38").copy
4. With neu.sheets("Copy Rechnung").Range("A1")
5. .pasteSpecial xlPasteAll
6. end with
7. neu.SaveAs "C:\Users\s\Desktop\Rechnungstool DonBo Original\NeuesDokument.xlsx"


In Zeile 3 werden die Zellen A1:I38 der Quell-Exceldatei des Arbeitsblattes "Rechnung" kopiert.
Das sieht noch gut aus.
In Zeile 4 wird ein Arbeitsblatt der Ziel-Exceldatei angesprochen, welches es nicht gibt.

Dieses sollte ersetzt werden durch:
With neu.sheets(1).Range("A1:I38")

Ein weiterer Fehler besteht - wie schon bemerkt - darin, dass kein Name für das neue Dokument vergeben wird.

Der Name soll ja aus den Werten der Zelle A3 + A2 gebildet werden:
Annahme:
Name = thisworkbook.sheets("Rechnung").Range("A3")
Vorname = thisworkbook.sheets("Rechnung").Range("A4")
Dann muss der Dateiname wie folgt gebildet werden:
Dateiname=
"C:\Users\s\Desktop\Rechnungstool DonBo Original" & thisworkbook.sheets("Rechnung").Range("A3") & ","
& thisworkbook.sheets("Rechnung").Range("A4") & ".xlsx"

Dann speichern unter ausführen:
neu.SaveAs Dateiname

 

[schnuff]

Hallo Makkaroni,

vielen Dank für deinen schnellen Beitrag und die Hilfestellung.
Mein Ziel wird sein, die Quali für die Regionalliga zu schaffen, aber das kann dauern.

Zum Sachverhalt!
Dein Code läuft bis .Range ("A3) danach kommt Fehlermeldung (siehe Anhang).
Kann ihn aber leider nicht identifizieren. Die Formatierung der Copy auf sheet 1 ist auch nicht der Weisheit letzter Schluss (fehlen Spalten).
Wir müssten nochmals nachjustieren.

VG
schnuff

Anhang anzeigen 594489

 

[SOL Solutions]

Hey schnuff, Dein Anhang funktioniert leider nicht. Kann es sein, dass Du die Variablen nicht deklariert hast und der Compiler deshalb bei Name ein Problem hat?

 

[schnuff]

Hi SOL,

ich habe Makkaronis Code verwendet und so eingestellt wie im Anhang. Geht auch bis .Range A3. Danach nicht mehr und auch die Formatierung müsste man anpassen. Wie müsste er denn richtig
eingestellt lauten?


schnuff