Saving a file to a "YYYY" and "MMM" folder

thanks for the reply but I don't understand. Could you tell me how I would incorporate that into this :

ActiveWorkbook.SaveAs Filename:= _
        "S:\Service Delivery\SS\Clients\H\Funds\ML\Art\Archived\"YYYY"\"MMM"\Art Analysis" & Format(Now(), "dd-mm-yyyy") & ".xls"

This would be run at the end of each day to archive "Art Analysis.xls"

I need it to save to the existing year and month folder, but if there is not one present (e.g. start of the new month) then I need it to create the directory.

thanks again

hi,

The below is a coding approach I use frequently (copied & pasted w/o the guts of the macro - hopefully I haven't deleted too much) & most of the ease comes from Ken Pul's function which is included below...

Option Explicit

Private Sub CreatingDailyFile()
Application.ScreenUpdating = False
    Dim RawCSVDataFolder As String, StartOfFolderPathForSaving  As String, LastFoldersInPathForSaving As String, File_name As String

'''definitions of variables used
 ''File strings
    RawCSVDataFolder = "S:\blahblah\blah\"
    StartOfFolderPathForSaving = "S:\xyz\abc\"
    LastFoldersInPathForSaving = Year(Date) & "\" & Month(Date) & "_" & MonthName(Month(Date), True) & " " & Year(Date) & "\"
    File_name = Format(Date, "yyyymmdd") & " Created File"

'To check on existence of folders & files and create if required
If DoesFileFolderExist(StartOfFolderPathForSaving & LastFoldersInPathForSaving & File_name & ".xls") Then _
    MsgBox "Daily SOH already exists therefore macro ending & macro file closing.", , "INFO ONLY": IsMacroComplete = True: GoTo ExitSub
        If Not DoesFileFolderExist(StartOfFolderPathForSaving & Year(Date)) Then MkDir (StartOfFolderPathForSaving & Year(Date))
        If Not DoesFileFolderExist(StartOfFolderPathForSaving & LastFoldersInPathForSaving) Then _
            MkDir (StartOfFolderPathForSaving & LastFoldersInPathForSaving)

'to check the existence of the raw data extract, open it & id the last row of data
If Not DoesFileFolderExist(RawCSVDataFolder & File_name & ".csv") Then _
    MsgBox "Raw data extract does not exist therefore macro ending.", , "INFO ONLY": IsMacroComplete = False: GoTo ExitSub
Workbooks.Open Filename:=RawCSVDataFolder & File_name & ".csv"

'insert the guts of your macro here...

'Saves the daily file in xls format.
    ActiveWorkbook.SaveAs Filename:= _
        StartOfFolderPathForSaving & LastFoldersInPathForSaving & File_name & ".xls", _
        FileFormat:=xlNormal, Password:="", WriteResPassword:=FilePassWord, _
        ReadOnlyRecommended:=False, CreateBackup:=False

'to advise macro is finished (& macro file closing) or otherwise.
MsgBox "Daily file now created & saved - therefore macro file will close.", , " FILE NOW CREATED"
ExitSub:
Application.ScreenUpdating = True
If IsMacroComplete Then
ThisWorkbook.Close False
Else
MsgBox "Daily file not created yet - therefore macro file staying open.", , "FILE NOT CREATED!"
End If
End Sub

Private Function DoesFileFolderExist(strfullpath As String) As Boolean
'sourced from www.excelguru.ca/node/30 by Ken Puls
'note it only checks for the existence of the lowest folder (or the file) in the strfullpath string.
If Not Dir(strfullpath, vbDirectory) = vbNullString Then DoesFileFolderExist = True
End Function

hth
Rob

hi again,

This would be run at the end of each day to archive "Art Analysis.xls"

On rereading your post I noticed that with your current naming convention
the file could be over-written each day but if you were to modify my code you would get a msgbox saying the file already exists and the macro would end. This can be overcome by including a date code in the file name so that each day has unique filename.
I use the below with yyyymmdd as the date order because this allows for easy chronological sorting based on the file name.

File_name = Format(Date, "yyyymmdd") & " Created File"

Let us know if you have any more questions & hopefully someone else can help - I'm off to bed now.

hth
Rob