Hello Excellers and welcome back to another #McaroMonday #Excel blog posit in my Excel Macro 2020 series. Today I will show you how to write some code that will allow you to check if an Excel folder exists. It’s very useful, and I have used it many times.
Starting The VBA Macro.
First, you will need to open the Visual Basic Editor. There are two ways to do this.
- Either by hitting ALT +F11 or
- Selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result.
Watch The Video.
Before you begin to write any code you need to decide where to store the code. You have a choice.
- To store your code either in your Personal Macro Workbook or
- Store it in your current workbook
What Is The Difference In These Locations?.
Well, if you save the code in your Personal Macro workbook it will be available in any Excel workbooks. If you store it in the current workbook then use is restricted to that workbook. In this instance, I may want to reuse the code so I will store it in my Personal Macro Workbook. When you select this option then Excel creates (if it is not already created) this workbook and saves the macro in that location. By default, this macro workbook is named Personal.xlsb. This is a very useful macro which can be used over and over again. I therefore still want to save it in my personal macro workbook.
If you want more details on creating and updating your personal macro workbook then I recommend my blog posts below.
Creating and Updating Your Personal Macro Workbook
Why Is My Personal Macro Workbook Not Loading Automatically?
Create A Shortcut To Your Personal Excel Macro Workbook
I want to use this code over and over again so I will choose at this time to save it in my Personal Macro Workbook. I can then check my folder exists.
Preparing To Write The Code.
First, I need to start off the process by inserting a New Module. Do this by selecting the Personal.xlsb workbook, then Insert Module. Type Sub then the name of your macro. I this example I have named my macro CheckMyFolder.
[stextbox id=’info’]
Sub CheckMyMacro
End Sub [/stextbox]
Declaring Variables.
Next, I need to declare any variables I want to use in the code. A variable is simply seen as a memory container to hold values. I this macro I declare two variables. One is for the Folder we are looking to see exists, the other the information to display to the user if it exists or not. Both variables are data type String.
[stextbox id=’info’]
Dim strFolderName As String
Dim strFolderExists As String
[/stextbox]
Setting The Folder Path.
The next action is to assign the Folder Path the variables strFolderName. This is simple as can be seen in the code below. This is the folder we are checking exists on our computer. You can see in my example I am looking to check if the folder “Sales” exits on my C drive.
[stextbox id=’info’]
strFolderName = “C:\Sales\”
[/stextbox]
Using The Dir Command.
The Dir command is used to get the Folder Name into the second variable This was created at the beginning of the macro. Dim strFolderExists As String
Dir returns a String representing the name of a file, directory, or folder that matches a specified pattern or file attribute, or the volume label of a drive. Sounds like what we need right?. This will tell us if your folder exists.
[stextbox id=’info’]
strFolderExists = Dir(strFolderName, vbDirectory)
[/stextbox]
If the folder does exist then it will be assigned to the variable strFolderExists. The variable remains blank if it does not exist.
Using The MsgBox With IF Statement.
So, we now can use the IF Statement along with the MsgBox. If the strFolderExists variable is blank then the message “The selected folder does not exist” displayed. Alternatively of the folder does exist then “The selected folder exists” is displayed in the MsgBox.
[stextbox id=’info’]
If strFolderExists = “” Then
MsgBox “The selected folder does not exist”
Else
MsgBox “The selected folder exists”
End If
[/stextbox]
Ending The Macro. The Folder Exists!.
The code ends with the End Sub statement. This is already input by default by Excel when we began writing the macro. Now you know if your Excel folder exists.
[stextbox id=’info’]
End Sub
[/stextbox]