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.
Write better macros in half the time
I see people struggling with Excel every day and I want to help. That's why I'm giving away my personal macro library for free. This powerful gift lets you automatically import all my macros directly into your spreadsheet with just one click.
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
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!
Fortunately, you don’t have to memorize how to check if a folder exists. Just install the wellsrPRO Excel Add-in and you’ll be able to automatically import the macros in this tutorial directly into your VBA editor without ever leaving Excel!
If you want to make yourself look even better at Excel, check out my Excel VBA Store. The Excel VBA Store has the latest add-ins, online courses, books, and software to boost your VBA skills and automate Excel.