Supportnet Computer
Planet of Tech

Supportnet / Forum / Tabellenkalkulation

Makro ! brauche dringend hilfe !





Frage

Hallo Zusammen, ich muss ein Makro schreiben und bin darin blutiger Anfänger und komme gar nicht zurecht. Also es geht um folgendes: Ich habe gut 200 Datein deren Inhalt in ein Programm eingespielt werden muss. Diese Dateien haben aber nicht die richtige "Form" um sie einspielen zu können. Jetzt habe ich mir eine Datei in Excel mit der richtigen "Form" aufgebaut. Mit einem Makro möchte ich nun die benötigten Daten aus der Datei in die neue Form ziehen. Problem dabei ist, dass die 200 Datein (auch Exceldateien) zwar alle die gleichen Spalten haben aber unterschiedlich viele Zeilen. Zum Ablauf des Makro: Das Makro soll in der neuen Datei eingebaut werden, also in diejenige, die die passende Form enthält, um die Daten in das Programm einspielen zu können. Ich nenne die Datei jetzt der Einfachheit halber mal Plan_1. Also Plan_1 enthält die neue Spaltenanordnung. Nach einem klick auf einen Button startet das Makro. Es soll sich ein Fenster öffnen, das den Ordner mit den 200 Dateien enthält die in die neue Form gebracht werden sollen. Ich makiere die Datei und die Daten werden in Plan_1 kopiert. Problem bei der Sache ist, wie vorher schon erwähnt, dass die 200 Dateien unterschiedlich viele Zeilen haben. Sind alle Daten nach Plan_1 kopiert soll die Datei automatisch in einem neuen Ordner gespeichert werden. Dabei soll Plan_1 jedes mal automatisch unter einem neuen Namen speichern. Beispiel: Eine der 200 Dateien heißt Umsatz2006, dann soll die Datei automatisch als Umsatz2006_Plan_1 gespeichert werden. Gibt es da eine Möglichkeit, dass das Makro jedes mal diesen Zusatz an den ursprünglichen Dateinamen anhängt? Wenn die Datei gespeichert wurde, sollen alle Zeilen in Plan_1 wieder gelöscht werden und ein Fragefenster aufpoppen. z.B. "Weitere Dateien umwandeln?" =>ja oder nein. Klickt man dann auf ja, beginnt das ganze von vorn. Bei nein wird das Makro beendet. Habe schon stundenlang daran herumprobiert. Das größte Problem ist der Datenimport von der einen Exceldatei in die andere. Es wäre echt nett, wenn mir da jemand helfen könnte. Vielen Dank schon im Voraus.

Antwort 1 von coros

Moin BWL_Studi,

nachfolgendes Makro kopiere in ein StandardModul und weise es einer Befehlsschaltfläche zu.

Public Auslesedatei$, Verzeichniss$, Dateiname_Auslesedatei As String, _
FreieZeile As Long, Dateiname_neu As String, Dateiname As String
Sub Auslesen_Start()
Dateiname = ActiveWorkbook.Name
Anfang:
Verzeichniss = "C:\\"
On Error Resume Next
ChDrive Left(Verzeichniss, 1)
ChDir Verzeichniss
On Error GoTo 0
Auslesedatei = Application.GetOpenFilename("Exceldateien (*.xls), *.xls")
Application.ScreenUpdating = False
If Dir(Auslesedatei) <> "" Then
Workbooks.OpenText Filename:=Auslesedatei
Dateiname_Auslesedatei = ActiveWorkbook.Name
FreieZeile = Worksheets("Tabelle1").Range("A65536").End(xlUp).Offset(1, 0).Row
Worksheets("Tabelle1").Range(Cells(1, 1), Cells(FreieZeile, 2)).Copy
Workbooks.Add
Range("A1").PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Windows(Dateiname_Auslesedatei).Close (False)
ActiveWorkbook.SaveAs Filename:=Left(Auslesedatei, Len(Auslesedatei) - 4) & "_" & Dateiname
Dateiname_neu = ActiveWorkbook.Name
Windows(Dateiname_neu).Close (False)
Select Case MsgBox("Soll eine weitere Dateien ausgelesen werden?", vbYesNo, "Abfrage")
Case 6
GoTo Anfang
Case 7
Exit Sub
End Select
Else
Exit Sub
End If
End Sub


