(C) 2009 - 2018 by Mourad Louha · Alle Rechte vorbehalten

Verzeichnisse und Dateien mit Windows API Funktionen in Excel auflisten

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.

Maninweb.de Add-Ins

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.

Mourad Louha

Über den Autor · Mourad Louha

Mourad arbeitet seit 2005 als selbständiger Softwareentwickler und Trainer und lebt in Aachen, gelegen am Dreiländereck Belgien, Deutschland und Niederlande. In seiner Freizeit engagiert er sich in Online Communities rund um Office, schreibt beim Excel Ticker Artikel zu Office & Co. und betreibt weitere Community Projekte in Zusammenarbeit mit Freunden und Partnern, wie das Forum zu Microsoft Office 365 und Office 2013, den Excel Soccer oder den Excel Formel Übersetzer. Für sein außergewöhnliches Engagement wurde er seit Januar 2011 jährlich von Microsoft als Microsoft Most Valuable Professional (MVP) ausgezeichnet.

 
Comments

Das Tool ist großartig.

Für mich wäre es noch sehr hilfreich, wenn das letzte Änderungsdatum in die Auflistung übernommen würde.

Danke aber für die Programmierarbeit!

Hallo Ulli,

Vielen Dank!

Deine Idee nehme ich gerne auf, kann Dir allerdings nicht sagen, wann ich dazu komme, die nächste Version freizugeben, die auch weitere Verbesserungen enthalten wird. Momentan hat mein Buch Vorrang, aber wie gesagt, in Planung ist einiges.

Grüße :-)

Hallo ! Das Tool bringt bei mit einen „AUTOMATISIERUNGSFEHLER“
Ich verwende Office 2007 – 32 Bit auf Win 7 – 64 Bit

Im VA Editor bleibt er beim Debugging/Kompilierien in der Zeile „Private mlvhColors As Collection“ stehen und markiert diese. Mehr geht nicht da der beschriebene Fehler sofort wieder kommt.

Hallo DerTurl

Da scheint möglicherweise bei Deiner Installation etwas nicht in Ordnung zu sein, da Collection ein Objekt der VBA-Bibliothek ist.

Aber bevor ich mich da festlege, führe bitte folgenden Test einmal durch: Lege eine neue Arbeitsmappe an und speichere diese als XLSM oder XLSB-Datei ab. Wechsele anschließend in den VBA-Editor, z.B. über Alt+F11, und füge ein neues Modul hinzu. Kopiere folgenden Code in das Modul:

Public Sub Test()
Dim objTest As Collection
Set objTest = New Collection
objTest.Add "Hallo!", "K1"
MsgBox objTest.Count & " = " & objTest(1)
Set objTest = Nothing
End Sub

Anschließend setze den Cursor irgendwo in einer Codezeilen und drücke F5 zum Ausführen des Testmakros. Was passiert?

Gruß

Hallo Mourad,

Danke für Deinen Beispielcode. Ich hoffe, ich kann das Thema hier nochmal hochholen. Folgenden Code habe ich bereits mithilfe anderer Blogs zusammenstellen können. Dieser geht alle Dateien des Ordners durch, öffnet diese, wendet das Makro „Einfügen1“ an und schließt die Dateien.

Könnte man den Code insofern erweitern, dass das Makro das komplette Verzeichnis inkl. Unterordnern durchgeht? Hierbei enthalten die Unterordner ebenfalls Unterordner, diese weitere Unterodner usw..
Mein Gedankenansatz wäre hier eine rekursive Funktion, die in dem Hauptordner alle Excel abarbeitet, danach in die 2.Ebene geht und hier in allen Ordnern die enthaltenen Excel abarbeitet, danach in die 3.Ebene geht, usw.

Wäre super, wenn Du mir hier helfen könntest! :/

Danke und liebe Grüße!

Sub Alle_Exceldateien_nacheinander_öffnen()

Dim sFile As String
Dim sPath As String

sPath = „D:\Test\“
sFile = Dir(sPath & „*.xlsx“)

Do While sFile „“
Workbooks.Open sPath & sFile
sFile = Dir()
Call Einfügen1
ActiveWorkbook.Save
ActiveWorkbook.Close
Loop
End Sub

Hallo Wowa,

ich habe den Kommentar freigeschaltet, damit Du merkst, dass ich den Kommentar gesehen habe. Momentan habe ich sehr wenig Zeit und bin auch unterwegs, aber wenn Du möchtest, schaue ich es mir am Wochenende an und melde mich hier nochmal.

Gruß :-)

Hallo Mourad,

Das wäre super! Danke schon Mal! :)

Hallo Wowa,

habe Dir ein Beispiel per Mail an die hier angegebene Mail-Adresse zugesandt.

Gruß :-)

Auflisten aller Ordner und Dateien in einer Excel Tabelle

Funktioniert leider bei mir (Excel 2007) nicht.

Ich habs durch Probieren gefunden.

Hallo ws,

prima, was war denn das Problem?

