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

Hallo liebes Forum,

mich beschäftigt momentan folgende Aufgabenstellung:

Ich habe ein Verzeichnis mit Excel Dateien die nach Mitarbeiternamen benannt sind. Die Dateien haben alle denselben Aufbau.

Ich möchte nun in einer weiteren Exceldatei die im selben Pfad liegt, eine Summenbildung pro Zelle über alle im Verzeichnis liegenden Dateien erzeugen.

Im Grunde sollen Ranges von C3:I33; K3:K33; M3:M33; O3:O33; Q3:Q33; S3:S33; U3:U33; W3:W33; Y3:Y33; AA3:AA33 gesucht, hochaddiert und in den jeweiligen Bereich der Sammeltabelle geschrieben werden.

Per einfacher Formel wäre das noch möglich. Allerdings ändert sich der Mitarbeiterstamm und die Anzahl der Dateien im Pfad ändert sich ständig im laufenden Jahr.

Wäre toll wenn mir hier jemand per Formel oder VBA einen Tipp geben könnte wie man das umsetzen kann.

Viele Grüße

Thomas

 

Moinsens!

Trotzdem würde ich mir überlegen, ob es nicht machbar ist, ggf. 1x im Monat die Tabelle mit den Mitarbeitern auf den aktuellen Stand zu bringen.

Ansonsten würde mir nur eine VBA-Lösung einfallen, die das Verzeichnis nach Excel-Mappen durchsucht und dann eben das Übersichts-Blatt mit den passenden Formeln füllt. Alternativ würde auch eine Auflistung der Mappen reichen, auf die dann mittel Indirekt() zugegriffen werden kann.

Leicht problematisch ist das Durchsuchen des Verzeichnisses, weil es einen Knacks zwischen Excel bis 2003 und ab 2007 gibt: In den "alten" Versionen gab's noch Application.Filesearch, in den neueren muß man auf Scripting.FileSystemObject zurückgreifen. Außerdem muß man sich die Rekursion durch Unterverzeichnisse wohl selbst stricken. (Ich bin mir nicht sicher, ob die FileSystemObject-Variante auch vor 2007 funktionieren würde. Ich merke nur, daß ich gerade nach und nach alle FileSearch-Routinen auf die neue Variante umstellen muß.)

Ich hab Dir im Anhang mal ein Beispiel gebaut, was die Dateien eines Verzeichnisses auflistet.

Gruß, Michael

 

Hallo und Danke für die Antwort!

Im Verzeichnis gibt es keine Tabelle mit den MA Namen. Jeder MA hat eine eigene XLS. Alle mit demselben Aufbau. Ich bräuchte daher nur ein Script dass alle einzelnen XLS liest und die Werte der entsprechenden Zelle hochaddiert und in eine Gesamt.xls schreibt.

Ich hatte dazu auch an anderen Stellen schon ein VBA Script gefunden. Allerdings waren diese zu alt und sind genau aus deinem Grund nicht lauffähig gewesen.

Da ich keinerlei Ahnung von Programmieren habe, bin ich leider auf Hilfe angewiesen. Ich hoffe, es findet sich noch eine Lösung.

Grüße

 

Was ich noch vergessen hatte.

Die einzelnen MA Tabellen haben mehrere Tabellenblätter. Unter anderem Januar bis Dezember und nur aus diesen möchte ich die Zahlen ziehen.

 

Moinsens!

Ich hab mal den Code aus dem vorigen Beispiel erweitert. Er macht jetzt folgendes:
- alle *.xls* werden erfaßt
- jede gefundene Mappe wird geöffnet
- in die Liste wird eingetragen:
Dateiname (ohne Pfad)
Blattname des 1. Blatts in der Mappe
der Wert aus Zelle A1 der Mappe (kopiert)
eine Formel, die Zelle A1 der Mappe referenziert

Code:

Option Explicit

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

    Dim p As String
    Dim m As String

    Dim f As String
    Dim s As String
    Dim v As Variant

    p = Range("b1")
    m = Range("b2")

    Set FileList = SearchFiles(p, m)

    Range("b7:e" & Rows.Count).Clear
    For n = 1 To FileList.Count

        f = Right$(FileList(n), Len(FileList(n)) - Len(p) - 1)
        Range("b" & n + 6).Value = f

        On Error GoTo NextFile
        Workbooks.Open p & "\" & f
        On Error GoTo 0
        s = ActiveWorkbook.Worksheets(1).Name
        v = ActiveWorkbook.Worksheets(1).Range("a1").Value
        ActiveWorkbook.Close savechanges:=False

        Range("b" & n + 6).Value = f
        Range("c" & n + 6).Value = s
        Range("d" & n + 6).Value = v
        v = "='" & p & "\[" & f & "]" & s & "'!$a$1"
        Range("e" & n + 6).Formula = v

NextFile:
            On Error GoTo 0

    Next n

End Sub

Entscheidend sind für Dich die Zeilen

Code:

    Dim s As String
    Dim v As Variant

        s = ActiveWorkbook.Worksheets(1).Name

        v = ActiveWorkbook.Worksheets(s).Range("a1").Value
        Range("d" & n + 6).Value = v

oder alternativ die Zeilen

Code:

    Dim v As Variant

        v = "='" & p & "\[" & f & "]" & s & "'!$a$1"
        Range("e" & n + 6).Formula = v

Variante a kopiert den Wert selbst direkt, so daß keine Referenzen auf externe Tabellen in der aktuellen Mappe sind. Variante b referenziert die Zelle in der externen Tabelle per Formel und kann so auf Änderungen dort reagieren. Ich schätze mal, daß aber Variante a für Dich etwas passender ist.

Was ist also zu tun? Nun... "s" muß mit dem Blattnamen belegt werden, aus dem der Wert kopiert werden soll. "v" muß den Wert aus der Zelle lesen, entsprechend muß die Range()-Angabe angepaßt werden. Und der Wert soll dann in die "lokale" Mappe eingefügt werden, also muß das Range() in der 3. Zeille ebenfalls angepaßt werden.

Wenn Du also aus dem Blatt "März" die Zelle C5 in das aktuelle Blatt, auf dem sich der Button befindet, an Stelle E9 kopieren möchtest:

Code:

        s = [COLOR=#ff0000]"März"[/COLOR]
        v = ActiveWorkbook.Worksheets(s).Range("[COLOR=#ff0000]c5[/COLOR]").Value
        Range("[COLOR=#ff0000]e9[/COLOR]").Value = v

Ich hab das Kopieren übrigens nur aus Gründen der Übersichtlichkeit in 2 Zeilen aufgeteilt. Genau genommen kannst Du dir das

Code:

Dim v as Variant

sparen und die anderen beiden Zeilen zu

Code:

Range("e9").Value = ActiveWorkbook.Worksheets(s).Range("c5").Value

zusammenziehen.

Wenn Du der Reihe nach alle Werte aus "Januar" bis "Dezember" brauchst, muß noch eine Schleife um das Kopieren gebaut werden. In einer "For..Next" Schleife könnte dann per "Select Case" die Variable "s" mit dem Blattnamen belegt werden. So in der Art:

Code:

for m=1 to 12
    select case m
        case 1: s="Januar"
        case 2: s="Februar"
        [...]
    end select
    Range("e9").Value = ActiveWorkbook.Worksheets(s).Range("c5").Value
next m

Macht allerdings nicht viel Sinn, die 12 Werte der Reihe nach auf die gleiche Ziel-Zelle E9 zu kopieren... ;-) Ein

Code:

Range("e" [COLOR=#ff0000]& 8+m[/COLOR]).Value = ...

würde die Werte dann ab E9 abwärts einfügen.

Wenn Du mehrere Werte aus dem jeweiligen Blatt brauchst, wiederholt sich die Kopieren-Zeile entsprechend oft. Bei größeren Bereichen wird es dann aber sinnvoll, entweder mit einer weiteren Schleife zu arbeiten oder mit .copy und .pastespecial die Werte unter Zuhilfenahme der Zwischenablage zu kopieren.

Übrigens: Diese "On Error Goto ..." Geschichte ist eigentlich böse. Das ist nur ein "quick and dirty" hack und sollte viel lieber durch vorherige Prüfungen ersetzt werden, die eine Fehlersituation vermeiden. Entsprechende Prüfungen können aber umfangreich werden (Datei gesperrt, Blatt nicht vorhanden, ...) und sollten das Beispiel hier nicht zu unübersichtlich machen.

Gruß, Michael

 

Hallo Michael,

erst mal danke und wow! Ich glaube, das geht in die richtige Richtung. Da ich jedoch nicht wirklich programmiertechnisch versiert bin, stellen sich mir ein paar Fragen.

Was mir aufgefallen ist.

- Der Pfad oder der Dateiname braucht nicht in die Ziel Excel geschrieben werden, die Werte reichen.
- Die Werte in den Zellen werden noch nicht addiert. In der Ziel Excel soll im Grunde nur das Produkt einer Quell Zelle geschrieben werden.
- Die einzelnen .xlsx brauchen nicht geöffnet zu werden

Das mit den Schleifen muss ich mir dann noch genauer ansehen wenn das Grundgerüst steht.

Viele Grüße

Thomas

 

Hallo Michael,

anbei mal die Datei, die am Ende befüllt werden soll. Ich denke damit wird ersichtlicher was ich vorhabe :)

Die restlichen Dateien in einem bestimmten Pfad sind von den Zellorten her genau so aufgebaut.

Grüße

 

Bitte die Forenregeln beachten. Für Anhänge ist ausschließlich der interne Attachment Mod zu verwenden. Bitte korrigieren! Danke

 

Das habe ich versucht aber den Hinweis bekommen nur 100kb hochladen zu dürfen. Meine Datei ist allerdings 114kb groß.

 

Du brauchst sicher nicht die kompletten Daten um Dein Problem zu schildern ;-)

 

Moinsens!

Jo, wie ich dachte: Variante a, bei der die Werte direkt kopiert werden und keine Formel-Referenzen auf die anderen Mappen erstellt werden.

Code:

- Die Werte in den Zellen werden noch nicht addiert. In der Ziel Excel soll im Grunde nur das Produkt einer Quell Zelle geschrieben werden.

Den ersten Satz verstehe ich, zumindest in diesem Kontext, nicht so recht. Aber das Beispiel macht, ganz "stur und dumm" nichts weiter, als die Werte aus der anderen Mappe in die eigene zu kopieren. Da wird nichts addiert oder multipliziert. Wenn Dein Übersichts-Blatt dann mit den kopierten Werten weiter rechnet, kein Problem.

Code:

- Die einzelnen .xlsx brauchen nicht geöffnet zu werden

;-) Doch, wenn auch nur kurz, da man ja sonst den Wert nicht auslesen kann. Aber die geöffneten Mappen werden ja auch gleich danach wieder geschlossen.

Was mir aber jetzt noch nicht so ganz klar ist:

Dein "Dummy" ist die Mappe mit den Summen, also das "Ziel", in das die Werte einkopiert werden sollen, richtig? Und die Mappen der einzelnen Mitarbeier, aus denen die Werte herauskopiert werden sollen, sind genauso aufgebaut? Ich vermute jetzt mal:

In Zelle 'März'!K6 steht eine Stückzahl. Das ist in jeder Mappe der ganzen Mitarbeiter immer die Stückzahl "V2" für den 24.12.2014, richtig? Und jetzt sollen alle Zellen 'März'!K6 aus allen Mitarbeiter-Mappen addiert werden und das Ergebnis in das Summen-Blatt ebenfalls 'März'!K6 eingesetzt werden, richtig?

Wenn das nämlich so ist, dann ändert sich auch das Makro schon recht deutlich. Da muß ich dann schon mal vorwarnen, daß die Abarbeitung etwas länger dauern wird und das Makro auch etwas umfangreicher wird. Ich hab da zwar schon eine Idee, aber bevor ich das umsetze, will ich lieber sicher gehen, daß ich das so richtig verstanden habe.