Bei obigen Makro wird das Fenster zum Öffnen von Exceldateien geöffnet. Dort kannst Du dann die Datei, deren Daten ausgelesen werden sollen auswählen. Die Datei wird dann geöffnet, die Daten, die in der Datei vorhanden sind werden kopiert. Dabei ist es unerheblich wieviele beschriebene Zeilen die Datei enthält. Das wird ermittelt. Danach wird eine neue Datei erstellt, in der die kopierten Daten hinein kopiert werden. Diese Datei wird dann unter dem Namen der geöffneten Datei plus dem Namen der Datei, aus dem das makro ausgeführt wurde, also wie von Dir gewollt, abgespeichert. Zum Schluss wird die ausgelesenen Datei ebenfalls geschlossen und es erscheint ein Fenster mit der Abfrage, ob weitere Dateien ausgelesen werden sollen. Du musst allerdings noch den Pfad in dem Makro anpassen. Standardmäßig wird im Moment auf Platte C zugegriffen. Wenn Du dort einen anderen Pfad benötigst, dann in dem Makro in der Zeile

Verzeichniss = "C:\\"

die Pfadangabe ändern.

Ich hoffe, Du kommst klar. Bei Fragen oder Problemen melde Dich.

Solltest Du nicht wissen, wie Du den Code in Deine Datei bekommst, dann schau mal auf meiner HP in der Rubrik Anleitungen und dort dann in der Anleitungsnummer 3 nach. Dort stelle ich dazu eine bebilderte Anleitung zur Verfügung, die Dir sicherlich helfen wird.

MfG,
coros
Da hier der einzige Lohn für die Helfer eine Rückmeldung ist, wäre es nett, wenn Du ein
Feedback abgeben könntest, ob der Lösungsvorschlag Dein Problem gelöst hat.

Antwort 2 von Timor

@BWL-Studi
Dort wo coros einen doppelten Backslash hat, gehört nur einer hin. Ist die Folge eines Darstellungsfehlers hier im Forum.

@coros
Der Backslashfehler im Supportnet ist jetzt behoben! ^^

Antwort 3 von coros

Moin Timor,

danke Dir für die Info. Da der Fehler sooooooooolange nicht behoben wurde, hatte ich mich schon daran gewöhnt immer einen doppelten Bakslash einzufügen.

MfG,
coros
Jeder macht was er will, keiner macht was er soll, aber alle machen mit.

Antwort 4 von BWL-Studi

Hallo Coros,

vielen Dank für Deine ausführliche Antwort. Ich kann das ganze erst morgen ausprobieren.

Allerdings bin ich mir nicht ganz sicher, ob ich mich bei meiner Problembeschreibung ganz deutlich ausgedrückt habe. Deine Antwort habe ich jetzt so verstanden, dass alle Daten, die in einer der 200 Dateien enthalten sind ausgelesen werden. Korrigiere mich, wenn ich da falsch liege. Ich wollte eigentlich vorher die Bereiche festlegen aus denen die Daten ausgelesen werden. Ich habe heute mal ein wenig rumgebastelt und bin auch noch ein wenig weiter gekommen. Es folgt das Makro, wie es im Moment aussieht (auch wenn es noch nicht so viel ist :-) ):

PrivateSub CommandButton_Click()

´Öffnen des Dateiauswahlfensters
datsuch = Application.GetOpenFilename

Workbook.Open Filename:=datsuch

´Aktivieren des Tabellenblattes der geöffneten Datei (also eine der 200)

Worksheets ("Data Input").Activate

Worksheets ("Data Input").Range ("O11:O2000).Select

Selection.Copy

´Einfügen der Daten in die richtig strukturierte Datei

Windows("Plan_1.xls").Activate


So bis hier hin gehts ganz gut. Ist ja auch nicht so viel.

Jetzt will ich die kopierten Daten in Plan_1 einfügen beispielsweise nach C11:C2000. Außerdem sollen nur Werte übertragen werden also keine Formate o.ä.

Leider weiß ich nicht, ob die Fragen, die ich hier nochmal stelle, schon in Deinem Makro enthalten sind, da ich Dein Makro nicht komplett verstehe.

Wie kann ich denn eigentlich prüfen, ob im Bereich der Zeile 6-4000 noch leere Zellen vorhanden sind?

Würde das gerne lösen in dem ich eine If...then Abfrage einbaue

z.b. Prüfe Zeile 6-4000, wenn ganze Zeile leer, dann löschen.

Ist das hier der Befehl für das Speichern der Datei?

ActiveWorkbook.SaveAs Filename:=Left(Auslesedatei, Len(Auslesedatei) - 4) & "_" & Dateiname

Vielleicht kannst Du ja nochmal was dazu schreiben? Vielen Dank schon mal im Voraus.

Antwort 5 von coros

Nabend BWL Studi,

mein Makro kopiert aus der Datei die ausgelesen werden soll den Bereich A1 bis B letzte beschriebene Zelle. Wenn Du einen anderen Bereich haben möchtest, dann musst Du dass in dem Makro in der Zeile

Worksheets("Tabelle1").Range(Cells(1, 1), Cells(FreieZeile, 2)).Copy

angeben. Die erste Zahl in der Klammer hinter Cells sagt die Zeile aus und die zweite Zahl die Spalte. Für A also eine 1, für B eine 2, für C eine 3 usw. Nun wundere Dich nicht, dass bei dem zweiten Cells nicht als erstes eine Zahl steht, sondern die Variable "FreieZeile". Die enthält die ermittelte Zahl für die letzte beschrieben Zeile. Das habe ich so gemacht, da Du geschrieben hattest, dass die Tabellen zwar die selbe Anzahl an Spalten, aber unterschiedlich von den Zeile her sind.

Dann zu dem Einfügen in den Bereich z.B. C11:C2000. Ich denke es sollen alle Daten kopiert werden, also von erster Zelle mit Werten bis zur letzten Zelle mit Werten. Das können doch dann eventuell auch mal mehr als 1989 Zeilen sein. Oder sehe ich das jetzt falsch. Wenn Du allerdings wirklich nur Zellen kopieren möchtest, die in den bereich C11:C2000 passen, dann muss das bei dem Copybefehl angegeben werden. Dann müsste also in dem Makro die zeile für das kopieren folgendermaßen lauten:

Worksheets("Tabelle1").Range(Cells(1, 1), Cells(1989 , 1)).Copy

Die Anweisung würde nun den Bereich A1:A1989 kopieren und der würde dann in den bereich C11:C2000 hineinpassen. Die Anweisung zum Einfügen müsste dann folgendemaßen lauten:

Range("C11").PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False


Die Anweisung zum Einfügen fügt die vorher kopierten Werte ab Zelle C11 ein. Das der letzte Wert dann in Zelle C2000 steht, ergibt sich daraus, dass eben nur der Bereich z.B. A1:A1989 kopiert wurde. Die Anweisung zum Einfügen fügt nur Werte, keine Formeln oder Formate ein. Das war aber auch schon so in dem Makro von heute morgen.

Um die leeren Zeilen zu löschen würde ich nicht eine If Abfrage einbauen, sondern eher das Blatt sortieren. Dann stehen die Leerzeilen eh am Ende und brauchen nicht gelöscht werden. Müsste jedenfalls so sein. Wenn Du allerdings unbedingt eine Abfrage haben möchtest, dann müsste man schon wissen, ob Du wirklich mit leer die ganze Zeile, also von A bis IV meinst, oder nur einen Teilbereich.

Zu Deiner Frage am Schluß, ja das ist die Anweisung, die die Datei unter dem Namen, den Du haben wolltest, abspeichert.

Ich hoffe, Du kannst damit was anfangen. Wenn nicht, dann schreib doch mal genau auf, was Du haben möchtest. Mein erstes Makro macht das, was Du in Deiner ersten Frage geschrieben hast. In Deiner zweiten Frage bringst Du nun Bereiche, also richtig definierte (O11:O2000, C11:C2000) mit rein, die Du kopiert haben möchtest. Wenn Du das schon in der ersten Frage erwähnt hättest, wäre das Makro auch entsprechend erstellt worden.

MfG,
coros
Da hier der einzige Lohn für die Helfer eine Rückmeldung ist, wäre es nett, wenn Du ein
Feedback abgeben könntest, ob der Lösungsvorschlag Dein Problem gelöst hat.

Antwort 6 von BWL-Studi

Hallo Coros,

erstmal vielen Dank für die Hilfe. Deine Tips haben mir wirklich weiter geholfen. Mein Makro funktioniert jetzt. Allerdings ist es wohl noch ein wenig plump.

Die Sache mit dem Kopieren habe ich jetzt so gelöst, dass ich die Bereiche aus denen kopiert werden soll, einfach genau definiert habe. Das klappt auch ganz gut.

Die letzten zwei Sachen, die ich jetzt noch einbauen möchte sind zum einen das automatische Speichern, was ich leider noch nicht hinbekommen habe, und nach dem Speichern die Abfrage, ob ich noch eine weitere Datei bearbeiten möchte.

Die Sache mit dem Löschen der leeren Zeilen habe ich auch gelöst, aber das ist nicht so toll. Ich habe das mit dem autofilter gemacht und das ist noch etwas holprig. Ich habe jetzt 24 Spalten in Plan_1 und ich möchte eine Zeile nur löschen, wenn keine Zelle einen Inhalt in diesem Bereich hat. Also wenn beispielsweise A6:X6 leer ist. Fänd da eine If...Then..Abfrage ganz gut. Vielleicht hast Du da aber eine bessere Idee. Die würde ich dann natürlich gerne aufgreifen :-).

