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.