What Folder Operation are covered?
- Create Folder or VBA.MkDir
- Delete Folder or VBA.RmDir
- Check Folder Exists or VBA.Dir
- Open Folder with Shell.Application
To perform advanced functions on Folders or File System in Windows, Google for File System Objects.
Lets assume, we need to create a new folder in D: Drive after checking whether any folder with same name exists in same 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.
12345678910111213141516Private 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.
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.12345678Sub 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.
Previous Post: How to Insert Formula into Excel – How to Use Excel Formula in Macro
Next Post: Reading Process List From Task Manager
Windows Folder Operations