Create individual folders for next day

[jobroiwn1963]

I hope someone can help me. I’m having problems with one line of code.
I found this code to create folders from names entered into cells. Works great except I need to have the folder created for tomorrows date. I can’t figure out how to write something that will automatically change this date every day.

I enter patients names into excel 2007 for the following days exam. I then have to make folders for each patient where we will store their information. Currently I go into the macro and change the date xdir = "h:\patient reports 2014\july\17JUL14\" to the next day.
(I have code to create the month folder and save the sheet to tomorrows date.)
Rather than having to go into the macro and change the date on this one line everyday (and the month when the month changes) is there something I could write to do this automatically?
I’m sure its something simple but I’m at my wits end.
Thankyou


'create pt folders in july works.....

'Sub MakeFolders()
Dim xdir As String
Dim fso
Dim lstrow As Long
Dim i As Long
Set fso = CreateObject("Scripting.FileSystemObject")
lstrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "i").End(xlUp).Row
Application.ScreenUpdating = False
For i = 1 To lstrow
'change the path on the next line where you want to create the folders
xdir = "h:\patient reports 2014\july\17JUL14\" & Range("i" & i).Value

If Not fso.FolderExists(xdir) Then
fso.CreateFolder (xdir)
End If
Next
Application.ScreenUpdating = True

End Sub

[jobroiwn1963]

[/color] after 17jul14 is a typo. apologies.
Jesse

[mancubus]

welcome to VBAX.

the Date function returns the current date. Date - 1 is yesterday and Date + 1 is tomorrow

this is for your red part

Sub test()
xdir = "h:\patient reports 2014\july\" & Format(Date + 1, "ddmmyy") & "\"
MsgBox xdir
End Sub




but i would use below



Sub test()
xdir = "h:\patient reports " & Year(Date + 1) & "\" & Format(Date + 1, "mmmm") & "\" & Format(Date + 1, "ddmmyy") & "\"
MsgBox xdir
End Sub

[JKwan]

Not to steal your thunder, but you missed an "m"


Sub test()
xdir = "h:\patient reports " & Year(Date + 1) & "\" & Format(Date + 1, "mmmm") & "\" & Format(Date + 1, "ddmmmyy") & "\"
MsgBox xdir
End Sub

[mancubus]

thanks for the correction, JKwan.