Supportnet / Forum / Tabellenkalkulation

Hallo Excel-Experten, ich stehe vor einer für mich nicht lösbaren Excel-Aufgabe. Ich habe einen Ordner in dem sich etliche Excel-Arbeitsmappen befinden deren Anzahl sich durch hinzufügen neuer, und löschen erledigter Mappen ständig ändert. Die Mappen sind als z.B. Müller 123, Meier 456, Schmitz 234 benannt. (Später gilt es bestimmte Daten aus diesen einzelenen Mappen auszulesen und in einer Tabelle zusammen zu fassen.) Jetzt zu meinem ersten Schritt. Ich möchte eine zusätzliche Arbeitsmappe anlegen, in der z.B. in der Spalte A alle "Namen" (Arbeitsmappen)entsprechend dem aktuellen Inhalt des gesamten Ordners untereinander aufgelistet sind. Da ich ziemlicher Excel Laie bin weiß ich nicht ob nicht ob das überhaupt möglich ist. Vielleicht hat jemand eine Idee zur Lösung parat. Gruß Josch

Hey Josch,

das ist ja eine recht interessante Aufgabe,
prinzipiell ist das schon möglich,
allerdings nur mithilfe von Makros.
Ich habe dir mal eins gebastelt, kann man dann noch entsprechend anpassen. In folgendem Makro werden beim Öffnen der Tabelle auf Ballt 1 Spalte A untereinander alle XLS - Dateien in dem gleichen Pfad wie die geöffnette Tabelle angezeigt. Derzeit ist die Anzeige allerdings noch mit vollständigem Pfad:

Private Sub Workbook_Open()
  Dim i As Integer
  Dim Pfad As String
  
  Pfad = ThisWorkbook.Path
    With Application.FileSearch
      ChDir Pfad
      .LookIn = Pfad
      .Filename = "*.xls"
        If .Execute > 0 Then
             Columns("A:A").Clear
                For i = 1 To .FoundFiles.Count
                  Sheets(1).Cells(i, 1) = .FoundFiles(i)
                Next i
            End If
    End With
End Sub

Diesen Code einfach verwenden:
mit Alt + F11 kommst du in die VBA Entwicklungsumgebung, Im Projektexplorer (findest du unter Ansicht) wird dir deine Exel Tabelle mit den Einzelheiten angezeigt: Doppelklick auf: Diese Arbeitsmappe und in dem Fenster entsprechjenden Code eintragen. Sollte eigentlich funktionieren.

Beste Grüße

Primut

Hi Josch,

nochmal das überarbeitete Makro, es werden jetzt nur noch die Dateinamen angezeit, genau so, wie gewünscht.

Private Sub Workbook_Open()
  Dim i As Integer, Länge As Integer
  Dim Pfad As String
  Dim NameDaten As String
  Dim AWF As WorksheetFunction
  Set AWF = Application.WorksheetFunction
  Pfad = ThisWorkbook.Path
  Länge = Len(Pfad) + 1
  
    With Application.FileSearch
      ChDir Pfad
      .LookIn = Pfad
      .Filename = "*.xls"
        If .Execute > 0 Then
             Columns("A:A").Clear
                For i = 1 To .FoundFiles.Count
                  NameDaten = AWF.Replace(.FoundFiles(i), 1, Länge, "")
                  Sheets(1).Cells(i, 1) = NameDaten
                Next i
            End If
    End With
End Sub

Funktioniert bei mir jedenfalls gut. Falls du Probleme haben solltest, diesen Code einzubauen, dann hat Coros das ziemlich gut und genau hier: https://supportnet.de/threads/173231 in Antwort 3 erklärt!

Gruß Primut

Hey Primut

Zuerst mal herzlichen Dank das du dich meiner Aufgabe angenommen hast.
Ich habe mehrfach dein Makro in ein hinzugefügtes Arbeitsblatt in diesem
Ordner kopiert bin aber irgendwie noch nicht zum gewünschten Erfolg gekommen.

Ich weiß nicht ob ich die Situation richtig beschrieben habe, sodass deine Lösung
funktionieren kann, oder ob mir ein Fehler beim Einfügen unterlaufen ist.
Hier deswegen einmal die ganze Aufgabenstellung.

