VBA Create Folder

What Is VBA Create Folder?

Using VBA Create folder, you can dynamically build a folder when needed instead of directing files to an existing folder. It is possible to create a folder for your files in a specified directory by using the VBA Create Folder function.

For example, it is best to save the file in a folder named after the month for which you are creating a VBA-driven monthly budget spreadsheet. This procedure can be automated with the VBA Create Folder, guaranteeing that the folder creation is in real-time sync with the ongoing operation. Let us look at an example where we create a new folder using VBA.

In this example, we have a subroutine named “CreateNewFolder” that uses the “MkDir” function to create a new folder named “NewFolder” in the root directory of the C drive. The “MkDir” function is a built-in function in VBA used to make directories (folders) at the specified path.

When this code is executed, it will create a new folder named “Sample” in the provided directory of the C drive.

What Is VBA Create Folder?

VBA Create folder provides functionality to interact with the file system, allowing you to create, delete, and manage folders.
VBA create folder offers powerful capabilities for folder management within Excel, allowing users to automate the creation of folders, including nested structures, based on specific conditions such as cell values or predefined lists.
Always check if a folder exists before attempting to perform operations on it to avoid errors.
Use error handling in VBA to manage unexpected issues during file system operations.
The “FileSystemObject” offers an alternative approach for folder manipulation in VBA, providing more features than the built-in functions like “MkDir.”

How to Create a Folder in VBA?

To create a folder in VBA, follow these steps:

Step 1: Open Excel and press ALT + F11 to open the VBA editor.

Step 2: Insert a new module by clicking on “Insert” in the menu and selecting “Module.”

Step 3: Inside the module, write a subroutine or function to create the folder using the “MkDir” function.

For example:

Sub CreateFolder()

MkDir “C:\NewFolder”

End Sub

Step 4: You can now execute the VBA code to create the folder by clicking on Run.

Examples

Let us look at some examples on how to implement create folder using VBA.

Example #1 – Delete a Folder

Suppose we have a folder named “Delete” which we want to delete, we will see how to use the VBA Create Folder function to perform this.

Step 1: We being by creating a subroutine named DeleteFolder.

Step 2: Here, we will declare a variable folderPath as a string. This variable will store the path of the folder to be deleted.

Step 3: Next, we will assign the path of the folder to be deleted to the folderPath variable.

Step 4: Here, we check if the specified folder exists using the Dir function. The “vbDirectory” constant indicates that we are checking for a directory. If the folder exists, the condition is true.

Step 5: In this step, we will delete the folder specified by the folderPath variable using the RmDir function.

Step 6: Next, we will use the message box function to display a message indicating if the folder has been successfully deleted if it existed or to state if the folder doesn’t exist if there is no folder.

Step 7: Finally, save the macro and click on run. When you run this code and the specified folder exists, it will be deleted, and you will receive a message box confirming its deletion. If the folder does not exist, you will receive a message box indicating that it does not exist.

Here is the full code:

Sub DeleteFolder()

Dim folderPath As String

folderPath = “C:\Path\To\Folder”

If Dir(folderPath, vbDirectory) <> “” Then

RmDir folderPath

MsgBox “Folder deleted successfully!”

Else

MsgBox “Folder does not exist!”

End If

End Sub

Example #2 – Check if a Folder Exists

Suppose you have a folder named “Folder” and you want to check if it exists, you can utilize the VBA create folder functionality. Let us see in the following steps:

Step 1: In the new module, we start by creating a new function as follows:

“Function FolderExistsFunction(folderPath As String) As Boolean.”

The above line defines a function named “FolderExistsFunction”. It takes the parameter “folderPath” of type String and returns a Boolean value.

Step 2: Next, we assign the result of the condition (Dir(folderPath, vbDirectory) <> “”) to the function FolderExists.

It checks if the specified folder exists by using the Dir function with the vbDirectory parameter.

If the result is not an empty string, it means the folder exists, and the function returns True; otherwise, it returns False.

Step 3: Now, after the function is created, we will start by defining a new subroutine named “CheckFolderExistence.”

Step 4: Next, we will declare two variables: “folderPath” to store the path of the folder to be checked, and “folderExists” to store whether the folder exists or not.

Step 5: Now, we assign the path of the folder to be checked to the folderPath variable.

Step 6: In this step, we will call the FolderExists function to check if the specified folder exists and assign the result to the folderExists variable.

Step 7: Now, we will check the value of the folderExists variable. If it’s True, we will execute the next line of code.