(Eigentlich sieht mir der ganze Aufbau übrigens eher nach einem Job für Access statt Excel aus...)

Gruß, Michael

 

Was ich vergessen hatte, zu fragen: Von wie vielen Mappen / Mitarbeitern so ca. reden wir hier eigentlich? Bei der Summen-Bildung über die ganzen Mappen wäre nämlich die Frage, ob für einen gewissen Zeitraum _alle_ Mappen _gleichzeitig_ geöffnet werden können, oder ob das den Speicher platzen läßt.

Gruß, Michael

 

Ließ mir dann ja doch keine Ruhe... ;-)

Versuch mal den Anhang, nachdem Du den Pfad in A1 angepaßt hast. Das Ding macht jetzt folgende Schritte:
- Es werden alle *.xls* Filess gescannt
- Jedes File wird 1x kurz geöffnet - Fehler --> Mappe überpringen
- In jedem File wird nach dem Blatt "Januar" gesucht - Fehler --> Mappe überpringen
- Mit der "Namens"-Liste aller erfolgreich geöffneten Mappen wird dann eine Formel gebastelt:
= ' Pfad \ [ Mappe1 ] Blatt ' ! C3 + ' Pfad \ [ Mappe2 ] Blatt ' ! C3 + ...
- Auf dem Blatt mit dem Button werden ab B7 abwärts alle Mappen aufgelistet, daneben der Blattname, daneben der Zellinhalt aus C3
- Am Ende wird die gebastelte Formel in E7 gesetzt

Und jetzt der Clou:

Das Blatt "Januar", was ich als 2. leeres Blatt in die Mappe aufgenommen habe, wird mit dieser Formel in C3 gefüllt, die wird dann nach rechts bis I3 gezogen, in K3, M3, O3, Q3, S3, U3, W3, Y3 und AA3 kopiert und dann wird der ganze Kladderadatsch noch bis Zeile 33 nach unten gezogen. Zum Schluß werden die Formel-Ergebnisse kopiert und als konstante Werte wieder in die Zellen geschrieben. (Das gilt es noch zu verfeinern, damit Formeln "drumherum" erhalten bleiben...)

Grundsätzlich müßte die Routine aber Deine Vorlage füllen können und wenn die Formel dem Sinn entspricht, was Du vorhast, sollten wir da recht schnell weiter kommen. Dann wären ja noch die anderen Monatsblätter zu verwursten...

Gruß, Michael

 

Hallo Michael!

Danke für deine Antwort. Echt Klasse!

Also, im deine Frage zu beantworten, du hast es richtig verstanden. Die MA Tabellen und die Gesamt Tabelle haben alle den gleichen Aufbau. Es sind ca. 30 MA Tabellen. Alle gleichzeitig öffnen sollte aber klappen. Es muss nur jedes Mal "Daten aktualisieren" bestätigt werden obwohl das deaktiviert ist.. naja.

Leider ist es so, dass dein aktuelles File nur noch einen Hinweis bringt wie viele Files gefunden wurden und schreibt den Pfad ab B7 untereinander. Das wars. Da waren wir schon mal weiter :)

Das hochaddieren von gleichen Zellen bzw. Zellbezeichnungen scheint der Knackpunkt zu sein. Und ja, eigentlich wäre es etwas für Access jedoch arbeiten mit den Tabellen Menschen die sogar Excel nur vom hören sagen kennen :)

 

Moinsens!

Örks... ?! Ok, da hab ich letztes mal vielleicht den Attach zu früh an das Posting gehängt und danach noch was verändert. Also ich hab's eben nochmal mit "Tabelle1" statt Januar auf mein Verzeichnis losgelassen und er hat mir alles sauber aufgelistet. Ich hab's jetzt wieder auf "Januar" eingestellt, Du müßtest nur noch den Pfad anpassen.

Ich erinnere mich, daß ich an der "On Error" Geschichte letztes mal noch was geändert hatte. Das war dann wohl nicht die Version, die es in den Upload geschafft hatte. ;-)

Gruß, Michael