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

Helfe beim Thema Ein Verzeichnis, mehrere Excel Dateien und Summe von Zellen addieren in Microsoft Excel Hilfe um das Problem gemeinsam zu lösen; Hallo liebes Forum, mich beschäftigt momentan folgende Aufgabenstellung: Ich habe ein Verzeichnis mit Excel Dateien die nach Mitarbeiternamen... Dieses Thema im Forum "Microsoft Excel Hilfe" wurde erstellt von tommy536, 15. Januar 2015.

  1. tommy536 Erfahrener User

    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
     
    tommy536, 15. Januar 2015
    #1
  2. miriki Erfahrener User
    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
     
  3. tommy536 Erfahrener User
    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
     
    tommy536, 19. Januar 2015
    #3
  4. tommy536 Erfahrener User

    Ein Verzeichnis, mehrere Excel Dateien und Summe von Zellen addieren

    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.
     
    tommy536, 19. Januar 2015
    #4
  5. miriki Erfahrener User
    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
     
  6. tommy536 Erfahrener User
    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
     
    tommy536, 20. Januar 2015
    #6
  7. tommy536 Erfahrener User
    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
     
    Zuletzt bearbeitet: 21. Januar 2015
    tommy536, 20. Januar 2015
    #7
  8. Michael
    Michael Administrator

    Ein Verzeichnis, mehrere Excel Dateien und Summe von Zellen addieren

    Bitte die Forenregeln beachten. Für Anhänge ist ausschließlich der interne Attachment Mod zu verwenden. Bitte korrigieren! Danke
     
    Michael, 20. Januar 2015
    #8
  9. tommy536 Erfahrener User
    Das habe ich versucht aber den Hinweis bekommen nur 100kb hochladen zu dürfen. Meine Datei ist allerdings 114kb groß.
     
    tommy536, 20. Januar 2015
    #9
  10. Michael
    Michael Administrator
    Du brauchst sicher nicht die kompletten Daten um Dein Problem zu schildern ;-)
     
    Michael, 21. Januar 2015
    #10
  11. miriki Erfahrener User
    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
     
  12. miriki Erfahrener User
    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
     
  13. miriki Erfahrener User

    Ein Verzeichnis, mehrere Excel Dateien und Summe von Zellen addieren

    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
     
  14. tommy536 Erfahrener User
    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 :)
     
    tommy536, 22. Januar 2015
    #14
  15. miriki Erfahrener User
    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
     
Thema:

Ein Verzeichnis, mehrere Excel Dateien und Summe von Zellen addieren

Die Seite wird geladen...
  1. Ein Verzeichnis, mehrere Excel Dateien und Summe von Zellen addieren - Similar Threads - Verzeichnis Excel Dateien

  2. Makro gesucht Sicherungskopie in anderes Verzeichnis - Sicherung ohne Makros

    in Microsoft Excel Hilfe
    Makro gesucht Sicherungskopie in anderes Verzeichnis - Sicherung ohne Makros: Hallo, kann mir jemand helfen bei der Erstellung eines Makros? Ich möchte von einer Datei, sobald sie geschlossen wird, eine Sicherungskopie in ein anderes Verzeichnis legen. Jedoch soll die...
  3. Bestimmte Inhalte aus sämtlichen Textdateien im Verzeichnis in Excel Tabelle / Zeilen und zuordnen

    in Microsoft Excel Hilfe
    Bestimmte Inhalte aus sämtlichen Textdateien im Verzeichnis in Excel Tabelle / Zeilen und zuordnen: Hallo liebes Forum, ich bin neu hier und hoffe, dass Sie mir vielleicht helfen können: Ich habe ein Verzeichnis mit *.txt Dateien mit bestimmten (z.T. variablen Inhalten). Diese Textdateien...
  4. Verzeichnis Beschriftung

    in Microsoft Word Hilfe
    Verzeichnis Beschriftung: Guten Morgen, ich habe in meiner Masterarbeit unter Verzeichnis die Beschriftungen für Tabellen und Abbildungen gelöscht ... ich kann sie nicht mehr auswählen oder formatieren. Wie kann ich den...
  5. Backup Verzeichnis auswählen und dauerhaft hinterlegen

    in Microsoft Access Hilfe
    Backup Verzeichnis auswählen und dauerhaft hinterlegen: Hallo, ich speichere meine Backend Datei nach jedem schliessen der Datenbank automatisch mit folgendem Code: Code: Public Sub BackupBackend() 'Backup automatisch nach jedem Beenden ausführen Dim...
  6. Teams Fehlermeldung: Verzeichnis wird eingerichtet

    in Microsoft Teams Hilfe
    Teams Fehlermeldung: Verzeichnis wird eingerichtet: Guten Abend! Wir verwenden mit den SchülerInnen Teams für Distance Learning. Seit gestern passiert es regelmäßig - besonders am Nachmittag - dass im Register "Dateien" diese Fehlermeldung...
  7. Mails in Explorer-Verzeichnisstruktur automatisch speichern

    in Microsoft Outlook Hilfe
    Mails in Explorer-Verzeichnisstruktur automatisch speichern: Hallo Zusammen, Ich habe im outlook viele Verzeichnisse in denen entsprechend Mails abgelegt sind. Die selbe Verzeichnisstruktur gibt es bei mir im Explorer auf der Festplatte. Gibt es eine...
  8. Beilagenverzeichnis erstellen

    in Microsoft Word Hilfe
    Beilagenverzeichnis erstellen: Hallo! Ich bin ein reiner Anwender und möchte in einem langen Word-Dokument Folgendes machen: Im fortlaufenden Test soll in runder Klammer "Beilage" mit fortlaufender Nummer eingefügt werden,...
  1. Diese Seite verwendet Cookies, um Inhalte zu personalisieren, diese deiner Erfahrung anzupassen und dich nach der Registrierung angemeldet zu halten.
    Auf dieser Website werden Cookies für die Zugriffsanalyse und Anzeigenmessung verwendet.
    Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden