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
Make powerful spreadsheets with our free VBA Developer's Guide It’s easy to copy and paste a macro like this, but it’s harder make one on your own. I’ve made hundreds of pre-built macros like this one and I want to send them to you so you can master file I/O, arrays, strings and more.
It’s easy to copy and paste a macro like this, but it’s harder make one on your own. I’ve made hundreds of pre-built macros like this one and I want to send them to you so you can master file I/O, arrays, strings and more.
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.