Create Folder with VBA

With Excel VBA you can create a folder in a specific directory to save your file or files to. Rather than saving files to a pre-existing folder you can create a folder on the fly so to speak which more suits the nature of the Excel file you are creating. For example, you may be creating a monthly budget with VBA and the file is best saved in a folder with the month name. You can use VBA to do this for you in real time. The following is the cod

Sub AFolderVBA()
MkDir "C:\MonthlyFiles\January"
End Sub

As you can see the VBA coding is quite straight forward. There needs to be a folder called MonthlyFilesin the C drive for this to work and after the VBA procedure has run the folder path should look like this;


"C:\MonthlyFiles\January"


'Ensure the drive is in quotation marks.

To have the folder name created by a name in a cell you could do the following;


Sub AFolderVBA1() 'PLace the name of the folder in A1
MkDir "C:\Test\" & [a1]
End Sub

The following video takes you through the procedure in VBA.
 
 
 

Where the name of your folder is in A1 of your Excel file. Perhaps you want to take this procedure one step further with Excel VBA and you would like to create numerous folders and save your files into this folder.

Lets say you had the folder names from cells A1 to A5. Firstly I will create a simple loop. To understand looping in VBA the below article on Looping Constructs gives some background into the process.


Sub AFolderVBA2() 'Excel VBA to make a folder.
Dim i as Integer

For i=1 to 5
MkDir "C:\MonthlyFiles\" & Range("A" & i)
Next i
End Sub

The above procedure is a simple VBA loop which starts at A1 and takes the value in that cell and makes a folder in theC: MonthlyFiles directory.

For more information on looping with VBA see the following: LOOPING