Search and list files with VBA in Microsoft Excel

The Dir () function is very useful for finding files and editing a list of files. In a first application it should be determined whether there is a file with the name text.txt in the directory in which the application is located:

Sub DateiSuchen()
' Suche mit Suchmuster
If Dir(ThisWorkbook.Path & "\test.txt") <> "" Then
MsgBox "Datei test.txt gefunden"
Else
MsgBox "Datei test.txt nicht gefunden"
End If
End Sub

The Dir () function returns the name of a file that corresponds to the specified search pattern. You can also use wildcards for single characters (?) Or multiple characters (*). In this example, a specific file name was searched for, without placeholders. The return value is either the name of the file (if it exists) or an empty one String (if the file does not exist). With the help of the second application, a list of all files that match a certain search pattern is output:

Sub DateiListe()
Dim DateiName As String
Dim Ausgabe As String
' Suche mit Suchmuster
DateiName = Dir(ThisWorkbook.Path & "\*.txt")
Ausgabe = ""
Do While DateiName <> ""
Ausgabe=Ausgabe&""&DateiName
' Suche mit ursprünglichem Suchmuster
DateiName = Dir
Loop
MsgBox Ausgabe
End Sub

First, the Dir () function is called with a parameter. It states that files with the file extension .txt are searched for. The name of the first file that corresponds to the search pattern is saved in the variable FileName (e.g. a.txt). A loop is then started. If a file was found, then the variable FileName is not empty, so the loop is executed.

The name determined is collected in an output variable. The Dir () function is called again within the loop, this time without parameters. This results in the search for the next file that matches the original search pattern. In this way, the loop continues until all files that match the same search pattern have been found. Finally, all collected file names are output.

Was the explanation to "Search and list files with VBA in Microsoft Excel"Helpful? Rate now:

That's what visitors have been looking for

  • suche nach datei ohne zeichen

Weitere Erklärungen zu Skriptsprachen