CODEWITHSUNDEEP
excelvba
New Kid on the block
New Kid on the block

Reputation: 55

Excel VBA Auto-Save IF, THEN, ElSE

I'm trying to make an Excel VBA macro to automate my saves. So if it is 2022 or 2023 and it is either the month of January, February, march, etc. The file will save in that year's folder and under that month's folder. However, I'm not the best at If, Then, Else statements. I made this VBA and it doesn't work after I tried to make it create folders if they don't exist.

Sub auto-organize-save()
'
' auto-organize-save Macro
'

'
    'this is for date
    Dim dateOne As Date
    'This is for making new folder
    Dim fdObj As Object
    Application.ScreenUpdating = False
    Set fdObj = CreateObject("Scripting.FileSystemObject")
         
    If fdObj.FolderExists("C:\temp\april") Then
    If dateOne = April Then
    ActiveWorkbook.SaveAs Filename:= _
        "C:\temp\april\save3.xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
    Else
    fdObj.CreateFolder ("C:\temp\april")
    
    End If
    End If
End Sub

i modified it a bit further and I'm getting results but i need to figure out how to change the name of the folder to display the following: "04 - APR" - 4 means the 4th month and APR is the abbreviated version. With the help of the user trincot following works perfectly.

 Sub auto_organize_save1()
    Dim fdObj As Object
    Dim folder As String
    
    Set fdObj = CreateObject("Scripting.FileSystemObject")
    folderYear = "C:\temp\" & Format(Now, "YYYY") & "\"
    folderMonth = "C:\temp\" & Format(Now, "YYYY") & "\" & Format(Now, "MM-MMM") & "\"
    
    
    
    If Not fdObj.FolderExists(folderYear) Then
        MkDir folderYear
    End If
    
    If Not fdObj.FolderExists(folderMonth) Then
        MkDir folderMonth
    End If
    
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=folderMonth & "example2.xlsx"
    Application.DisplayAlerts = False
End Sub

Upvotes: 0

Views: 417

Answers (1)

trincot
trincot

Reputation: 350715

Some issues:

  • Your code is not getting a particular date, it just uses the default value of dateOne. Instead use Now.
  • dateOne = April references an undefined variable April. In order to get the month of a date, use the Month function, and compare it with a number.
  • Hard coding months, like "April", is not going to give you elegant code. Moreover, this is not even the format you are asking for ("4 - APR").
  • I would suggest to prefix the month 4 with a zero so it always has two digits, and will look better when other entries are "12 - DEC", ...etc.
  • Application.ScreenUpdating = False should only be used when you already have well working code. Don't use it for as long your code is not working. And if you use it, add also the opposite: Application.ScreenUpdating = True
  • I'm not sure it is a good idea to call your file always "save3", but as I got no information about this aspect, I just left it as you had it.

Here is some code you could use:

Sub auto_organize_save()
    Dim fdObj As Object
    Dim folder As String
    
    Set fdObj = CreateObject("Scripting.FileSystemObject")
    folder = "C:\temp\" & Format(Now, "MM-MMM") & "\"
    
    If Not fdObj.FolderExists(folder) Then
        MkDir folder
    End If
    
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=folder & "save3.xlsx"
    Application.DisplayAlerts = True
End Sub

Upvotes: 1

Related Questions