VBA Check If File Exists If Not Create It

VBA Check If File Exists If Not Create It

VBA check If File Exists or not. If not create a new file with 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 File Exists If Not Create It using VBA Dir Function

Let us see an example macro to check specified file exists or not. If it is not available create a new file using VBA Dir function to check file exists or not. First we are checking specified folder is available or not. If file is not available then creates a new file. In this case we creates a new Workbook. You can create any file like ppt, word, CSV, Notepad etc.

'VBA Checking If File Exists If Not Create It using Dir Function
Sub VBAF1_Check_If_File_Exists_If_Not_Create_It_Using_Dir_Function()

    'Variable declaration
    Dim sFolderPath As String
    Dim sFileName As String, sFilePath As String
    
    'Define Folder Path
    sFolderPath = "C:\VBAF1\Files and Folders\"
    
    'Specify File Name which we we are looking for
    sFileName = "Sample.xlsx"
    
    'Create File Path
    sFilePath = sFolderPath & sFileName
    
    'Check Specified Folder exists or not
    If Dir(sFilePath) <> "" Then
        'Check file is available or not
        MsgBox "File already exists!", vbInformation, "VBAF1"
        Exit Sub
    End If
    
    'If file is not available
    Dim Wb As Workbook
    
    'Create New Workbook file
    Set Wb = Workbooks.Add
                  
    'Save File
    Wb.SaveAs sFolderPath & sFileName
    
    'Close Workbook
    Wb.Close
    
	'Display Message
    MsgBox "New file has created successfully!", vbInformation, "VBAF1"
	
End Sub

Output: You can find output of the above macro code.
VBA Check If File Exists If Not Create It

VBA Checking If File Exists If Not Create It using FileSystemObject(FSO) Object

Let us another example macro to check specified file exists or not. If it is not available create a new file using VBA FileSystemObject(FSO) to check file exists or not.

'VBA Check If File Exists If Not Create It
Sub VBAF1_Check_If_File_Exists_If_Not_Create_It_Using_FSO()

    'Variable declaration
    Dim sFolderPath As String
    Dim sFileName As String, oFile As Object, bStatus As Boolean
    
    'Define Folder Path
    sFolderPath = "C:\VBAF1\Files and Folders\"
    
    'Specify File Name which we we are looking for
    sFileName = "Sample2.xlsx"
    
    'Create FSO Object
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    
    'Check Specified Folder exists or not
    If oFSO.FolderExists(sFolderPath) Then
        'Check Specified file exists or not
        If oFSO.FileExists(sFolderPath & sFileName) Then
            'If file is available
            MsgBox "File already exists!", vbInformation, "VBAF1"
            Exit Sub
        End If
    End If
    
    'If file is not available
    Dim Wb As Workbook
    
    'Create New Workbook file
    Set Wb = Workbooks.Add
                  
    'Save File
    Wb.SaveAs sFolderPath & sFileName
    
    'Close Workbook
    Wb.Close
    
    'Display Message
    MsgBox "New file 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.

VBA File and Directory Functions in Excel Related Posts

4 thoughts on “VBA Check If File Exists If Not Create It”

Leave a Comment

Your email address will not be published. Required fields are marked *