VBA Checking If Folder Exists If Not Create It
VBA Check If Folder Exists If Not Create It in specified name using VBA in Excel. We are using 2 methods to check folder exists or not. Those are Dir VBA function and FileSystemObject object. In the following tutorial let us see an example macro code. And also see the step by step instructions to run vba code in the visual basic editor(VBE) window.
Example to Check If Folder Exists If Not Create It using VBA Dir Function
Let us see an example macro to check specified folder exists or not. If it is not available create a new folder using VBA Dir function to check folder exists or not. First we are checking specified folder is available or not. If it exists, then displays message on the screen. If doesn’t exists, creating new folder using VBA MkDir function. After successfully creating folder displaying message on the screen for user notification.
'VBA Checking If Folder Exists If Not Create It using Dir Function Sub VBAF1_Checking_If_Folder_Exists_If_Not_Create_It_Using_Dir_Function() 'Variable declaration Dim sFolderPath As String Dim oFSO As Object 'Define Folder Path sFolderPath = "C:\VBAF1\Files and Folders\" 'Check Specified Folder exists or not If Dir(sFolderPath) <> "" Then 'If file is available MsgBox "Folder already exists!", vbInformation, "VBAF1" Exit Sub End If 'If folder is not available MkDir sFolderPath 'Display Message MsgBox "New folder has created successfully!", vbInformation, "VBAF1" End Sub
Output: You can find following output screenshot for your reference. You can see different outputs in the screen shot. If folder available it displays first message. 2nd message displays when folder is created newly.
VBA Checking If Folder Exists If Not Create It using FileSystemObject(FSO) Object
Let us another example macro to check specified Folder exists or not. If it is not available create a new Folder using VBA FileSystemObject(FSO) object to check Folder exists or not. In the below example VBA MkDir function helping us to create new folder.
'VBA Checking If Folder Exists If Not Create It using FSO Object Sub VBAF1_Check_If_Folder_Exists_If_Not_Create_It_Using_FSO_Object() 'Variable declaration Dim sFolderPath As String Dim oFSO As Object 'Define Folder Path sFolderPath = "C:\VBAF1\Files and Folders2\" 'Create FSO Object Set oFSO = CreateObject("Scripting.FileSystemObject") 'Check Specified Folder exists or not If oFSO.FolderExists(sFolderPath) Then 'If Folder is available MsgBox "Folder already exists!", vbInformation, "VBAF1" Exit Sub End If 'If folder is not available MkDir sFolderPath 'Display Message MsgBox "New folder has created successfully!", vbInformation, "VBAF1" End Sub
Instructions to use VBA Macro Code
Here are the instructions to use above macro in Visual basic editor.
- Open Visual Basic Editor(VBE) by clicking Alt +F11
- Go to code window by clicking F7
- Copy above specified macro or procedure
- Paste above copied code in code window
- Run macro by clicking F5 or Run command
- you can find above specified input and output screenshot for your reference and better understand.
Related Articles
You can also learn complete details like syntax, example and etc by clicking on the following buttons.