Office: (Office 2016) 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

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

 

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.

 

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 )).

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

 

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.

 

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...
Da das hier ein Forum und keine Leerstunde ist, werde ich's dabei belassen.

Vielen Dank,
Lutz