Viele Grüße, Mourad

Benno Savioli

Lieber Herr Louha,
vielen Dank für das Add-in! Ich habe ähnlichen Code schon seit längerem immer mal wieder in Benutzung für meine kleinen Arbeitsprojekte, bin aber jetzt auf ein Problem gestoßen, an dem ich mir die Zähne ausbeisse: kyrillische Schriftzeichen. Es ist (ausser mit Range.Copy Range.Paste) mir nicht möglich, mittels VBA EXCEL-Zelleinhalte in kyrillischer Schrift zwischen Zellen zu kopieren, da bei der Zuweisung zu einer stringvariablen vorzugsweise „?????“ zurückgeliefert werden und natürlich gilt das auch für die Speicherung von ausgelesenen Dateinamen. Sind Sie dem Problem schon mal begegnet? Das Add-In jedenfalls löst das Problem nicht. Falls Sie einen guten Hinweis hätten – ch würde es gerne ausprobieren und rückmelden – 2 tage Internetrecherche haben mich jedenfalls der Lösung noch nicht näher gebracht, allerdings bin ich auch kein professioneller Programmierer – eher ambitionierter Amateur?
Ihnen noch einen schönen 1. Mai

MfG
Benno

Guten Tag Herr Savioli,

vielen Dank für’s Lob, freu mich :-)

Der VBA-Editor kann kein Unicode, deshalb können Sie dort keine Unicode-Strings direkt in der Form strString = „Pусский“ ablegen. Was allerdings geht ist, den Zellinhalt mit Unicode Zeichen von einer Zelle in die andere zu kopieren. Z.B. steht in A1 = Pусский, dann funktionieren:

Sub Test()

ThisWorkbook.ActiveSheet.Cells(2, 2).Value = _
ThisWorkbook.ActiveSheet.Cells(1, 1).Value

End Sub

Sub Test2()

Dim strUnicode As String

strUnicode = ThisWorkbook.ActiveSheet.Cells(1, 1).Value
ThisWorkbook.ActiveSheet.Cells(3, 3).Value = strUnicode

End Sub

Ebenso ging dann nicht den String im Direktfenster anzuzeigen, Debug.Print strUnicode liefert Käse. Was das Add-In betrifft, so verwendet dieses die MSCOMCTL-Bibliothek, die nicht unicode-fähig ist. Somit müsste das Add-In umprogrammiert werden, so dass es dann diese Bibliotheken nicht mehr benutzt. Bei dem Listview ginge es vielleicht noch relativ einfach, bei dem TreeView (Baumstruktur) nicht.

Nachtrag: habe es mal geprüft, das würde, wenn API verwendet wird, schon aufwändiger, da dann die Unicode Versionen der API’s zum Einsatz kämen. Ein paar informative Links zu Unicode und VBA:
» http://blog.nkadesign.com/2013/vba-unicode-strings-and-the-windows-api/
» http://www.mrexcel.com/forum/excel-questions/422934-function-shows-unicode-code-character-3.html
» http://www.xtremevbtalk.com/showthread.php?t=288285
» http://www.vb-zentrum.de/unidateihandling.html

Viele Grüße,
Mourad

Sehr geehrter Herr Louha,

das Auflisten von Dateien funktioniert hervorragend. Ich brauche allerdings Daten zu den Dateien wie Datum und Dateigröße. Wie kann ich das anzeigen lassen?

Gruß ws

Hallo WS,

dies ist im Tool nicht vorgesehen und müsste dementsprechend im Code hinzugefügt werden. Sie können dies durchführen, indem Sie folgende Ergänzungen in dem Code machen. Im Modul MLP_Api die Funktion mlfpApiFiles suchen und dort die Zeile „If Order Then“. Nach dem „End If“ zwei Zeilen zur Ermittlung der Dateigröße und dem Dateidatum einfügen. Das sieht dann im Ergebnis wie folgt aus:

' Order...
  If Order Then
   .Cells(y, ColumnLast + 1).Value = _
    mlchApiStringPrefix & Path & "\" & f 
  End If
