Create Folder and Save Excel File using VBA

Create Folder and Save File in the newly created folder using VBA. We use Dir VBA function to check specified new folder or directory already exists. If already exists it quits from the VBA macro. Otherwise creates new folder using MkDir VBA function. It creates new Excel workbook and save it in newly created folder. In this tutorial we have explained examples and step by step instructions for user reference.

VBA Create Folder and Save Excel File

Let us see an example macro VBA Create Folder and save new file in the newly created folder. You can change folder path and file name to suite your requirement. we use Add, SaveAs, Close methods of Workbook. Add method helps to create new workbook. SaveAs method saves workbook in the specified folder. Close method helps to close opened workbook. You can create any file like text, csv, word, PowerPoint, etc

'VBA Create Folder and Save Excel File
Sub VBAF1_Create_Folder_and_Save_File()
    
    'Variable declaration
    Dim sFolderPath As String
        
    'Define Folder Path
    sFolderPath = "C:\VBAF1\Test_Folder\"
        
    'Check Specified Folder exists or not
    If Dir(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"
    
    'Create and Save File (If file is not available)
    Dim Wb As Workbook
    Dim sFileName As String
    
    'File Name
    sFileName = "Sample_File"
    
    '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 in the new folder!", vbInformation, "VBAF1"

End Sub

Output: You can find following output screenshots of above specified VBA Macro examples for your reference.
The first one is when created new folder.

The second one is when created new file in the new folder.

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
• Now you can find new folder and new file inside of folder.

Related Reference 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