Excel VBA – Create, Delete, Exists, Copy, Move, Open Folder Operation
Be it any operation on a system directory, like any of these:
- List Folder or check folder exists?
- Create/Delete Folder. (Table1)
- Copy or Move Folder. (Table2)
- Current working directory
- Get or Set file or folder attributes
- Open a folder from Excel
- 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:
Command Description Syntax Dir 1. 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 ] )
MkDir Used to Create a New directory or Folder MkDir (Path) RmDir Delete a System folder or a directory RmDir (Path) ChDir Change Current working directory or Active folder ChDir (Path) CurDir Get current working or active windows directory CurDir GetAttr Get File or Folder Attributes. Returns the vbFileAttribute* VBA.GetAttr (“File_Or_Folder_Path”) SetAttr Change Attributes of a Folder or File.
Hint: Hide a Folder or make it Read-Only
VBA.SetAttr (“File_Or_Folder_Path”, vbFileAttribute*) Glossary * vbFileAttribute vbNormal(0)
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.
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”)’
Command Description Syntax CreateFolder Create new folder FileSystemObject.CreateFolder (Path) CopyFolder Copy Folder from One location to another FileSystemObject.CopyFolder (Source, Destination, [OverWriteFiles Flag]) MoveFolder Move the File to different path FileSystemObject.MoveFolder (Source, Destination, [OverWriteFiles Flag]) DeleteFolder Delete Folder FileSystemObject.DeleteFolder (Path) CreateTextFile Copy File CreateTextFile (Path) CopyFile Copy File CopyFile(Source,Destination) MoveFile Move File MoveFile(Source,Destination) DeleteFile Delete File from specific path DeleteFile(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.VBA Code - Check if Folder ExistsVisual Basic1234567891011121314Private Sub Check_Folder_Exists()'Get Root Folder NameRoot_Path = "D:TestFolder"'Folder Already Exists or not?'VBA.Dir Function can be used to find whether a folder already exists or not.Folder_Status = VBA.Dir(Root_Path, vbDirectory)If VBA.Trim(Folder_Status) = "" ThenMsgBox "Folder Does Not Exist"ElseMsgBox "Folder Already Exists"End IfEnd Sub
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.
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.
- Create a New Excel Workbook and Press Alt + F11.
- Then copy paste the below code and run it by pressing F5.
- 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.Visual Basic12345678910111213141516Private Sub Create_Folder()'Get Root Folder NameRoot_Path = "D:TestFolder"'Folder Already Exists or not? VBA.Dir Function can be used to find whether a folder already exists or not.Folder_Status = VBA.Dir(Root_Path, vbDirectory)ThisWorkbook.Sheets(1).Cells(2, 1) = Folder_StatusIf VBA.Trim(Folder_Status) = "" ThenVBA.MkDir Root_Path 'VBA.RmDir can be used to Delete a Directory or FolderMsgBox Root_Path & ": Folder Created"ElseMsgBox "Folder Already Exists"End IfEnd Sub
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.Open Folder From Excel VBAVisual Basic12345678Sub Open_Folder()Dim Launch_App As ObjectSet Launch_App = CreateObject("Shell.Application")'File_Path = Sheets("Sheet1").Cells(1, 1) orFile_Path = "D:\TestFolder"Launch_App.Open (File_Path)End Sub
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.
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.Excel VBA - Copy Move Folder or DirectoryVisual Basic12345678910111213141516Sub VBA_Copy_Folder()'Add Reference to Microsoft Scripting RuntimeDim fso As FileSystemObjectDim sourcePath As String, targetPath As String'Before Copying Check if Both Folder existsIf fso.FolderExists(sourcePath) And fso.FolderExists(targetPath) Then'Copy Folderfso.CopyFolder sourcePath, targetPath'Move Folderfso.MoveFolder sourcePath, targetPathEnd IfEnd Sub
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: How to Insert Formula into Excel – How to Use Excel Formula in Macro
Next Post: Reading Process List From Task Manager
Excel VBA – Folder Operation – Exists, Create, Copy, Delete Directory