Create new folder macro

MattH1

Board Regular
Joined
Jul 15, 2016
Messages
174
Hey,
I have a macro that currently filters data and then creates a new spreadsheet and saves it into a file. I want to add (to the beginning of the macro) a code that would create a new folder so that I can save these new files into that folder. How do I create a new folder using VBA that uses today's date as part of the name?

Thanks!!
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
some solid options here -> Is there a way to create a folder, and sub folders in Excel VBA? - Stack Overflow

remember to add the reference for Microsoft Scripting Runtime when using file system object

Hey Barry

Here's the code I was using from there when I got an error I couldn't fix:



Code:
'requires reference to Microsoft Scripting Runtime
Sub MakeFolder()
Dim strComp As String, strPart As String, strPath As String
strComp = Range("A1") ' assumes company name in A1
strPart = CleanName(Range("C1")) ' assumes part in C1
strPath = "C:\Images\"
[COLOR=#FF0000]If Not FolderExists(strPath & strComp) Then[/COLOR]
'company doesn't exist, so create full path
    [COLOR=#FF0000]FolderCreate strPath & strComp & "\" & strPart[/COLOR]
Else
'company does exist, but does part folder
[COLOR=#FF0000]    If Not FolderExists(strPath & strComp & "\" & strPart) Then[/COLOR]
   [COLOR=#FF0000]     FolderCreate strPath & strComp & "\" & strPart[/COLOR]
    End If
End If
End Sub
Function FolderCreate(ByVal path As String) As Boolean
FolderCreate = True
Dim fso As New FileSystemObject
If Functions.FolderExists(path) Then
    Exit Function
Else
    On Error GoTo DeadInTheWater
    fso.CreateFolder path
    Exit Function
End If
DeadInTheWater:
    MsgBox "A folder could not be created for the following path: " & path & ". Check the path name and try again."
    FolderCreate = False
    Exit Function
End Function
Function FolderExists(ByVal path As String) As Boolean
FolderExists = False
Dim fso As New FileSystemObject
If fso.FolderExists(path) Then FolderExists = True
End Function
Function CleanName(strName As String) As String
'will clean part # name so it can be made into valid folder name
'may need to add more lines to get rid of other characters
    CleanName = Replace(strName, "/", "")
    CleanName = Replace(CleanName, "*", "")
End Function

These two (after you press enter) give the error:
"Compile error: Expected: ("
It seems the problem is with strComp and how it's called or used, as when I just use "If Not FolderExists(strPath) Then" it works.
Any help is appreciated.
 
Upvote 0
I modified the below and it worked for me

Code:
Sub creat()
Dim strPath As String, path As String
strPath = "G:\Investment Administration\Fund Accounting\NAV Oversight\ZBarry\" & Range("A1").Value
If FolderExists(strPath) = False Then
'company doesn't exist, so create full path
    FolderCreate (strPath)
Else
'company does exist, but does part folder
    If Not FolderExists(strPath) Then
        FolderCreate strPath
    End If
End If
End Sub
Function FolderCreate(ByVal path As String) As Boolean
FolderCreate = True
Dim fso As New FileSystemObject
If FolderExists(path) Then
    Exit Function
Else
    On Error GoTo DeadInTheWater
    fso.CreateFolder path
    Exit Function
End If
DeadInTheWater:
    MsgBox "A folder could not be created for the following path: " & path & ". Check the path name and try again."
    FolderCreate = False
    Exit Function
End Function
Function FolderExists(ByVal path As String) As Boolean
FolderExists = False
Dim fso As New FileSystemObject
If fso.FolderExists(path) Then FolderExists = True
End Function
 
Upvote 0
I modified the below and it worked for me

Code:
Sub creat()
Dim strPath As String, path As String
strPath = "G:\Investment Administration\Fund Accounting\NAV Oversight\ZBarry\" & Range("A1").Value
If FolderExists(strPath) = False Then
'company doesn't exist, so create full path
    FolderCreate (strPath)
Else
'company does exist, but does part folder
    If Not FolderExists(strPath) Then
        FolderCreate strPath
    End If
End If
End Sub
Function FolderCreate(ByVal path As String) As Boolean
FolderCreate = True
Dim fso As New FileSystemObject
If FolderExists(path) Then
    Exit Function
Else
    On Error GoTo DeadInTheWater
    fso.CreateFolder path
    Exit Function
End If
DeadInTheWater:
    MsgBox "A folder could not be created for the following path: " & path & ". Check the path name and try again."
    FolderCreate = False
    Exit Function
End Function
Function FolderExists(ByVal path As String) As Boolean
FolderExists = False
Dim fso As New FileSystemObject
If fso.FolderExists(path) Then FolderExists = True
End Function

Hey Barry it seems to me that strComp is the problem and I think I found out why. I'm running Excel 2010 in which strComp is already a variable in Excel, so I think I just had to change the name. I like your code though, it seems to flow a lot better. Thank you so much for your help!!
 
Upvote 0
You're welcome, glad you got it sorted.
 
Upvote 0
You're welcome, glad you got it sorted.

Hey Barry,
I seemed to be overconfident in the code executing just by reading it and going through it.
When I run it, I get the error "Compile error: User-defined type not defined" and it highlights in yellow Function FolderExists(ByVal path As String) As Boolean and then also highlights Dim fso As New FileSystemObject.
I don't really know how to fix this, I know close to nothing about FileSystemObjects and couldn't get much information looking at it online and looking at other codes.
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top