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
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
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.