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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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:
Change line 14 to:
sFilename = "myWorkbook" 'Set filename
Change line 14 to:
ActiveWorkbook.SaveAs sPath & sFilename & Format(Date, "_ddmmmyy") & ".xls"
ASKER
brilliant thanks
ASKER