Office: (Office 2016) Liste aller Blätter in allen Dateien eines Verzeichnisses

Helfe beim Thema Liste aller Blätter in allen Dateien eines Verzeichnisses in Microsoft Excel Hilfe um das Problem gemeinsam zu lösen; Hallo zusammen, ich habe da ein Problem mit einem Code. Der Code soll die Verzeichnisse, Dateinamen und Blattnamen aller Blätter in einem... Dieses Thema im Forum "Microsoft Excel Hilfe" wurde erstellt von Lutz Fricke, 16. Januar 2017.

  1. Lutz Fricke Erfahrener User

    Liste aller Blätter in allen Dateien eines Verzeichnisses


    Hallo zusammen,

    ich habe da ein Problem mit einem Code. Der Code soll die Verzeichnisse, Dateinamen und Blattnamen aller Blätter in einem Verzeichnis ausgeben.
    Verzeichnis und Dateinamen habe ich im Netz gefunden, aber beim Blattnamen hängt's Liste aller Blätter in allen Dateien eines Verzeichnisses :rolleyes:
    Code:
    Option Explicit
    Option Compare Text
    
    Const sRootPath As String = "C:\Test" 'Pfad bitte anpassen ohne Trennzeichen am Ende!!!
    Private lRowCounter As Long
    
    'Start der Routine: Call MWDateienMitUnterordnernAuslesen
    
    Public Sub MWDateienMitUnterordnernAuslesen()
             lRowCounter = 2
             Call MWReadSubFolder(sRootPath)
    End Sub
    
    Private Sub MWReadSubFolder(ByVal sPath As String)
       Dim oFSO As Object
       Dim oFolder As Object
       Dim oSubFolder As Object
       Dim oFile As Object
    Dim oSheet As Object
      
         Set oFSO = CreateObject("Scripting.FileSystemObject")
         Set oFolder = oFSO.getfolder(sPath)
    
        
         With ActiveSheet
        
             For Each oSubFolder In oFolder.subfolders
            
                 'Alle Dateien auflisten
                 For Each oFile In oSubFolder.Files
                    For Each oSheet In oFile.Sheets
                     .Cells(lRowCounter, 1) = oSubFolder.Path
                     .Cells(lRowCounter, 2) = oFile.Name
                     .Cells(lRowCounter, 3) = oSheet.Name
                     lRowCounter = lRowCounter + 1
                    Next oSheet
                 Next oFile
                
                 'Alle Unterverzeichnisse verarbeiten (rekursiv)
                 Call MWReadSubFolder(oSubFolder.Path)
        
             Next oSubFolder
        
         End With
        
         Set oFSO = Nothing
         Set oFile = Nothing
         Set oFolder = Nothing
         Set oSubFolder = Nothing
    
    End Sub
    
    Bei der Zeile
    For Each oSheet In oFile.Sheets
    bekomme ich den Fehler "Objekt unterstützt diese Eigenschaft oder Methode nicht".
    Wozu benötige ich eigentlich das erste Makro? Warum läuft der zweite Teil nicht alleine?

    Vielen Dank für Eure Hilfe,
    Lutz
     
    Lutz Fricke, 16. Januar 2017
    #1
  2. Exl121150 Erfahrener User
    Hallo Lutz,

    Code:
    Option Explicit
    Option Compare Text
    
    #Const LateBinding = True
    
    Const sRootPath As String = "I:\Excel" '"C:\Test" 'Pfad bitte anpassen ohne Trennzeichen am Ende!!!
    Private lRowCounter As Long
    
    'Start der Routine: Call MWDateienMitUnterordnernAuslesen
    
    Public Sub MWDateienMitUnterordnernAuslesen()
      lRowCounter = 2
      Call MWReadSubFolder(sRootPath)
    End Sub
    
    Private Sub MWReadSubFolder(ByVal sPath As String)
    
    #If LateBinding Then
      Dim oFSO As Object
      Dim oFolder As Object
      Dim oSubFolder As Object
      Dim oFile As Object
      Set oFSO = CreateObject("Scripting.FileSystemObject")
    #Else
      'Falls "#Const LateBinding = False" ganz oben angegeben wurde:
      'Dann muss im VBA-Menü > Extras > Verweise... > Verfügbare Verweise > Microsoft Scripting Runtime
      'in der Checkbox angehakt werden, damit dieses externe Modul eingebunden und verfügbar wird.
      Dim oFSO As New Scripting.FileSystemObject
      Dim oFolder As Scripting.Folder
      Dim oSubFolder As Scripting.Folder
      Dim oFile As Scripting.File
    #End If
    
      Dim oWorkbook As Excel.Workbook
      Dim oSheet As Object
      Dim Ws As Worksheet
      Dim sExcelDateiName As String
      Dim ErrPt%
      
      Set oFolder = oFSO.getfolder(sPath)
      Set Ws = ActiveSheet
      On Error GoTo Err_File_not_Excel
        
      For Each oSubFolder In oFolder.SubFolders
         ErrPt% = 1
         'Alle Dateien auflisten
         For Each oFile In oSubFolder.Files
            ErrPt% = 2
            '1) Jetzt "oFile" testen, ob überhaupt eine Excel-Datei vorliegt, und - falls ja -
            '2) "oFile" Excel-spezifisch öffnen, um auf die Arbeitsblatt-Auflistung zugreifen zu können:
            sExcelDateiName = oFile.Name
            If UCase(sExcelDateiName) Like "*.XL*" Then
              ErrPt% = 3
              Set oWorkbook = Application.Workbooks.Open(Filename:=oSubFolder.Path & "\" & sExcelDateiName)
              oWorkbook.Application.Calculation = xlCalculationManual
              For Each oSheet In oWorkbook.Sheets
                 ErrPt% = 4
                 Ws.Cells(lRowCounter, 1) = oSubFolder.Path
                 Ws.Cells(lRowCounter, 2) = oFile.Name
                 Ws.Cells(lRowCounter, 3) = oSheet.Name
    Nxt_Excel_Sheet:
                 lRowCounter = lRowCounter + 1
                 If lRowCounter Mod 15 = 0 Then Ws.Activate: Ws.Cells(lRowCounter, 1).Select
              Next oSheet
              oWorkbook.Close SaveChanges:=False
    Nxt_File_Excel:
              Set oWorkbook = Nothing
            End If
    Nxt_File:
         Next oFile
        
         'Alle Unterverzeichnisse verarbeiten (rekursiv)
         Call MWReadSubFolder(oSubFolder.Path)
         
    Nxt_SubFolder:
      Next oSubFolder
      
    Exit_Proc:
      Set oFSO = Nothing
      Set oFile = Nothing
      Set oFolder = Nothing
      Set oSubFolder = Nothing
      Exit Sub
      
    Err_File_not_Excel:
      Select Case ErrPt%
        Case 1:    Resume Nxt_SubFolder
        Case 2:    Resume Nxt_File
        Case 3:    Resume Nxt_File_Excel
        Case 4:    Resume Nxt_Excel_Sheet
        Case Else: Resume Exit_Proc
      End Select
    End Sub
    
    Die Objektklasse "Scripting.FileSystemObject" behandelt die Verzeichnisse und Dateien des Dateisystem mit allgemeinen Methoden.
    Um aber auf die Innereien von Excel-Dokumentdateien zugreifen zu können, um zB. diverse Auflistungen zu erhalten, muss man diese Dateien excelspezifisch öffnen (Workbooks.Open....)

    Das erste Makro ist eine SUB ohne Parameter/Argumente. Diese können aus dem Excel-Arbeitsblatt heraus aufgerufen werden, zB. mit der Tastenkombination Alt+F8.
    Speziell bei obigen Makros ist das erste "Public" deklariert und das zweite "Private", sodass dieses zweite unsichtbar im Modul enthalten ist und nur das erste für Aufrufe von außerhalb des Moduls zur Verfügung steht.
    "Public" bedeutet für den Compiler, er solle Einsprungspunkte beim Compilieren des Moduls zur Verfügung stellen, sodass externe Programme diese als Startpunkte verwenden können.
     
    Exl121150, 17. Januar 2017
    #2
  3. Lutz Fricke Erfahrener User
    Hallo Anton,

    ein wie immer mehr als ausführliche Antwort. Dafür erstmal vielen Dank.
    Meinen Fehler habe ich dadurch gefunden (natürlich muss ich die Datei öffnen, um die Sheets auszulesen (Ich Depp Liste aller Blätter in allen Dateien eines Verzeichnisses :rolleyes:)).

    Wie üblich ergeben sich für mich aus deinem Code einige Fragen:
    Was hat es mit dem "#Const LateBinding = True" auf sich? Verstehe ich es richtig, dass der Code im oberen Teil der #If-Abfrage auf allen Rechnern läuft, der untere Teil aber nur mit der entsprechenden Verweis-Einstellung? Ist der untere Teil schneller oder warum machst Du das?

    Du setzt die Berechnung auf manuell. Muss sie dann nicht wieder auf automatisch gesetzt werden? Oder bezieht sich das nur auf das geöffnete Workbook?

    Wozu ist die Zeile "If lRowCounter Mod 15 = 0 Then Ws.Activate: Ws.Cells(lRowCounter, 1).Select"?

    Ich weiß, Fragen, Fragen, Fragen... Trotzdem hoffe, ich Du kannst mir ein Bisschen Licht im dunklen VBA-Reich machen.

    Vielen Dank,
    Lutz
     
    Lutz Fricke, 18. Januar 2017
    #3
  4. Exl121150 Erfahrener User

    Liste aller Blätter in allen Dateien eines Verzeichnisses

    Hallo Lutz,

    Die 4 Zeilen in meinem VBA-Code, die mit "#" beginnen, sind sogenannte Compileranweisungen: Das sind Zeilen, die der Compiler nicht übersetzt, um sie in den ausführbaren Code einzufügen, sondern der Compiler selbst wird dadurch informiert, wie er sich zu verhalten hat. Je nachdem, welchen Boolean-Wert die von mir definierte Variable "LateBinding" (zugewiesen bekommen) hat, übersetzt der Compiler die entsprechenden Codeabschnitte im "#If LateBinding Then" <TrueAbschnitt> "#Else" <FalseAbschnitt> "#End If":
    Ist "LateBinding = True", übersetzt der Compiler die Anweisungen im <TrueAbschnitt> und behandelt die Anweisungen im <FalseAbschnitt> als Kommentar, während es bei LateBinding = False (das hieße, "EarlyBinding" müsste dann "True" sein) genau umgekehrt ist.

    Das Ganze habe ich gemacht - bzw. kannst auch du machen -, um bequem (= bloßes Austauschen einer Boolean-Konstante) zwischen beiden Deklarations/Programmier-Arten umschalten zu können: zwischen Late-Binding und Early-Binding.

    Ich gehe nämlich stets so vor, wenn ich in ein VBA-Makro externe Objektklassen einbinden muss:

    1) Ich beginne im Programmiermodus "Early-Binding" (d.h. #Const LateBinding=False). In diesem Modus habe ich zwar die Unbequemlichkeit, dass ich im VBA-Menü über "Verweise..." das externe Objektmodul einbinden muss, dafür habe ich aber hinterher jede Menge Bequemlichkeit beim Programmieren. Ich bekomme nämlich dann beim Programmieren alle Eigenschaften, Methoden, Ereignis-Handler, etc. des externen Moduls eingeblendet, so als ob sie schon immer Bestandteil von - wie in diesem Fall - Excel wären. Dass nämlich zB. bei der Makroprogrammierung in Excel bereits alle Excel-Objekte und auch VBA-Objekte zur Verfügung stehen, ist ja auch nur deswegen der Fall, weil Excel beide Module unter "Verweise..." automatisch einblendet. Der große Vorteil ist, ich brauche beim Programmieren nicht lange Rätsel raten, wie diese oder jene Eigenschat/Methode/Objekt.... lautet, ob sie existiert, etc. Ich bekomme automatisch das korrekte Kontextmenü eingeblendet für die existierenden Eigenschaften/Methoden/Auflistungswerten etc.
    Im "Dim"-Statement kann ich sofort den exakt passenden Objekttyp verwenden, mit NEW kann ich dort gleich die Variable mit dem richtigen Klassentyp instanziieren, ich brauche auch keine "CreateObject(....)"-Zuweisung einzufügen. Das alles bewirkt, dass ein solchermaßen erstellter Code zur Runtime schneller ausgeführt wird.

    2) Funktioniert dann alles und muss ich das Makro auf anderen PCs installieren, stelle ich auf den Programmiermodus "#Const LateBinding = True" um. Dort muss ich dann die "anonymisierten" Objektvariablen vom Type "Object" verwenden im Dim-Statement, muss "CreateObject(...)" zum Instanziieren zur Runtime einsetzen, was zwar eine einfache Verfügbarkeit von bereits funktionierendem Code bedeutet, aber natürlich Null Programmierkomfort und eine etwas längere Ausführungsdauer des Codes, weil sich dieser erst zur Runtime vollständig organisieren muss und nicht schon zur Programmierzeit.

    Der Code sieht folgendermaßen aus:
    Code:
    Set oWorkbook = Application.Workbooks.Open(Filename:=oSubFolder.Path & "\" & sExcelDateiName)
    oWorkbook.Application.Calculation = xlCalculationManual
    '...
    Set oWorkbook = Nothing
    Diese Manuell-Stellung der Berechnung bezieht sich demnach (nur) auf die temporär geöffnete Arbeitsmappe und dürfte sich eigentlich nicht auf die Excel-Arbeitsmappe auswirken, in deren Arbeitsblatt die Auflistung erstellt wird.
    Das habe ich deswegen so eingestellt, weil ich bei einem Testlauf auf meinem PC festgestellt hatte, dass ich mehrere Arbeitsmappen habe mit vielen (Arbeits)blättern und umfangreichen Berechnungen. Diese lösten bei jedem Blattabruf umfangreiche Berechnungsaktualisierungen aus, was eine erhebliche Zeit beanspruchte.
    Falls dadurch auch die aktive Arbeitsmappe mit der Auflistung auf manuelle Berechnung gestellt wird, müsste man im Makro nach Beendigung der Schleifen die Anweisung "ActiveWorkbook.Application.Calculation = xlCalculationAutomatic" einfügen.
    Diese Zeile kann auch problemlos entfernt werden. Ich hatte sie eingebaut, um im Arbeitsblatt, in dem die Auflistung erzeugt wird, den Fortschritt dieses Vorganges mitverfolgen zu können. Jedesmal, wenn nämlich die Variable "lRowCounter" einen durch 15 teilbaren Wert enthält (also 15, 30, 45, ...) wird die Zelle in Spalte A mit der entsprechenden Zeilennummer markiert als aktive Zelle und dadurch die Bildschirmanzeige im Blattfenster erneuert, sodass man den Auflistungsfortschritt ersehen kann. Bei meinem Test entstand nämlich eine umfangreiche Liste, die auch eine entsprechende Zeit benötigte; so konnte ich auch leichter feststellen, ob bzw. wo sich noch Problemstellen im Makro befinden.

    Eine solche Problemzone ergibt sich aus der Tatsache, dass es in Excel verschiedene Arten von Blättern gibt:
    - Arbeitsblätter
    - Diagrammblätter
    - Dialogblätter (veraltet, aber noch verfügbar)
    - Makroblätter (veraltet, aber noch verfügbar)

    Darum habe ich auch 2 verschiedene Variablen (Ws As Worksheet) und (oSheet As Object), wobei die Variable oSheet den Zugriff auf alle vorgenannten Blatttypen ermöglichen muss, weshalb sie auch nur als Object-Typ deklariert ist.
     
    Exl121150, 18. Januar 2017
    #4
  5. Lutz Fricke Erfahrener User
    Hallo Anton,

    vielen Dank für die wieder ausführliche Antwort.

    Die Early/Late-Binding-Geschichte überreiße ich nicht wirklich. Ist für mein beschränktes Programmieren hoffentlich auch nicht lebenswichtig. Ich habe beim Ausprobieren auch keinen Unterschied beim Programmieren gesehen...Liste aller Blätter in allen Dateien eines Verzeichnisses :confused:
    Da das hier ein Forum und keine Leerstunde ist, werde ich's dabei belassen.

    Vielen Dank,
    Lutz
     
    Lutz Fricke, 18. Januar 2017
    #5
Thema:

Liste aller Blätter in allen Dateien eines Verzeichnisses

Die Seite wird geladen...
  1. Liste aller Blätter in allen Dateien eines Verzeichnisses - Similar Threads - Liste Blätter Dateien

  2. Zwei Listen zusammenführen

    in Microsoft Excel Hilfe
    Zwei Listen zusammenführen: Hallo, ich kenne in Excel nur die einfachsten Funktionen. Ich möchte zwei Listen zusammenführen. Die eine Liste enthält die Spalte "Artikelnummer" und die Spalte "Verkaufspreis". Die zweite...
  3. Liste bedingte Formatierung für eine Zeile u. anderes

    in Microsoft Excel Hilfe
    Liste bedingte Formatierung für eine Zeile u. anderes: Hallo, ich habe hier eine Liste, wo wir EC-Belege eintragen. Also Kunde, Belegnummer und Betrag. In unregelmässigen Abständen (z.B. wenn viele EC Zahlungen am Tag waren, oder am Ende der Woche...
  4. Pivot Gruppe basierend auf Liste

    in Microsoft Excel Hilfe
    Pivot Gruppe basierend auf Liste: Hallo. Ist es möglich eine Gruppierung in einer Pivot-Tabelle nicht manuell sondern basierend auf einer hinterlegten Liste zu erstellen? Zur Erklärung: ich habe einen Kundenstamm A und einen B...
  5. zu jedem Eintrag einer drop & down Liste ein bestimmter Betrag in der Nebenspalte

    in Microsoft Excel Hilfe
    zu jedem Eintrag einer drop & down Liste ein bestimmter Betrag in der Nebenspalte: Hallo Zusammen, ich bräuchte bitte Hilfe, steh total an und finde keine Lösung.... ich habe eine kleine einfache drop & down Liste mit 4 Einträgen, je nachdem welchen Eintrag man auswählt, soll in...
  6. Benutzerdefiniertes Sortieren nach eigener Liste funktioniert nicht

    in Microsoft Excel Hilfe
    Benutzerdefiniertes Sortieren nach eigener Liste funktioniert nicht: Hallo zusammen, ich erstelle eine etymologische Datenbank zu einer Sprachgruppe in Nigeria. Die Spalte "SORTIEREN" soll dabei nach dem unten angegebenen Muster benutzerdefiniert sortiert werden....
  7. Wert einer Zelle neben einer anderen Zelle automatisch anzeigen

    in Microsoft Excel Hilfe
    Wert einer Zelle neben einer anderen Zelle automatisch anzeigen: Hallo! Ich stehe vor einer Herausforderung und hoffe auf deine Unterstützung: Mein Excel-Problem sieht folgendermaßen aus: In meiner Datei habe ich zwei Registerkarten, R1 und R2. Auf R1...
  8. Ganze Zeilen durch VBA in ein anderes Blatt kopieren, wenn eine Bedingung erfüllt ist

    in Microsoft Excel Hilfe
    Ganze Zeilen durch VBA in ein anderes Blatt kopieren, wenn eine Bedingung erfüllt ist: Hallo zusammen, Ich schilder dann erstmal mein Problem. Ich habe eine allgemeine Liste (Tabelle 2), bei der ganz viele Behälter aufgezählt sind (mehrere 100 Zeilen). die Tabelle 1 besteht...
  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