VBA to create folder

[data808]

I have some VBA that looks for a specific folder by the current year in this format YYYY and then names the excel file and saves it in that current year YYYY folder. Here is the code:

ActiveWorkbook.SaveAs Filename:="K:\BACKEND\RECORDS\CLERK\" & Format(Now(), "YYYY") & "\" _
    & "ABC_" & Format(Now(), "MM-DD-YYYY") & ".xlsm"

Usually the folder is always there but when the new year comes for 2025, instead of manually creating a folder every year, I was wondering if there was a way that if the YYYY folder does NOT exist, can it auto create one to save the file in it?

How would I write the VBA to do that if possible? Thanks for the help.

 

[Burrgogi]

This thread covers pretty much the same exact request.

How to save file to current year folder?

I have a macro that exports a worksheet as a PDF file and saves it in the workbook location. How would I save the PDF file into a folder labelled as the “current year”, e.g. 2020? If there is no fo...

stackoverflow.com

 

[data808]

Thanks for the help. I was able to figure it out using that thread you suggested. Thanks again.

 

[Burrgogi]

Thanks for the help. I was able to figure it out using that thread you suggested. Thanks again.

You're welcome. Glad you got it figured out!

 

[data808]

In case anyone comes to this thread, here is what worked for me:

'this section is to create a current year folder if it doesn't exist
'be sure to change the source directory for the (respective clerk)
Dim sourceDir As String
Dim year As Integer

'get year
year = Trim(Str(Format(Date, "yyyy")))

'source directory (respective clerk)
sourceDir = "C:\Users\Username\Desktop"

'check if current year folder exists, if no current folder then create one
folder_exists = Dir(sourceDir & "\" & year, vbDirectory)
If folder_exists = "" Then
    MkDir sourceDir & "\" & year
    folder_exists = Dir(sourceDir & "\" & year, vbDirectory)
    MsgBox "Current Year Folder Did Not Exist." & vbNewLine & _
    "Folder Created And File Will Now Save In It."
End If

ActiveWorkbook.SaveAs Filename:="C:\Users\Username\Desktop\" _
    & Format(Now(), "YYYY") & "\" _
    & "CLK_" & Format(Now(), "MM-DD-YYYY") & ".xlsm"

Basically, it looks for the current year folder on the desktop (change directory to your fitting). If folder does not exist it will prompt user to let them know a current year folder has been created and it will now save into that folder. If folder already exists then it will just save the file to that current year folder. Hope this helps out someone.