Vor kurzem fragte mich Andreas Entenmann, ob ich mein Tool zum Auslesen von Verzeichnissen so erweitern könnte, dass nicht nur die Namen der Unterverzeichnisse ausgelesen und in eine Excel Tabelle geschrieben werden, sondern auch die kompletten Pfade pro gefundenem Unterordner. Und wie es oft so ist, kurz nach Andreas‘ Anfrage erhielt ich weitere Anfragen von Usern aus dem Forum „Office-Lösung“, ob ich nicht das Tool auch um eine Funktion zum Auslesen der Dateien erweitern könnte.
Gewünscht, gesagt und getan. Ich habe nun einerseits das Tool um die entsprechenden Funktionen erweitert und ein Add-In generiert, welches eine Benutzeroberfläche mit einstellbaren Optionen zur Verfügung stellt und am Ende des Artikels heruntergeladen werden kann.
Die relativ einfache Variante des Tools hatte ich im Januar 2009 in meinem Blog auf meiner Hauptseite publiziert, den Artikel finden Sie hier „Verzeichnisstruktur mit VBA auflisten“.
Der Code im Add-In ist zwar kennwortgeschützt, kann aber von jedem eingesehen und darf auch in eigenen Projekten verwendet werden, solange die Hinweise im Kopfbereich der Module erhalten bleiben. Davon unberührt bleiben jegliche Urheber-, Verwertungs- und Publizierungsrechte, die alle bei mir verbleiben. Das Kennwort zum VBA Projekt lautet „excelticker“. Der enthaltene Code füllt nicht nur eine Excel Tabelle mit den Ordner- und Dateinamen sondern enthält auch Funktionen zum Füllen eines TreeView Steuerelementes mit der Ordnerstruktur und einer ListView mit den in einem Ordner enthaltenen Dateinamen. Am Rande erwähnt, ein paar Hilfsfunktionen ermöglichen den Aufruf des Windows Verzeichnisauswahldialoges und das Entfernen der Titelleiste in einer UserForm. Nachfolgend eine kurze Beschreibung der wesentlichen Funktionen der Module und des Add-Ins.
Es gibt verschiedenste Möglichkeiten, um die Ordnernamen und Dateinamen innerhalb eines Ordners zu ermitteln. Beispielsweise können die in VBA integrierte Funktion „Dir()“ oder Funktionen aus dem „FileSystemObject“ verwendet werden. In Foren sind solche Beispiele relativ schnell zu finden. Aber auch zu Windows API Implementierungen sind im Netz einige Beispiele zu finden. Somit bin ich zwar nicht der Erste, der eine solche Variante implementiert; eine Implementierung in Form eines Add-Ins ist mir jedoch soweit nicht bekannt.
Im VBA Projekt zum Add-In werden Sie die UserForm „MLF_Directories“ finden, die das TreeView, die ListView und ein paar weitere Steuerelemente für die Optionen enthält. Im unteren Bereich befinden sich Schaltflächen zum Beenden des Dialoges, zum Exportieren der Ordnerstruktur und Dateinamen in eine Excel Tabelle, zur Aktualisierung der Daten und zum Öffnen einer ausgewählten Excel Datei.
Ausserdem enthält das Add-In das Modul „MLP_Run“, welches nur ein paar Funktionen zum Erstellen der Menüleiste am rechten Rand von Excel, falls es sich um Excel 2003 handelt. In höheren Excel Versionen wird das Menü zum Add-In im Reiter „Addins“ des Menübandes (Ribbon) erstellt. In einem weiteren Modul „MLP_Api“ befinden sich alle Funktionen, die die Kernfunktionen zum Auslesen der Verzeichnisse betreffen.
Auswahl eines Verzeichnisses per Windows API
Im oberen Bereich des Dialoges zum Add-In kann der Startordner ausgewählt werden. Hierbei kommt die Funktion „mlfpApiBrowse()“ zum Einsatz, welche als Parameter einen Pfad und einen Titeltext für den Windows Standard Verzeichnisauswahldialog erwartet. Damit der Dialog zur Verzeichnisauswahl beim Aufruf direkt das übergebene Verzeichnis anzeigt, wird eine Nachricht via einer Callback-Funktion an den Dialog gesendet.
Füllen des TreeViews mit der Ordnerstruktur
Diese Aufgabe erfüllt die Funktion „mlfpApiDirectoriesTreeview()“ innerhalb des Moduls „MLP_Api“. Die Funktion erwartet ein Handle zu einem UserForm Objekt, welches auch das TreeView enthält. Zudem muss der Steuerelementname des TreeViews, des Startordner und ein Startschlüssel an die Funktion übergeben. Ausserdem sollte beim erstmaligen Aufruf der Funktion festgelegt werden, dass es sich um den Wurzeleintrag vom TreeView handelt. Ein Beispielaufruf der Funktion wäre:
mlfpApiDirectoriesTreeview Me, TreeView1.Name, Path, Key, True
Die Funktion ruft sich selbst innerhalb der Aufrufe auf, um den Scanvorgang für jedes gefundene Unterverzeichnis rekursiv zu wiederholen. Zum Ermitteln der Ordnernamen werden die Windows API Funktionen „FindFirstFileA()“, „FindNextFileA()“ verwendet. Nachfolgender Auszug des Codes enthält die Hauptschleife der Funktion:
Die Variable „t“, eine Struktur vom originären Typ WIN32_FIND_DATA, wurde zuvor einmalig durch den Aufruf der von mir gekapselten Windows API Funktion „apiFindFirstFile()“ gefüllt. Anschließend wird in der Schleife „api FindNextFile()“ solange aufgerufen, bis keine Verzeichnisse mehr gefunden werden. In jedem Schleifendurchlauf wird durch die Überprüfung eines Attributes verifiziert, dass der gefundene Eintrag auch tatsächlich ein Verzeichnis ist.
Füllen der ListView mit Dateinamen eines Ordners
Die Funktion „mlfpApiFilesListview()“ erwartet als Argumente ein Handle zur UserForm, den Steuerelementnamen der zu verwendenden ListView und den Pfad, der nach Dateien durchsucht werden soll. Im Prinzip funktioniert der Code genau wie der Code zum TreeView. Die Prüfung ob es sich bei dem gefundenen Element um ein Verzeichnis handelt ist invertiert, es wird also geprüft ob das Element kein Verzeichnis ist. Der Code filtert jedoch keine Systemdateien oder versteckte Dateien heraus, es sollte aber kein Problem sein, den Code entsprechend zu erweitern. Im Add-In wird die Funktion jedes Mal aufgerufen, wenn ein Eintrag im TreeView selektiert wird.
Auflisten aller Ordner und Dateien in einer Excel Tabelle
Hierfür habe ich die zwei Funktionen „mlfpApiDirectories()“ und „mlfpApiFiles()„ vorgesehen, die letzten Endes eine angepasste Kopie der jeweiligen Funktion zum Füllen des TreeViews und des ListViews sind. Die Übergabeparameter sind entsprechend ebenfalls verändert worden. So erwarten beide Funktionen den Namen der Arbeitsmappe, der Tabelle, der Startzeile und -spalte und ob die Pfade pro Zeile vollständig aufgeführt werden sollen. Letzteres ist relativ simpel und geschieht durch einfaches Kopieren der Einträge der Zeile, die sich oberhalb der aktuellen Zeile befindet.
Einsatzbedingungen für das Add-In
Getestet wurde das Add-In in Excel 2003, 2007 und 2010 auf einem deutschen Windows 7 32-Bit System. Eine englische Version des Add-Ins erscheint demnächst in meinem englischen Blog.
Da hier 32 Bit Windows API Funktionen verwendet wurden, ist das Add-In auf einem 64-Bit System nicht lauffähig. Der Code enthält zudem keine Überprüfung, ob Excel Limitierungen überschritten werden, hier ist beispielsweise die Anzahl der verfügbaren Zeilen gemeint. Die Geschwindigkeit sollte für „normale“ Anwendungsgebiete ausreichend sein, ich hatte ein paar Tests mit sehr großen Ordnern gemacht; im Schnitt brauchte das Add-In zum Einlesen von 65.000 Ordnern zwischen 40 und 50 Sekunden.
Update: das Add-In hat nun die Version 1.25 Build 110811 und wurde am 11.08.2011 upgedated. Die Änderungen habe ich in diesem Artikel „Update auf Version 1.25 zum Add-In zum Auflisten von Verzeichnissen“ aufgelistet.
Prima Tool. Klasse wäre wenn es das auch noch alphbetisch sortieren könnte.
mfg
W. Schaefer