This tutorial will show you how to check if a folder exists and create the folder with VBA MkDir if it doesn’t exist. It’s important that you check to see if the folder exists before calling the VBA MkDir statement. If you attempt to use it to create a folder that already exists, you’ll be greeted with a “Run-time error 75: Path/File access error.”
The macros in this tutorial will show you how to prevent this error by checking if the folder exists first.
MkDir is short for “Make Directory” and it does exactly that. If you’re familiar with Unix or Linux, or even with the Windows command prompt, you may already know about similar MkDir functions. The VBA MkDir statement behaves in much the same way.
Use VBA MkDir to Create a Folder
Sub CreateFolder(sFolder As String) 'Check if a folder exists, and if it doesn't, create folder with VBA MkDir If Len(Dir(sFolder, vbDirectory)) = 0 Then MkDir sFolder End If End Sub
We created some VBA Reference Guides to compliment tutorials like this. We filled them with over 180 tips and 135 pre-built macros covering the 100 most important topics in VBA. Refer to them as we explain the rest of this tutorial and it’ll be a lot easier to understand.
All you have to do is pass this subroutine a string storing the directory you want to create, just like in this demo:
Sub Demo() CreateFolder ("C:\MySampleFolder") End Sub
If Dir can’t find the directory, it creates it from scratch using MkDir. If the folder already exists, the macro does nothing. No more run-time error!
I like creating subfolders in the directory where my workbook is saved so I can store text files or PDFs I automatically create based on my data. What kind of uses do you have for creating folders using VBA? Let me know in the comments section!
That’s all for this tutorial. When you’re ready to take your VBA to the next level, subscribe using the form below.
Write better macros in half the time
I see people struggling with Excel every day and I want to help. That's why I developed this unique 3-part free Excel training program to help you quickly learn VBA in a natural setting: right inside Excel. I'm also going to give you my entire personal macro library for free.