Create Dated Folders & Save Files within

[Ryth]

Hi All,

I’m quite new to this with my only knowledge being within Power Query, however I expect this would need to be solved within VBA

I am looking for a way to create and store files based on date, creating dated folders if there are none available
E.g within my excel file would be:
Cell A1: Title, Cell B1: Title Code, Cell C1: DATE(DD/MM/YYYY)

Based on this I’d like an excel sheet saved in a centralised folder in the inputted day month year titled “Title + Title Code”

This is something I’ll hopefully be able to build on from here adjusting to my needs in the future e.g creating multiple files in similar methods based on whether there are multiple rows with the same data

Is there a clean way to do this?

Thanks in advance!

 

[breynolds0431]

Hi. See if this helps...

Sub MakeFolders()

'declare this workbook and sheet with file name data
Dim tWB As Workbook: Set tWB = ThisWorkbook
Dim tWS As Worksheet: Set tWS = tWB.Sheets("Sheet1") 'modify sheet to suit

'set variables for SaveAs filename
Dim title As String: title = tWS.Range("A1")
Dim tCode As String: tCode = tWS.Range("B1")
Dim tDate As String: tDate = Format(tWS.Range("C1"), "DDMMYYYY")

'Note tWB.path is the path of this workbook
Dim tPath As String: tPath = tWB.Path & "\" & tDate

'Creates date folder only if it doesn't already exist
If Not IsDir(tPath) Then
    MkDir (tPath)
End If

'will save tWS to dated folder
tWS.Copy
ActiveWorkbook.SaveAs Filename:=tPath & "/" & title & " + " & tCode & ".xlsx", FileFormat:=51
ActiveWorkbook.Close

End Sub

 

[Ryth]

Hi. See if this helps...

Sub MakeFolders()

'declare this workbook and sheet with file name data
Dim tWB As Workbook: Set tWB = ThisWorkbook
Dim tWS As Worksheet: Set tWS = tWB.Sheets("Sheet1") 'modify sheet to suit

'set variables for SaveAs filename
Dim title As String: title = tWS.Range("A1")
Dim tCode As String: tCode = tWS.Range("B1")
Dim tDate As String: tDate = Format(tWS.Range("C1"), "DDMMYYYY")

'Note tWB.path is the path of this workbook
Dim tPath As String: tPath = tWB.Path & "\" & tDate

'Creates date folder only if it doesn't already exist
If Not IsDir(tPath) Then
    MkDir (tPath)
End If

'will save tWS to dated folder
tWS.Copy
ActiveWorkbook.SaveAs Filename:=tPath & "/" & title & " + " & tCode & ".xlsx", FileFormat:=51
ActiveWorkbook.Close

End Sub

I tried applying the code as shown and find it continues to error at
“Create date folder only if it doesn’t exist
If Not IsDir …

The file path is simple beginning in Z:\Archived Files
The only file being this excel spreadsheet. Would it help if I pre-make the folders?
E.g. “Z:\Archived Files\2023\Jan\01”

 

[breynolds0431]

Sorry about that. I was missing the IsDir function. But, instead of using a function, I modified the below to have the dir checked within the sub. My previous code created a single dated folder (i.e.; 23012023). But it now creates the year folder, then the month sub folder, and then the day sub folder as shown in your e.g.

Sub MakeFolders()

'declare this workbook and sheet with file name data
Dim tWB As Workbook: Set tWB = ThisWorkbook
Dim tWS As Worksheet: Set tWS = tWB.Sheets("Sheet1") 'modify sheet to suit

'set variables for SaveAs filename
Dim title As String: title = tWS.Range("A1")
Dim tCode As String: tCode = tWS.Range("B1")
Dim cPath As String: cPath = "Z:\Archived Files\"
Dim tYear As String: tYear = Year(tWS.Range("C1"))
Dim tMon As String: tMon = Left(MonthName(Month(Range("C1"))), 3)
Dim tDay As String: tDay = Format(Day(tWS.Range("C1")), "00")

'Creates year folder if doesn't exist
Path = cPath & tYear
Folder = Dir(Path, vbDirectory)
If Folder = vbNullString Then VBA.FileSystem.MkDir (Path)

'Creates month folder if doesn't exist
Path = cPath & tYear & "\" & tMon
Folder = Dir(Path, vbDirectory)
If Folder = vbNullString Then VBA.FileSystem.MkDir (Path)

'Creates day folder if doesn't exist
Path = cPath & tYear & "\" & tMon & "\" & tDay
Folder = Dir(Path, vbDirectory)
If Folder = vbNullString Then VBA.FileSystem.MkDir (Path)

'will save tWS to dated folder
tWS.Copy
ActiveWorkbook.SaveAs Filename:=cPath & "\" & tYear & "\" & tMon & _
         "\" & tDay & "\" & title & " + " & tCode & ".xlsx", FileFormat:=51
ActiveWorkbook.Close

End Sub