Save file with current date in folder where Path changes each month/year etc

The below code was provided to me by Jazzyjoop and works fine however I need a small change in my current project.

The file name needs to include the current days date, i.e. 11092013_myWorkbook.xlsm

Could the below code be amended to take this into account?

Sub monthFolder()
   
    Dim sBasePath As String
    Dim sFilename As String
    Dim sMonth As String
    Dim sPath As String
    Dim sYear As String
   
    sFilename = "myWorkbook.xlsm" 'Set filename
    sBasePath = "C:\rights\Mkt\Break\" 'Set base path
    If Right(sBasePath, 1) <> "\" Then sBasePath = sBasePath & "\" 'Check for "\"
   
    sYear = Year(Now) 'Set this year
    sPath = sBasePath & sYear & "\" 'Set path incl. year
    If Dir(sPath, vbDirectory) = "" Then
        MkDir sPath 'Create directory if it does not exist
    End If
   
    If Len(Month(Now)) = 1 Then
        sMonth = "0" & Month(Now) 'Add leading zero
    Else
        sMonth = Month(Now)
    End If
   
    sMonth = sMonth & " " & MonthName(Month(Now), True) 'Set month as number + name
    sPath = sPath & sMonth & "\" 'Set path incl. month
   
    If Dir(sPath, vbDirectory) = "" Then
        MkDir sPath 'Create directory if it does not exist
    End If
   
    ActiveWorkbook.SaveAs sBasePath & sFilename
   
End Sub


Thanks
JagwarmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

IrogSintaCommented:
ActiveWorkbook.SaveAs sBasePath & sFilename
What's strange about your code is that it creates folders and subfolders for the year and month yet it doesn't make use of them and saves the file in the sBasePath folder instead.

Anyway, here's a shorter version of your code along with addition of the date in the filename.
Sub monthFolder()
    Dim sFilename As String
    Dim sPath As String
     
    sFilename = "myWorkbook.xls" 'Set filename
    sPath = "C:\rights\Mkt\Break\" 'Set base path
    If Right(sPath, 1) <> "\" Then sPath = sPath & "\" 'Check for "\"
    
    sPath = sPath & Format(Date, "yyyy\\mm mmm\\")
   
    If Dir(sPath, vbDirectory) = "" Then 'If folder doesn't exist, create it
        Shell "cmd /c mkdir """ & sPath & """"
    End If
    ActiveWorkbook.SaveAs sPath & Format(Date, "mmddyyyy_") & sFilename
End Sub

Open in new window

The advantage of using Shell with MkDir is that it can simultaneously create folders and subfolders.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JagwarmanAuthor Commented:
IrogSinta, thanks for that it's exactly what I was looking for.
0
JagwarmanAuthor Commented:
IrogSinta,

don't know if you will pick up this but if you do would it be possible to change the save  so that it

becomes myWorkbook_16Jan 14

Thanks
0
IrogSintaCommented:
Change line 5 to:
sFilename = "myWorkbook"   'Set filename

Change line 14 to:
ActiveWorkbook.SaveAs sPath & sFilename & Format(Date, "_ddmmmyy") & ".xls"
0
JagwarmanAuthor Commented:
brilliant thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.