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.
Sub MakeMyFolder()
If Dir("C:\temp\April", vbDirectory) = "" Then
MkDir Path:="C:\temp\April"
Else
ActiveWorkbook.SaveAs Filename:= _
"C:\temp\april\save3.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
End If
End Sub
i updated the code again, except this time im getting an error: "run time error 75 path/file access error"
Sub auto_organize_save1()
Dim fdObj As Object
Dim folder As String
Set fdObj = CreateObject("Scripting.FileSystemObject")
folderYear = "C:\temp\testing\" & Format(Now, "YYYY") & "\"
folderMonth = "C:\temp\testing\" & Format(Now, "YYYY") & "\" & Format(Now, "MM-MMM") & "\"
If Not fdObj.FolderExists(folder) Then
MkDir folderYear
End If
If Not fdObj.FolderExists(folder) Then
MkDir folderMonth
End If
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=folderMonth & "example.xlsx"
Application.DisplayAlerts = False
End Sub
CodePudding user response:
Some issues:
- Your code is not getting a particular date, it just uses the default value of
dateOne
. Instead useNow
. dateOne = April
references an undefined variableApril
. In order to get the month of a date, use theMonth
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