Office: Ein Verzeichnis, mehrere Excel Dateien und Summe von Zellen addieren

Hallo Michael,

hab die Datei mal neu gezogen.. Irgendwo ist hier der Wurm drin. Das Ergebnis ist wieder dasselbe, nur eine Auflistung der Dateipfade.

Hier mal der Code aus VBA

Private Function SearchFiles(SearchPath As String, SearchFileMask As String) As Collection

Dim Result As New Collection

Dim objFileSystem As Object
Dim objFolderContent As Object
Dim objFile As Object

Set objFileSystem = CreateObject("Scripting.FileSystemObject")
Set objFolderContent = objFileSystem.GetFolder(SearchPath)

For Each objFile In objFolderContent.Files
If objFile.Name Like SearchFileMask Then
Result.Add SearchPath & "\" & objFile.Name
End If
Next

MsgBox Result.Count & " Files found"
Set SearchFiles = Result

End Function

Private Sub CommandButton1_Click()

Dim FileList As New Collection
Dim n As Integer

Set FileList = SearchFiles(Range("b1"), Range("b2"))

Range("b7:b" & Rows.Count).Clear
For n = 1 To FileList.Count
Range("b" & n + 6).Value = FileList(n)
Next n

End Sub

Ich finde hier nichts von Tabellenblättern.

 

Moinsens!

Ich wunderte mich schon, daß er die Anzahl per MsgBox ausgegeben hat. Die Zeile ist schon seit längerem auskommentiert. Sehr merkwürdig. Irgendwie ignoriert er meinen Upload und ersetzt ihn immer durch eine alte Version.

Ich mach jetzt mal ein zip daraus...

Gruß, Michael

 

Hallo Michael!

Jetzt hat es geklappt und ich muss sagen, ich bin begeistert!! Das gesamte Blatt Januar ist mit den Zahlen gefüllt! Jeder einzelne Bereich bzw. Range! Allerdings wie von dir angedeutet nur der Januar... Aber die anderen Monate werde ich schon hinbekommen. Von der Berechnung her gebe ich dir Recht, dauert etwas. Aber das ist nicht tragisch.

Wie kann ich nun eine Schleife einziehen die Tabellenblatt für Tabellenblatt durchgeht? Den Code von Januar müsste ich ja nur anpassen aber eben wie bring ich das Makro dazu dann mit Februar weiter zu machen?

Und das Wichtigste, wie bedanke ich mich bei dir dafür!?

LG

Thomas

 

Eine Sache ist mir aufgefallen...

Sämtliche "normale" Formeln im Blatt Januar sind verschwunden. So als habe das VBA die Formel ausgeführt, das Rechenergebnis genommen und als Text wieder eingefügt. Merkwürdigerweise sind aber vorher Berechnungen erfolgt da der geschriebene Werte ein Ergebnis ist.

 

Moinsens!

Ja, das schrieb ich ja: Mit dem Teil ist noch etwas Finetuning notwendig, damit ggf. Formeln "drumherum" erhalten bleiben, wenn es notwendig sein sollte. Es war mir für diesen "Probetest" nur etwas aufwändig, das auseinander zu fisseln, deswegen habe ich mal pauschal das ganze Blatt kopiert und die Werte wieder eingefügt. Das müßte dann aber ggf. in mehreren VBA-Zeilen auf die Bereiche angewendet werden, die gefüllt werden.

Das Ersetzen der Formeln durch feste Werte passiert aber tatsächlich erst ganz am Ende. Dadurch sollten bis dahin die Formeln eben die aktuellen Ergebnisse berechnen.

Eine Anpassung darauf, daß alle Monate von Januar bis Dezember durchlaufen werden, ist jetzt nicht mehr allzu aufwändig. Primär ist dazu eine äußere Schleife notwendig (1..12), eine Umsetzung der Schleifen-Variable auf den Monats- und damit Blatt-Namen und das war's dann auch schon fast. Das Aufbereiten des Übersichts-Blatts wird (jetzt schon) in einer ausgegliederten Routine erledigt, die nach einer kleinen Anpassung mit dem Monats-/Blatt-Namen als Parameter aufgerufen werden kann. In einem ersten Schritt müßte geändert werden:

Code:

'Private Sub Test_fuer_Uebersichtsblatt() --> ändern in
Private Sub Uebersichtsblatt(sht As String)

    'Set s1 = Worksheets("Januar") --> ändern in
    Set s1 = Worksheets(sht)

    'Test_fuer_Uebersichtsblatt --> ändern in
    Uebersichtsblatt "Januar"

Damit sollte das Ding unverändert das gleiche wie vorher erledigen. (Tut es, hab ich eben ausprobiert.) Jetzt gilt es noch, den "Januar" ebenfalls variabel zu gestalten:

Von der Umsetzung her am einfachsten wäre es wohl, "ganz außen" herum eine Schleife zu setzen, die den bisherigen Ablauf einfach 12x mit unterschiedlichem Monats-Namen durchläuft. Das würde aber bei z.B. 30 Mitarbeitern bedeuten, daß eben die einzelnen Mappen insgesamt 12x30 = 360x geöffnet und wieder geschlossen werden müssen. Das ist dann von der Performance her sicher nicht mehr so schön. Aber eben, wie gesagt: Am einfachsten umzusetzen.

Schöner wär's da, in der inneren Schleife die 12 Formeln in einem Rutsch zusammenzubauen und dadurch die Datei-Operationen wieder auf 30 zu belassen. Der Verwaltungsaufwand ist dann etwas größer, aber die Performance würde es danken. ;-)

In beiden Varianten ist aber Vorausetzung: Die Monats-Blätter müssen alle, so wie jetzt das "Januar"-Blatt schon, bereits in der Mappe vorbereitet liegen.

Am einfachsten _und_ schnellsten ist, wenn eine Annahme getroffen werden kann: Wenn die Routine eine Mappe erfolgreich öffnet und in der Mappe ein Blatt "Januar" findet --> Darf dann davon ausgegangen werden, daß es eine Mitarbeiter-Mappe ist und auch die Blätter "Februar" bis "Dezember" enthält? Wenn ja, dann braucht die 12er-Schleife nämlich nur um den kleinen Block am Ende gelegt werden, in dem die Formel zusammengebaut wird.

Ich schau mir mal an, was da am besten umzusetzen ist.

Gruß, Michael

 

Hallo Michael!

Ja, es ist wie du beschrieben hattest. Die Formeln werden überschrieben durch das kopieren. Aber, ich mache das tatsächlich anders.

Ich habe den Button bzw. das Script in meine Gesamt 2015 Tabelle als erstes Blatt angelegt. Die Werte werden dann nämlich schön in die echte Tabelle geschrieben... eben mit dem Nachteil des Formelverlusts. Aber, das kann ich auch anders lösen in dem ich die Daten Sammel Tabelle einzeln betrachte und dann selbst die Werte kopiere. So könnte man dieses "Problem" umgehen. Also damit kann ich locker leben.

Für die variable Gestaltung des Januars kann ich dir sagen, dass die Mitarbeiter Tabellen von Anfang an schon die Tabellenblätter Januar bis Dezember enthalten. Ich habe von Anfang an versucht das Jahr abzubilden mit allen notwendigen Formeln und Auswertungen die gebraucht werden. Deine Annahme ist dadurch richtig! Die Äußere Schleife ist in der Tat etwas ungünstig, da jedes Mal beim Aufruf einer MA Tabelle die Frage nach der Aktualisierung von äußeren Daten kommt. Das kann ich auch nicht unterbinden. Daher macht wohl die innere Schleife mehr Sinn.

Viele Grüße

Thomas

 

Moinsens!

Ok, ich hab's dann mal versucht, so aufzubauen:
erste Schleife:
- Mit den Angaben in A1 und A2 werden die Dateien durch-/gesucht.
- Wenn eine Mappe geöffnet werden kann und das Blatt "Januar" enthält, wird es in eine Liste der "gültigen" Mappen aufgenommen.
(Dabei wird davon ausgegangen, daß die Blätter "Februar" bis "Dezember" ebenfalls enthalten sind. Dies wird nicht weiter abgeprüft.)
zweite Schleife:
- Über die 12 Monate wird eine "Master"-Formel aufgebaut
- Diese wird dann in das monatliche Übersichts-Blatt kopiert und auf die Bereiche gezogen
(Dabei wird davon ausgegangen, daß die Blätter "Januar" bis Dezember" alle bereits vorhanden sind. Dies wird nicht weiter abgeprüft.)

Die in das Monats-Blatt einkopierten Formeln werden am Ende durch feste Werte ersetzt - nur diese Formeln, nicht mehr das ganze Blatt. In der Routine "Übersichtsblatt" ist am Ende eine auskommentierte Zeile, die die Formel in C3 restauriert. Wenn Du Dir also nicht ganz sicher bist, ob das alles so richtig rechnet, nimm das ' am Anfang der Zeile dort raus und überprüf die Formel.

Ich hab noch einige weitere Kommantar-Zeilen eingefügt. Wenn ein Kommentar mit "Debug:" anfängt, ist das ein untrügliches Zeichen dafür, daß die folgende(n) Zeile(n) nicht zwingend für den eigentlichen Ablauf sind, sondern nur Zwischenergebnisse zur Überprüfung ausgeben sollen. Diese Zeilen können dann ggf. später mit einem ' auskommentiert werden.

Viel Erfolg beim Testen. Für mich hier ist das "Trockenschwimmen", da ich ja nicht Deine ganzen Daten nachbilden kann. Aber ich bin da mal recht zuversichtlich... ;-)

Gruß, Michael

 

Hallo Michael,

habe das neue File nun getestet und es rennt super durch! Die Werte passen und werden 1a in meine Gesamtdatei kopiert und beeinflussen nun nicht mehr die Originalformeln. Super Arbeit! Und wie zeige ich mich nun erkenntlich? Ein fettes Dankeschön!

Angenommen, ich möchte das Script um einen Bereich erweitern, was muss ich dazu beachten? Es könnte sein, dass neue Spalten hinzukommen. Auch unterhalb der Tabelle habe ich noch einen Bereich der mit berechnet werden könnte. Hier sind jedoch zwei Spalten Zellen zu einer zusammenfasst?

Kannst/Magst du das "erklären" ohne das ich große Programmierkenntnisse haben müsste?

Grüße

Thomas

 

Moinsens!

Klasse, das freut mich.

Die komplette Aufbereitung des Übersichts-Blatts ist in eine eigene kleine Routine ausgelagert. Diese basiert darauf, daß eine Formel für C3 fertig vorbereitet ist und kopiert diese nur noch in die passenden Bereiche. Gekürzt sieht die Routine so aus:

Code:

Private Sub Uebersichtsblatt(sht As String, frm As String)
    
    'Vorbereitung: Löschen der Bereiche

    s1.Range("c3:i33").ClearContents
    s1.Range("k3:k33").ClearContents
    [...]
    
    'Einkopieren der Formel und über Bereiche erweitern

    s1.Range("c3").Formula = frm

    s1.Range("c3").AutoFill Destination:=s1.Range("c3:i3"), Type:=xlFillValues
    s1.Range("c3:i3").AutoFill Destination:=s1.Range("c3:i33"), Type:=xlFillValues

    s1.Range("c3").Copy: s1.Range("k3").PasteSpecial Paste:=xlPasteFormulas: s1.Range("k3").AutoFill Destination:=s1.Range("k3:k33"), Type:=xlFillValues
    [...]

    'Ersetzen der Formeln durch feste Werte

    s1.Range("c3:i33").Copy: s1.Range("c3").PasteSpecial Paste:=xlPasteValues
    s1.Range("k3:k33").Copy: s1.Range("k3").PasteSpecial Paste:=xlPasteValues
    [...]

End Sub

Das Makro macht hier nichts anderes als das, was man auch "manuell" machen würde, um das zusammen zu stellen.

Zuerst wird der ganze Bereich, der später mit den Formeln gefüllt werden soll, gelöscht. Das ist nicht zwingend notwendig, aber schaden tut's auch nicht. Dazu wird einfach der Reihe nach jeder Bereich, wie z.B. C3:I33, mittels .ClearContents gelöscht. Kommen weitere Bereiche dazu, mußt Du also nur am Ende dieses Blocks eine entsprechende Zeile anfügen bzw. die "33" anpassen, wenn's weiter nach unten gehen soll.

