Link to home
Start Free TrialLog in
Avatar of Jagwarman
Jagwarman

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of IrogSinta
IrogSinta
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jagwarman
Jagwarman

ASKER

IrogSinta, thanks for that it's exactly what I was looking for.
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
Change line 5 to:
sFilename = "myWorkbook"   'Set filename

Change line 14 to:
ActiveWorkbook.SaveAs sPath & sFilename & Format(Date, "_ddmmmyy") & ".xls"
brilliant thanks