' Filedata...
 .Cells(y, ColumnLast + 2).Value = FileLen(Path & "\" & f)
 .Cells(y, ColumnLast + 3).Value = FileDateTime(Path & "\" & f)

Nun in der UserForm MLF_Directories die Funktion mlfhExport suchen und in dessen Code die Zeile „If CBool(CHK_0005.Value) Then“ suchen. Direkt danach eine neue Zeile mit dem Inhalt „l = l + 3“ einfügen, so dass Sie erhalten…

' Order...
  If CBool(CHK_0005.Value) Then
'   Add...
    l = l + 3   
'   With...
    With Application.Workbooks(b).Worksheets(s)

Danach werden dann Größe und Datum für die gefundenen Dateien in neue Spalten hinter dem Link in der Ausgabe des Tools hinzugefügt.

Gruß, Mourad Louha

Hallo Herr Louha,

ich bin auf der Suche nach einer Moeglichkeit einen Ordner mit verschiedenen Unterordnern und ungefaehr 14000 Dokumenten (xlsx, pdf, word, jpeg) in Excel einzulesen und zwar unter den Stichwoertern Dateiname, Dateityp, Datum, Beschreibung und Weblink.
Ist dies generell moeglich ?
LG

Pipo

Hallo Pipo,

das Einlesen der Dateinamen und Ordnernamen selbst sollte mit dem Tool aus diesem Beitrag bzw. dem verlinkten Beitrag zum Update gehen. Ebenfalls lassen sich einige Eigenschaften, wie Datumsangeben oder Größe von Dokumenten auslesen. Unter Zuhilfenahme der DSO-Bibliothek (nach DSOFile.dll googlen) lassen sich auch für Office-Dokumente und andere Dokumente (PDF weiss ich aber nicht) deren Eigenschaften auslesen. Vielleicht wäre auch eine Möglichkeit, die Docs alle einzulesen und anschließend per Excel nach Kriteren zu filtern.

Viele Grüße

Hallo Mourad,

erstmal super Arbeit für dein Programm. *Daumen Hoch*
Ich bin auf der Suche nach einem Programm, dass den Ordnernamen wo die Dateien drin liegt (nicht den ganzen Pfad) in eine gewünschte Zeile übernehmen kann.

Bsp: C:\Test1\Name_A , in Excel Tabelle soll dann stehen: Name_A

Ich habe mit deinem Programm derzeit ein bisschen rumprobiert und es leider nicht hinbekommen.
Ist es überhaupt möglich es so zu programmieren?

Vielen Dank vorab!

LG Kassierer

Hallo,

das würde sicherlich gehen. Entweder per Formel oder per Code. Ich schaue mir das mal an und melde mich später hier in den Kommentaren nochmal.

Du hast aber hier Version 1.25 heruntergeladen, richtig?

Viele Grüße,
Mourad

Hallo,

ich habe Dir mal eine Version erstellt, die Dir den Ordner auf unterster Ebene sowie den Dateinamen in je einer Spalte rechts in der Tabelle nochmal auflistet. Du kannst diese Version hier Zusatzfeatures zur Version 1.25 vom Verzeichnislister herunterladen.

Viele Grüße,
Mourad

Hallo,

ich habe mich bisher mit Dir() durch die Struktur eines Verzeichnisses gekämpft. Als ich jetzt „mein“ Verzeichnis in C:\User durchsuchen wollte, habe ich einen Laufzeitfehler 70 erhalten. Auf der Suche nach einer Lösung habe ich Ihr Addin gefunden.

Meine Kenntnisse in VBA sind recht bescheiden, jedoch ist es mir jetzt gelungen, dass Add-In zu installieren. Jetzt fehlt mir aber ein Beispiel, wie man das Add-In einsetzen kann.

Hallo Werner,

wenn Du das Add-In hast erfolgreich installieren können, müsstest Du im Excel-Menüband den Reiter Add-Ins finden. Und dort die zwei Einträge Infos und Verzeichnisse. Ein Klick auf letzteres öffnet einen Dialog. In der ersten Zeile kannst Du mit dem gelben Symbol einen Startordner auswählen. Danach werden die Dateien und Unterordner eingelesen. Das kann je nach Größe etwas dauern. Im unteren Bereich findest Du ein paar Optionen. Die Symbole unten – von links nach rechts – bedeuten Aktualisieren (also neu einlesen), In Excel-Tabelle schreiben, Einstellungen (ggf. inaktiv) sowie Dialog beenden. Hoffe, das hilft Dir weiter.

Viele Grüße,
Mourad

Hallo Mourad,

ich habe mich entschlossen, Dein Tool nicht einzusetzen, habe aber jetzt das Problem, es wieder los zu werden. Ich hatte die xla-File in ein spezielles Verzeichnis kopiert und von dort aktiviert. Jetzt finde ich keine Lösung, wie ich das Tool wieder aus meinem Excel entfernen kann. Bitte um Infos dazu.

Vielen Dank.

Gruß Werner

Hallo Werner,

es kann (leider) vorkommen, dass nach vorausgehender Deaktivierung des Add-Ins über die Excel-Optionen das Menü erhalten bleibt.

Zum Deaktivieren des Add-Ins die Excel Optionen aufrufen und unter Add-Ins / Verwalten / Excel-Add-Ins / Los im erscheinenden Dialog den Haken zum Add-In entfernen und Ok anklicken. Excel beenden und neu starten. Ist dann immer noch im Menüband im Reiter Add-Ins das Menü zu meinem Add-In zu sehen, klicke eines der Symbole mit der rechten Maustaste an und wähle im Kontextmenü Benutzerdefinierte Symbolleiste entfernen. Das Entfernen bestätigen, Excel beenden und neu starten. Danach sollte das auch das Menü verschwunden sein.

Gruß
Mourad

Danke für die Antwort. Es hat geholfen-

Leave a Reply