Step 8: If the above condition is True, we will display a message indicating that the folder exists.

Step 9: If the above condition is not True, then we will display a message indicating that the folder does not exist using the message box function.

Step 10: Now, save the macro and click on run.

When this code is run, it checks if the specified folder exists and displays a message indicating its existence or non-existence.

Here is the full code:

Function FolderExistsFunction(folderPath As String) As Boolean

FolderExistsFunction = (Dir(folderPath, vbDirectory) <> “”)

End Function

Sub CheckFolderExistence()

Dim folderPath As String

Dim folderExists As Boolean

folderPath = “C:\Users\Vikram\Desktop\MS Excel\Folder”

folderExists = FolderExistsFunction(folderPath)

If folderExists Then

MsgBox “The folder exists.”

Else

MsgBox “The folder does not exist.”

End If

Example #3 – Rename a Folder

Suppose you have a folder named “Rename” and you want to change the name of this folder to “NewName”, we will see how this can be achieved using the VBA Create Folder function.

Step 1: In the new module, start by creating a subroutine named “RenameFolder.”

Step 2: Next, declare two variables, oldPath and newPath, as strings. These variables will store the paths of the old and new folders, respectively.

Step 3: Now, we will assign the paths of the old and new folders to the respective variables as follows.

Step 4: Next, we will check if the old folder exists using the Dir function. If it exists, the condition is true.

Step 5: If the above condition is true, then it will rename the old folder to the new folder using the “Name” statement.

Step 6: Here, we will use the message box function to display a message indicating that the folder has been successfully renamed if the old folder exists.

Step 7: If the folder does not exist then we will use another message box function to display a message indicating that the old folder does not exist.

Step 8: Save the macro and click on run. If you run this code and the old folder exists, it will be renamed to the new folder, and you will receive a message box confirming its renaming.

However, if the old folder does not exist, you will receive a message box indicating that it does not exist.

Here is the full code:

Sub RenameFolder()

Dim oldPath As String, newPath As String

oldPath = “C:\Users\Vikram\Desktop\MS Excel\Rename”

newPath = “C:\Users\Vikram\Desktop\MS Excel\NewName”

If Dir(oldPath, vbDirectory) <> “” Then

Name oldPath As newPath

MsgBox “Folder renamed successfully!”

Else

MsgBox “Old folder does not exist!”

End If

End Sub

Important Things To Note

VBA create folder if not exists function will facilitate the creation of a folder if it did not already exist by using the “MkDir” function.
VBA create folder from cell value enables the extraction of the desired folder name, followed by using “MkDir” to create the folder.
VBA create folder and subfolders functionality enables the creation of nested directory structures by specifying the parent folder path along with the desired subfolder names in the MkDir function.
With the VBA create folders from Excel list functionality, it entails looping through the list of folder names, extracting each one, and using the “MkDir” function to create folders based on the names listed in the Excel worksheet.

Frequently Asked Questions (FAQs)

Q1. Can I create nested folders using VBA?

Yes, you can use VBA to create nested folders. Directories inside other directories are called nested folders.

When utilizing the MkDir method, you may easily construct nested folders by providing the entire path, which includes the parent folders.

For example, you might use the command MkDir “C:\ParentFolder\SubFolder” to create a folder called “SubFolder” inside a folder called “ParentFolder” that is located at “C:\ParentFolder”.

Q2. Is there a limit to the length of the folder path I can create using VBA?

The file system and operating system determine how long a folder path can be constructed with VBA. The majority of contemporary Windows operating systems allow paths up to 260 characters in length.

However, “Long Paths” (also known as “Enable Win32 long paths”), an optional feature that allows paths up to 32,767 characters, was added with Windows 10 version 1607 and Windows Server 2016. No particular restrictions are placed on the length of folder paths by VBA itself.

Q3. Can I create a folder in a network location using VBA?

Yes, you can use VBA to create folders at network locations. You may create folders at any accessible location, including network drives and shared folders by using VBA create folder file system interaction features.

When using the MkDir function, just specify the network path

Example: MkDir \server\share\FolderName.

Q4. Are there any alternative methods to create folders in VBA?

Yes, there are other ways in VBA to create folders than using the MkDir function.

Using the FileSystemObject from the Microsoft Scripting Runtime package is one substitute. Compared to the integrated MkDir function, this offers greater flexibility and other features.

To complete folder construction activities, you can alternatively use Windows API calls; however, this approach is more involved and necessitates a greater understanding of VBA programming.

VBA Create Folder