In einem Dateiordner sind ca. 280-350 Excel Arbeitsblätter die jeweils wie „Müller 123“ bezeichnet sind. Ein zusätzlich angelegtes Arbeitsblatt (z.B.„Übersicht“)in diesem Ordner soll alle diese Namen in der Spalte A analog des gesamten Ordners aufnehmen, Änderungen durch hinzufügen oder löschen von Arbeitsblättern berücksichtigen. Es sollen letztlich in dem Arbeitsblatt „Übersicht“ die Namen erkennbar sein (wie auch immer) bei denen in dem dazugehörigen Arbeitsblatt bestimmte Zellenwerte unterschritten werden.
Folgende Zellen werden nach und nach mit Zahlen beschrieben, immer 2 Zellen zusammen
Q13undQ15 dann Q34Q36 dann Q55Q57 Q76Q78 Q97Q99 Q118Q120 Q140Q142 Q161Q163 Q182Q184 Q203Q205 in der Reihenfolge wie angegeben. Der zuletzt getätigte Eintrag ist ausschließlich relevant, vorher getätigte Einträge brauchen nicht mehr berücksichtigt werden. Und zwar soll in der Übersicht angezeigt werden wenn einer dieser beiden Einträge kleiner oder gleich 4,0 ist.
Ziel ist es also das ständige prüfen jeder einzelnen Datei zu erübrigen bei denen in den
genannten Zellen Werte </= 4 stehen.

Vielleicht kannst du oder jemand anders da etwas draus machen, wäre klasse.

Gruß und schönen Abend
Josch

Hi Josch,

offensichtlich haben wir uns ja mißverstanden:
Mein Makro listet alle weiteren Exel-Dateien (Arbeitsmappen) auf, die sich in dem selben Windowsordner befinden.
Scheinbar hast du aber nur eine offene Exel-Arbeitsmappe, in der sich aber die unterschiedlich vielen Arbeitsblätter befinden - oder? Das sieht natürlich dann anders aus, ich werd mal schauen...
Habe ich dich damit richtig verstanden?
Falls du doch verschiedene Dateien meinst, wie sah denn die Fehlermeldung aus?

Gruß Primut

Hi Primut,

entschuldige das ich diese Problematik missverständlich beschrieben habe.
Das liegt wohl daran das ich mich mühsam in Excel eingearbeitet habe und weiterwurstele.
Es ist richtig so wie du schreibst.
Ich habe nur eine Datei-Ordner in dem sich die genannten Arbeitsblätter befinden.


Gruß Josch

Hi Josch,

also dann hier ein Makro, das dir im Arbeitsblatt Übersicht eine Liste aller anderen Arbeitsblätter anzeigt:

Private Sub Workbook_Open()
  Dim i As Integer
 
    For i = 1 To Worksheets.Count
      Worksheets("Übersicht").Cells(i, 1) = Worksheets(i).Name
    Next i
    
End Sub

Setzt natürlich vorraus, daß das Blatt Übersicht schon existiert. Wenn du es so wie in Antwort 1 und 2 beschrieben einfügst, sollte jedesmal, wenn du die gesamte Arbeitsmappe öffnest, die Liste der aktuellen Arbeitsblätter angezeigt werden.

OK, in der Übersicht werden deine aktuellen Arbeitsblätter angezeigt, hab allerdings noch nicht genau nachvollziehen können, was wo eingetragen wird. D.h. welche Zellen beziehen sich auf welche Arbeitsblätter, und welche bedingte Verknüpfung derselben soll genau welches Ergebnis wo erreichen?

Gruß Primut

Hi Primut,

Also bei den Tabellenblättern handelt es sich um "Eingabebereiche" (21 Zeilen) die sich durch nach unten scrollen wiederholen. Es werden etliche Daten eingefügt die für die Selection ohne Belang sind.
Einzig die Daten in den o.g. Zellen Q13 Q15 später
dann Q34 Q35 usw. sind für die Selection relevant.
Immer nur die zuletzt eingegebenen Einträge sollen geprüft werden. (In der Liste nach unten, immer gleiche Spalte, Zellen immer 21 weiter nach unten)

Wenn dort in einer der beiden, oder in beiden Zellen Werte =4 oder kleiner eingegeben sind, soll der Name in der Übersicht sich von den anderen irgendwie unterscheiden, erkennbar sein.

Ich hoff es wird jetzt verständlicher.

Gruß
Josch

Hi Josch,

na gut, ich hab mir dann doch noch mal die Mühe gemacht und ein Makro für die entsprechende Abfrage der Arbeitsblätter gemacht. Dabei wird im Übersichtsblatt, wenn jeweils einer der letzen beiden Werte kleiner gleich 4 ist, die Hintergrundfarbe der Zelle rot, ansonsten grün.
Dabei ist eine leere Zelle auch <4!, D.h. wenn beispielsweise nur eine der beiden Zellen belegt ist.
Mit Alt + F11 kommst du in die VBa Entwicklungsumgebung, und unter Einfügen / Modul erhälst du ein Arbeitsblatt, wo folgender Code einzufügen ist:

Sub Arbeitsblätter_aktualisieren()
  Dim i As Integer 
    
  For i = 1 To Worksheets.Count
      Application.DisplayAlerts = False
      Worksheets("Übersicht").Cells(i, 1) = Worksheets(i).Name
       zeile = Worksheets(i).Cells(Rows.Count, 17).End(xlUp).Row        ´17 entspricht Spalte Q!
      ZeilePrüf = 13 + Int((zeile - 13) / 21) * 21
      If ZeilePrüf = -8 Then ZeilePrüf = 1
       If (Worksheets(i).Cells(ZeilePrüf, 17).Value <= 4) Or (Worksheets(i).Cells(ZeilePrüf + 2, 17) <= 4) Then
        Wert = False
       Else
        Wert = True
       End If
      
      If Wert = True Then
       Worksheets("Übersicht").Cells(i, 1).Interior.ColorIndex = 4  ´ entspricht Hintergrundfarbe Grün
      Else
       Worksheets("Übersicht").Cells(i, 1).Interior.ColorIndex = 3  ´ entspricht Hintergrundfarbe Rot
      End If
      
    Next i
    Worksheets("Übersicht").Select
    Application.DisplayAlerts = True
End Sub

Sollte entsprechend deiner Beschreibung funktionieren, oder ich hab dich falsch verstanden.

Wichtig: wenn du es über Copy einfügst, mußt du die Hochkommas zum Auskommentieren erneuern, das wird hier nicht richtig wiedergegeben.

Dieses Makro kannst du dann jeweils bei Bedarf über Extras / makro aufrufen.
Oder du kopierst es ins alte Workbook-open Makro, damit es sich immer automatisch beim öffnen der
Arbeitsmappe ausgeführt wird. (Kopf - und Fußzeilen anpassen)

Gruß
Primut

Hi Primut,

ich habe dein Makro in meinem Blatt "Übersicht"
eingefügt und die Fehlermeldung "Laufzeitfehler ´9´
Index außerhalb des gültigen Bereichs" erhalten.
Wenn es dir recht ist und du mir deine mail Adresse zumailen würdest, könnte ich dir als Anhang eine vereinfachte Form der Datei zuschicken.
Meine Email Adresse josefschmitz@online.de
Ich denke das würde die Sache extrem vereinfachen.
Danke für deine Geduld.
Gruß
Josch

Hi Josch,

ich hab das Makro fast fertig, werd die Optik und ein paar Kleinigkeiten noch verbessern.
War doch komplexer als ich dachte.
Ich kanns dir dann morgen im Laufe des Tages rüber schicken.
Ich würd ich dann über ein entsprechendes Feedback
freuen, wenns läuft etc.

Gruß
Primut

Hi Primut,


ich lese gerade deine Nachricht kann im Moment jedoch nicht darauf eingehen.

Ich melde mich am Abend per Email.

Danke


Gruß

Josch

Hey Josch,

die Tabelle ist auf dem Weg,
wie gesagt, bin neugierig, obs läuft.
Schließlich hab ich ja auch noch nie 250 Arbeitsmappen per Makro abgefragt und weiß nict, was dort noch für Fehler auftreten können.

Gruß

Primut

Hi Primut,

die Tabelle die du mir geschickt hast läuft prima, besser noch als ich erwartet habe da deine zusätzlich eingebauten Features über überaus sinnvoll sind.

Dank hier nochmals für deine Hilfe.



guten Abend und eine gut Zeit

Josch

• Zirkelbezug
• Excel Arbeitsblätter
• Wechseln zischen zwei Arbeitsmappen per Makro
• Wie kann ich Werte aus einer Vielzahl von Arbeitsmappen in einer einzigen Mappe konsolidieren?
• Suchen in Arbeitsmappen