Dann wird als nächstes die vorbereitete Formel in C3 eingesetzt, so wie man sie auch manuell eintippen würde.

Diese "Master"-Formel wird dann zunächst nach rechts bis I3 gezogen, so wie man es per Maus mit dem "Anfasser" am rechten unteren Zellrand machen würde. Dafür ist diese "AutoFill" Geschichte da oben zuständig. Das sorgt u.a. auch dafür, daß die Zeilen- und Spalten-Bezüge sich anpassen, sofern sie nicht mit "$" fixiert sind. Dann wird der Bereich C3:I3 nach unten bis Zeile 33 gezogen, auch wieder wie mit dem "Anfasser", nachdem man C3:I3 markiert hat.

Dann wird die Formel aus C3 nach K3 (und in der Folge in die weiteren Spalten bis AA3) kopiert und danach wieder nach unten bis Zeile 33 gezogen. Kommen also weitere Spalten dazu, müßtest Du auch hier eine weitere VBA-Zeile am Ende dieses Blocks anfügen.

Und der letzte Block sorgt nur noch dafür, daß die Formeln durch feste Werte ersetzt werden. Hier gibt es also für jeden Bereich, der am Anfang gelöscht und im 2. Block durch Formeln gefüllt wurde, auch wieder eine Zeile, die den Bereich kopiert (Strg-C) und danch über "Inhalte einfügen - Werte" mit den berechneten die Formeln überschreibt. Also auch hier wieder: neuer Bereich = entsprechende VBA-Zeile anfügen.

Also zusammenfassend: An den Stellen, wo oben ein [...] steht wäre der Ort, um weitere VBA-Zeilen einzusetzen, die zusätzliche Bereiche auf dem Übersichts-Blatt abdecken sollen.

Außerdem könnte man die Länge der Spalten, also was z.Z. bis Zeile 33 geht, auch für spätere Anpassungen etwas einfacher gestalten:
Gleich am Anfang der Routine:

Code:

Const Zeile2 = 33

und dann die ganzen Zeilen der Routine markieren am besten mit "Suchen und Ersetzen" (im markierten Bereich)
Suchen: 33"
Ersetzen: " & Zeile2

Dann müßte an allen möglichen Stellen jetzt sowas in der Art stehen:
s1.Range("k3:k" & Zeile2)

Wenn sich dann später der Bereich bis Zeile 47 oder so erweitert, brauchst Du nur noch die "Const"-Anweisung am Anfang anpassen.

Gruß, Michael

 

Guten Tag,

Wahnsinn wie einem hier geholfen wird, super Toll.

Ich habe auch ein ähnliches Problem, ich möchte aus mehreren Excel Dateien welche alle mit "Building*.xlsx" anfangen Werte herauskopieren und aufaddieren. (Siehe Bild)

Aus diesen Dateien soll das Tabellenblatt "Summary" und die Werte C3:C31 in ein neues Dokument auf addiert werden. (Es handelt sich bei den Building Dateien um Stückzahlen einzelner Komponenten von Gebäuden. Die Summary ist immer gleich auf gebaut und jetzt benötige ich die Gesamte Stückzahl)

Wäre super wenn mir jemand helfen kann.

Herzlichen Dank und Gruß
Daniel

 

Hi michael. Ich würde den Inhalt der ZIP gerne für meine Zwecke anpassen. Würde es gerne in visual Studio öffnen aber was für ein Projekt muss ich dafür erstellen? LG. Julian

 

Ohje, mit VS wirste da nix werden. In dem ZIP befindet sich eine XLS Datei und die ist für MS-Excel gedacht.

Du kannst aber, wenn Du auch Excel hast, die Datei damit öffnen, mittels Alt-F11 in den VBA-Editor wechseln und den VBA-Code dann einsehen. Inwieweit er für ein neues VS-Projekt (VB.Net?) dann hilfreich ist, mußt Du dann selbst entscheiden.

Gruß, Michael