How to Create Folders Using VBA (With Example)


You can use the MkDir statement to create a folder using VBA.

Here is one common way to use this statement in practice:

Sub CreateFolder()
    MkDir "C:\Users\Bob\Desktop\My_Data"
End Sub

This particular macro will create a folder called My_Data on the Desktop of my computer.

The following example shows how to use this function in practice.

Example: Create a Folder Using VBA

Suppose my Desktop currently has two folders in it:

Suppose I would like to use VBA to create a folder called My_Data on the Desktop.

I can create the following macro to do so:

Sub CreateFolder()
    MkDir "C:\Users\Bob\Desktop\My_Data"
End Sub

Once I run this macro and open the File Explorer to navigate to the Desktop, I can see that this new folder has been created:

I can see that the new folder called My_Data has been created in the exact location that I specified.

Note that if this folder already existed in this location and I ran this macro, I would receive the following error:

The error box tells us that there is a Path/File access error because a folder with this name already exists in this location on my computer.

I could either create a folder with a different name or create the folder in a different location to avoid this error.

Additional Resources

The following tutorials explain how to perform other common tasks in VBA:

VBA: How to Count Number of Sheets in Workbook
VBA: How to Extract Data from Another Workbook
VBA: How to Delete Sheet if Name Contains Specific Text

2 Replies to “How to Create Folders Using VBA (With Example)”

  1. Hi,
    i’m new to VBA, what if the folder already exist, is there a way to ignore the command and continue or somehow use command “if”?
    My situation:
    I want to create a folder and then save into that folder printed sheet as pdf. And im struggling when the folder is already there.

    Thanks

    1. Hi Vojta…You can use VBA to check if a folder already exists before attempting to create it. If the folder exists, you can skip the creation step and proceed with saving the sheet as a PDF. Here’s a step-by-step guide to help you with your situation:

      1. **Check if the Folder Exists**: Use the `Dir` function to check if the folder exists.
      2. **Create the Folder if it Does Not Exist**: If the folder does not exist, use the `MkDir` function to create it.
      3. **Save the Sheet as PDF in the Specified Folder**: Save the active sheet as a PDF in the specified folder.

      Here’s a sample VBA code to achieve this:

      “`vba
      Sub SaveSheetAsPDF()
      Dim folderPath As String
      Dim pdfPath As String
      Dim ws As Worksheet

      ‘ Define the folder path and the PDF path
      folderPath = “C:\YourFolderPath\SubFolder\”
      pdfPath = folderPath & “SheetName.pdf”
      Set ws = ActiveSheet

      ‘ Check if the folder exists
      If Dir(folderPath, vbDirectory) = “” Then
      ‘ If the folder does not exist, create it
      MkDir folderPath
      End If

      ‘ Save the active sheet as PDF in the specified folder
      ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfPath, Quality:=xlQualityStandard

      MsgBox “PDF saved successfully in ” & folderPath
      End Sub
      “`

      ### Explanation:
      1. **Define the Folder Path and PDF Path**:
      – `folderPath`: The path where you want to create the folder and save the PDF.
      – `pdfPath`: The complete path including the file name where the PDF will be saved.

      2. **Check if the Folder Exists**:
      – `If Dir(folderPath, vbDirectory) = “” Then`: This checks if the folder exists. If the folder does not exist (`Dir` returns an empty string), the code inside the `If` statement will execute.

      3. **Create the Folder if Necessary**:
      – `MkDir folderPath`: This creates the folder if it does not already exist.

      4. **Save the Active Sheet as PDF**:
      – `ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfPath, Quality:=xlQualityStandard`: This saves the active sheet as a PDF in the specified folder.

      5. **Notification**:
      – `MsgBox “PDF saved successfully in ” & folderPath`: This displays a message box to confirm that the PDF has been saved successfully.

      ### Notes:
      – Make sure to replace `”C:\YourFolderPath\SubFolder\”` with the actual path where you want to create the folder and save the PDF.
      – Adjust `”SheetName.pdf”` to your desired PDF file name.
      – The code assumes the active sheet is the one you want to save as PDF. If you want to save a specific sheet, replace `ActiveSheet` with the specific sheet reference, e.g., `Sheets(“Sheet1”)`.

      By following this approach, you can handle the situation where the folder might already exist, ensuring your code continues to execute without errors.

Leave a Reply

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