• Excel VBA – Create, Delete, Exists, Copy, Move, Open Folder Operation

    Be it any operation on a system directory, like any of these:

    1. List Folder or check folder exists?
    2. Create/Delete Folder. (Table1)
    3. Copy or Move Folder. (Table2)
    4. Current working directory
    5. Get or Set file or folder attributes
    6. Open a folder from Excel
    7. and so on …

    Yes. It is all possible from within the Excel vba code. You just have to  use this Quick reference Table.

    And that is not the end. There is more examples as well …

    Note: In a hurry? Copy sample codes, just below the table.

    Table1 – VBA Commands for Folder Operations:

    CommandDescriptionSyntax
    Dir1. Widely used to check for folder or file existence.
    2. Scans the directory path passed as parameter & returns the string that matches the pattern.
    VBA.Dir ( pathname [ ,attributes ] )
    or VBA.FileSystem.Dir
    MkDirUsed to Create a New directory or FolderMkDir (Path)
    RmDirDelete a System folder or a directoryRmDir (Path)
    ChDirChange Current working directory or Active folderChDir (Path)
    CurDirGet current working or active windows directoryCurDir
    GetAttrGet File or Folder Attributes. Returns the vbFileAttribute*VBA.GetAttr (“File_Or_Folder_Path”)
    SetAttrChange Attributes of a Folder or File.
    Hint: Hide a Folder or make it Read-Only
    VBA.SetAttr (“File_Or_Folder_Path”, vbFileAttribute*)
    Glossary
    *vbFileAttributevbNormal(0)
    vbReadOnly(1)
    vbHidden(2)
    VbSystem(4)
    vbVolume(8)
    vbDirectory(16)
    vbArchive(32)
    vbAlias(64)

    Except for hide or lock folders, which is in a separate article, most processes are listed here.

    Either you create a folder or delete already existing one, copy it to a new location, move, rename, open. All is possible right from within an Excel VBA macro code.

    Also Read: VBA Macro - Choose Outlook Email Account - To Send Mass Mail

    Table2 – VBA with FileSystemObject:

    To use the Filesystemobject in VBA code, add reference to “Microsoft Scripting Runtime” Library.

    Or create a late-binding object to this library with command ‘set fsp = CreateObject(“scripting.filesystemobject”)’

    CommandDescriptionSyntax
    CreateFolderCreate new folderFileSystemObject.CreateFolder (Path)
    CopyFolderCopy Folder from One location to anotherFileSystemObject.CopyFolder (Source, Destination, [OverWriteFiles Flag])
    MoveFolderMove the File to different pathFileSystemObject.MoveFolder (Source, Destination, [OverWriteFiles Flag])
    DeleteFolderDelete FolderFileSystemObject.DeleteFolder (Path)
    CreateTextFileCopy FileCreateTextFile (Path)
    CopyFileCopy FileCopyFile(Source,Destination)
    MoveFileMove FileMoveFile(Source,Destination)
    DeleteFileDelete File from specific pathDeleteFile(Path)

    This is not the complete list for Filesystemobject. There are more function to it that can read file content & do more processing. We have listed only the required commands specific to the topic.

    Now, lets get some sample codes.

    1. VBA Code – Check if Folder Exists?

    There is inbuilt function within VBA class collection itself. Dir function can be used for this purpose.


    If the folder exists already, then the Dir function will return the folder name. If it does not exist then it will return blank or spaces.

    Also Read: VBA Macro to - Delete Shapes in Excel - Workbook Objects Boxes

    2. Excel VBA: Create/Delete Folder

    Assume, we need to create a new folder in D: Drive after checking whether any folder with same name  exists the path. We can do this with the following steps and code.

    1. Create a New Excel Workbook and Press Alt + F11.
    2. Then copy paste the below code and run it by pressing F5.
    3. A folder with name “TestFolder” will be created in  D: Drive.

    Note: In the VB Editor, type VBA.FileSystem (Do not copy paste, type this command), and it will display display the available functions for Folder or File operations.

    Comments are provided inside the code to explain about each function used.

    3. Open Folder From Excel

    Now, we have created the TestFolder. IF we need to open the Folder from the code itself and want to display it in the desktop, use the below code.

    In this code, an instance for the object “Shell.Application” is created and pass the Folder path for the .Open Function.

    To perform advanced functions on Folders or File System in Windows, Google for File System Objects.

    Also Read: VBScript Extract Data - Web Scrape - Parse HTML

    4. Copy or Move Folder – Using Excel VBA

    This code used the commands from the Table2. Again, do not forget to add reference to Microsoft Scripting Runtime either thru early or late binding.

     

    I guess, we have covered a lot more on this topic. If any more reference is requires, please leave a comment. We will add good samples in our page.


    Previous Post:
    Next Post: