VBA to create a folder name with the date for Monday and Friday separated by a hyphen?

dougmarkham

Board Regular
Joined
Jul 19, 2016
Messages
204
Office Version
365
Platform
Windows
Hi Folks,

I have found a great macro which allows you to check for the existence of folders in a file-path matching: Year, Month and Day (i.e., creating the folder if it doesn't exist).

For instance, if the FilePath starts: C:\Temp Time Sheets\
it checks for
C:\Temp Time Sheets\2019\
C:\Temp Time Sheets\2019\11_November\
C:\Temp Time Sheets\2019\11_November\21\
...creating a folder for any missing folders in the above.

VBA Code:
Sub DateFolderSave()

Dim strGenericFilePath      As String: strGenericFilePath = "C:\Temp Time Sheets\"
Dim strYear                 As String: strYear = Year(Date) & "\"
Dim strMonth                As String: strMonth = Format(Month(Date), "00") & "_" & MonthName(Month(Date)) & "\"
Dim strDay                  As String: strDay = Day(Date) & "\"
Dim strFileName             As String: strFileName = "Time Sheet"

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlManual
ThisWorkbook.CheckCompatibility = False

' Check for year folder and create if needed
If Len(Dir(strGenericFilePath & strYear, vbDirectory)) = 0 Then
    MkDir strGenericFilePath & strYear
End If

' Check for month folder and create if needed
If Len(Dir(strGenericFilePath & strYear & strMonth, vbDirectory)) = 0 Then
    MkDir strGenericFilePath & strYear & strMonth
End If

' Check for date folder and create if needed
If Len(Dir(strGenericFilePath & strYear & strMonth & strDay, vbDirectory)) = 0 Then
    MkDir strGenericFilePath & strYear & strMonth & strDay
End If

' Save File
ActiveWorkbook.SaveAs fileName:= _
strGenericFilePath & strYear & strMonth & strDay & strFileName, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

ThisWorkbook.CheckCompatibility = True
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlAutomatic

' Popup Message
MsgBox "File Saved As: " & vbNewLine & strGenericFilePath & strYear & strMonth & strDay & strFileName

End Sub
My aim is for the last folder in the FilePath to be a date-range spanning the current week e.g., for this week: \16.11.2019-22.11.2019\ (i.e., the folder name would contain two parts: 1) the date for Monday and 2) the date for Friday separated by a hyphen or something similar).

So the full FilePath would be C:\Temp Time Sheets\2019\11_November\16.11.2019-22.11.2019\

I know that to populate a cell with the date for a Monday of any given week, I need to input: =TODAY() - WEEKDAY(TODAY(),3)
However, using the following:

Code:
Dim strDay                  As String: strDay = TODAY() - Weekday(TODAY(), 3) & "-" & TODAY() - Weekday(TODAY(), 3) + 4 & "\"
...returns the error message: Sub or Function not defined

I replaced the TODAY element with NOW, and got "Run-time error '52: Bad file name or number"

Would anybody be willing to help me modify the code to achieve this aim?

Kind regards,

Doug.
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,566
Office Version
365
Platform
Windows
Doug

Try replacing TODAY()/NOW() with Date.
VBA Code:
Dim strDay As String

strDay = Format(Date - Weekday(Date, 3), "dd.mm.yyyy") & "-" & Format(Date - Weekday(Date, 3) + 4, "dd.mm.yyyy") & "\"
 

Paul Ked

Active Member
Joined
Jun 4, 2015
Messages
442
Now() does work
Code:
strDay = Format(Now() - Weekday(Now(), 3), "DD.MM.YYYY") & "-" & Format(Now() - Weekday(Now(), 3) + 4, "DD.MM.YYYY") & "\"
 

dougmarkham

Board Regular
Joined
Jul 19, 2016
Messages
204
Office Version
365
Platform
Windows
Doug

Try replacing TODAY()/NOW() with Date.
VBA Code:
Dim strDay As String

strDay = Format(Date - Weekday(Date, 3), "dd.mm.yyyy") & "-" & Format(Date - Weekday(Date, 3) + 4, "dd.mm.yyyy") & "\"
Hi Norie,

Thanks for showing me this---thanks for your time!
I too didn't know about using Format, nice to learn something new.

Kind regards,

Doug.
 

dougmarkham

Board Regular
Joined
Jul 19, 2016
Messages
204
Office Version
365
Platform
Windows
Now() does work
Code:
strDay = Format(Now() - Weekday(Now(), 3), "DD.MM.YYYY") & "-" & Format(Now() - Weekday(Now(), 3) + 4, "DD.MM.YYYY") & "\"
Hi Paul,

Thanks for this, it works great! I was afraid to use NOW as when used in formulas, it adds a time-stamp. No such issue with this though.
Thanks for your help.

Kind regards,

Doug.
 

Watch MrExcel Video

Forum statistics

Threads
1,100,138
Messages
5,472,742
Members
406,834
Latest member
RahafKh

This Week's Hot Topics

Top