Performing Operations with Directories in Excel VBA

Using VBA, you can create directories and delete them if they are empty. The following example demonstrates creating a subdirectory, copying a file into it, moving another file there, then deleting both files, and finally removing the subdirectory.

Between each step, a list of currently existing files in the subdirectory is displayed for verification. For this purpose, the procedure ListFilesInPath() is used.

Sub ListFilesInPath(path As String)
    Dim fileName As String
    Dim output As String
    fileName = Dir(path & "\*.txt")
    output = ""
    Do While fileName <> ""
        output = output & " " & fileName
        fileName = Dir
    Loop
    MsgBox output
End Sub

The directory operations are implemented in the following procedure:

Sub DirectoryOperations()
    Dim basePath As String, subPath As String
    basePath = ThisWorkbook.Path
    subPath = basePath & "\Subfolder"
    On Error GoTo ErrorHandler
    ' Create subdirectory
    MkDir subPath
    ' Copy a file in the base directory
    FileCopy basePath & "\lines.txt", basePath & "\linesCopy1.txt"
    ' Copy a file into the subdirectory
    FileCopy basePath & "\lines.txt", subPath & "\linesCopy2.txt"
    ' Move a file into the subdirectory
    Name basePath & "\linesCopy1.txt" As subPath & "\linesCopy1.txt"
    ' List files in the subdirectory
    ListFilesInPath subPath
    ' Delete files from the subdirectory
    Kill subPath & "\linesCopy1.txt"
    Kill subPath & "\linesCopy2.txt"
    ' List files in the subdirectory again (should be empty)
    ListFilesInPath subPath
    ' Remove the now-empty subdirectory
    RmDir subPath
    Exit Sub
ErrorHandler:
    MsgBox Err.Description
End Sub

Explanation:

• The function MkDir() creates a directory. It requires a string parameter specifying the name (or path) of the directory to create.
• The functions FileCopy() and Name() have been introduced earlier; they are used here to copy and move/rename files between directories.
• The function RmDir() deletes an empty directory. Like MkDir(), it requires a string parameter specifying the directory to remove.