Der Befehl für das Speichern hat leider nicht funktioniert. Ich denke der muss noch an meine Bedürfnisse angepaßt werden. Kannst Du mir da nen Tip geben? Es sind ja immer zwei Dateien geöffnet. Zum einen eine der 200 z.b. Plan2006.xls und die richtig strukturierte Datei Plan_1.xls. Kann man es so machen, dass Plan_1 beim Speichern nicht überschrieben wird z.b. mit dem saveas_copy befehl?
Der Dateiname soll ja immer automatisch generiert werden aus dem Name der Datei und aus Plan_1 z.B. Plan2006_Plan_1.xls
oder Plan2007_Plan_1.xls etc.
Hab das leider noch nicht hinbekommen.

Dann nochmal zu der Abfrage am Schluß, ob das Makro wiederholt werden soll. Gibt es da eine Möglichkeit das Tabellenblatt ab einem bestimmten Bereich z.B. ab A6:X6 zu löschen? Also: Weitere Dateien bearbeiten?
ja=>Inhalte Tabellenblatt löschen
nein=>Datei schließen
Die Abfrage soll übrigens erst nach dem Speichern durchgeführt werden.

Oje..ich glaube das klingt alles etwas konfus. Ich hoffe Du verstehst, was ich meine. Ansonsten können wir auch mailen. Das wäre für mich etwas besser.

Übrigens sorry, dass ich mich wohl bei den letzen Beiträgen etwas unklar ausgedrückt habe. Beim Schreiben schießt einem immer wieder was in den Kopf und dann drückt man es nicht immer ganz klar aus. Danke, dass Du Dich trotzdem mit meinem Problem beschäftigst.

Antwort 7 von coros

Moin BWL_Studi,

es wäre sicherlich hilfreich gewesen, wenn Du Dein Makro, so wie es im Moment ist, mal mit in Deinem Beitrag aufgeführt hättest. Dann wüsste man, was Du da im Moment alles zu stehen hast.

Zu dem Prüfen, ob ein Bereich leer ist: Das kannst Du mit nachfolgender Anweisung erledigen.

If Application.CountA([A6:X6]) = 0 Then
Rows(6).Delete
End If


Das ganze sollte allerdings in eine Schleife verpackt werden, denn ich glaube nicht, dass Du immer nur die Zeile 6 abfragen möchtest. Dass würde dann folgendermaßen aussehen:

For Wiederholungen = 2 To Range("A65536").End(xlUp).Offset(1, 0).Row
If Application.CountA(Range(Cells(Wiederholungen, 1), _
Cells(Wiederholungen, 24))) = 0 Then
Rows(Wiederholungen).Delete
End If
Next


Warum die Anweisung mit dem Speichern bei Dir nicht funktioniert, kann ich nicht nachvollziehen. Um das nachvollziehen zu können fehlt mir, wie schon oben angedeutet, Dein Makrocode.

Das Daten nach einer Abfrage gelöscht werden, ist kein Problem. Du musst dann, wenn ich mich auf meinen VBA Code aus Antwort 1 beziehe, die Anweisung

GoTo Anfang

gegen den Löschbefehl tauschen.

Du kannst mir auch gerne Deine Datei schicken und ich schau dann mal, warum das mit dem Speichern nicht funktioniert usw. Also wenn Du willst, schicke die Datei an meine E-Mail:coros@excelbeispiele.de. Binde in die Betreffzeile bitte das Wort Supportnet mit, da ich alle Mails, deren Absender ich nicht kenne, ungelesen lösche.

MfG,
coros
Da hier der einzige Lohn für die Helfer eine Rückmeldung ist, wäre es nett, wenn Du ein
Feedback abgeben könntest, ob der Lösungsvorschlag Dein Problem gelöst hat.