Articles

Excel VBA: Check If File or Folder Exists (DIR) – Open File or Create Folder


In today’s video I’m gonna show you a VBA function that let’s you check whether a file or folder exists. (relaxing beat music) The function that let’s us check for the existence of a file or folder is the dir or D-I-R function. Now, this function takes a path argument. That’s basically an address and it returns the name
of your file or folder. If that name isn’t found then DIR returns an empty string. Now, the attributes arguments is optional and they’re basically the ones that are listed here. Default is vbNormal, which are files with
no specific attributes. Now, you could filter
for specific attributes like read-only files
or filter specifically for folders by using the constants that are listed here. One thing that comes in handy here, and we’re gonna see that in a second, is that we can use wildcards for the path argument. For example, you can use the asterisk sign or the multiplication operator as a wildcard that changes size. Basically, it represents a placeholder for any text that can come in between. Another wildcard that you can use is the question mark. This one represents a fixed length string. If you use it once it means just one character can be variable. So just one character can be anything. So that was the theory. Let’s take a look at an example. (upbeat music) This video is brought to you by Unlock Excel VBA & Macros Course. So if you’d like to learn VBA in a structured way from the ground up, I suggest you check out
the complete course. You’re gonna find the link in the description below the video or in the YouTube cards provided. So we’re gonna do two task here. Task number one is to see how we can use the DIR function to
check if a file exists. If it doesn’t exist, we’re gonna give the user a message, telling them that this
file does not exist. And if it does exist, we could, for example, open that file. In task number two, we’re gonna check if a folder exists. Now, if it doesn’t exist, we will prompt the user with a message box asking them whether they
wanna create that folder. And if they say yes, we’re gonna create the folder for them. So let’s switch to VB Alt + F11. I’ve already created an empty module here and we’re gonna start with our first Sub called File_Exists. So remember, the DIR
function returns a name. And that name is a string, so we can declare a variable for that. I’m gonna actually use FileName. Okay, that can be any name you give it. I normally go with FileName
for these as String. Then we’re gonna do filename=, so since this is a normal data type, it’s not an object, we don’t need the set key word and we’re gonna do with dir. Now, dir is also in
the VBA global library, so you can get to it like this. It’s specific compartment is called the FileSystem class. So you can also get to it like this. I’m gonna give it this path name. Okay, then I’m gonna close brackets. So I’m just gonna go with
the default VB normal. Now, if this doesn’t exist, we’re gonna get an empty string, right. So we can check for that and say, if filename=VBNullString. Now, we can also get to
this through IntelliSense by going to VBA.Constants.vbNullString. Let’s do an End if here. Then we want to inform the user that the file does not exist, okay. So the moment this string is empty, it means file does not exist. Else, now as a first step let’s just get the file name. Once we see that this works, we’re gonna actually open that file. So let’s run this. And we get the file name back. Okay, so that’s the file that I have in this directory. Now, let’s just use
wildcards here as well. So if I do S2 underscore and I’m gonna put star sign here. And for the last one, let’s actually use the question mark here. Instead of the MsgBox
let’s open that file. We’re gonna do workbooks.Open. Now, we need the full pathname, actually gonna be this. And the full filename which is now our FileName variable. Okay, so let’s just check that folder what we have on there. So that’s the VBA folder. I have this S2 sitting here. Now, let’s run this and
see if it opens our file. And our file is opened. Now, let’s move onto the second task where we gonna check if a folder exists. And if it doesn’t exist, we’re actually gonna create that folder. Here we can declare two variables. We can declare the variable called Path. That’s gonna include the entire path together with the folder name. And we can declare the
variable called Folder which is just gonna be our Folder name. Okay, now we can define these. So Path=, let’s just
work with this folder. And I’m gonna add in a
folder that doesn’t exist in the VBA folder. So backslash, let’s call it S12. Now, folder is gonna=dir, our path, okay, which is this one here and since this is a folder it’s VBDirectory. Now we need to check if
this is empty or not, right, because if it’s empty it means it doesn’t exist. So if folder=VbNullString Then let’s do an End if here. Now, right here we wanna give the user the opportunity to create the folder. So we’re gonna ask them with a message box whether they wanna create
that folder or not? And allow them to click on yes or no. So it’s best here if I create a variable to keep the user’s answer in. So I’m gonna dim Answer as VbMsgBoxResult and inside here we’re gonna do answer=msgbox and we’re gonna ask them if they want to create it? Then we’re gonna give them the options to click on yes or no. And let’s just give this
a title Create Path? Now, let’s check their answers. This time I’m gonna use
the select case statement. So select case answer and let’s do End Select here. So case is vbYes. Then we want to make or
create this directory. And the function for that is mkdir. This one, again, is in the global library
so you can get to it like this or to the FileSystem class. Okay and it needs one argument. And that argument is the Path which includes the folder name. And it’s a good coincidence because we’ve also
called our variable Path. So we can go with that. Now, for no or let’s just do Case Else, we just wanna Exit the Sub, right. So in case the folder doesn’t exist, they’re gonna get this prompt. But if the folder does exist right now, we’re not informing them in any way. The Macro is just gonna exit. So we could give them some information and just before we do our End If we can add an Else, add a msgbox saying Folder exists. Okay, so S12 does not exist on here yet. So we should be getting
that message box, okay. Let’s test. Path does not exist. Would you like to create it? Yes. Okay, now let’s just double check. S12 is there. So let’s run this again and see if? We see that folder exists. So this is how you can
use the dir function to check whether a file or folder exists. And depending on the outcome, you can decide what you
wanna do in your code. (upbeat music)

Leave a Reply

Your email address will not be published. Required fields are marked *