I was updating my popular blog post Create a Folder with VBA and I realised while I was making the video I had left out a crucial element - what if the folder already exists. This will cause the code to bug out and this is not desirable. So in order to address that we can trap the existence of a folder.
The following short video shows you how to create a folder with VBA and how to avoid an error if the folder already exists. The following Excel file is the output from the video.
Avoiding errors in code is highly desirable. and the code to do that when making a folder is about using the combination of the Dir command and the MkDir command.
The Dir will either produce a ““ null result if the folder exists.
Or it will produce a period “.” because of the Boolean nature of this result we can trap the null result and create a new folder and ignore the period result meaning nothing happens.
Sub FolderVBA2()
'Purpose: Check if folder exists - Source TheSmallman.com
Dim i As Integer
For i = 1 to 5
str = "C:\MyFiles\" & Range("A" & i) & "\"
fol = Dir(str, vbDirectory)
IF fol = "" Then MkDir "C:\MyFiles\" & Range("A" & i)
Next i
End Sub
The code to check if a folder exists is as follows:
In the above example there are 5 folders to check. If any of the 5 folders exists then the code will do nothing. If on the other hand the folder does not exist then a new folder is created.
The string either produces “ “ or “.” as there are only two possibilities the IF statement can test for “ “ (blank) and the new folder is make with the MkDir statement.
Rookie error
"\"Remember to include the back slash in the code for the Str (string) if you leave it out all sorts of problems occur. We could test for its existence but just do the right thing : )
The following Excel file is from the video and has all the examples inside of it.
